Solved

Can I do a IsGreaterEqual in a BQL Fluent statement on a String field?

Joe Schmucker
Rank
Userlevel 6
Badge +3

Here is my scenario.

I have a table that has a range of zip codes.  If the left 3 characters of the Ship Address zip code fall in the range of “lower” and “higher” I want to select that record.

Example:

Ship Address Zip is 100234.

In my lookup table, the lower is 100 and the upper is 130.

So the left 3 digits of the zip (100) falls in the range.

In my BQL statement, I do not see a way to do an IsGreaterEqual using the string value.  It causes errors.

This is what I want to do:

ICSFSSScheduleLine item = SelectFrom<ICSFSSScheduleLine>
    .InnerJoin<ICSFSSSchedule>
    .On<ICSFSSSchedule.scheduleID.IsEqual<ICSFSSScheduleLine.scheduleID>>
    .Where<ICSFSSScheduleLine.scheduleID.IsEqual<@P.AsString>
    .And<ICSFSSScheduleLine.lower.IsGreaterEqual<@P.AsString>>
    .And<ICSFSSScheduleLine.upper.IsLessEqual<@P.AsString>>
    .And<ICSFSSSchedule.active.IsEqual<True>>>
    .OrderBy<Asc<ICSFSSScheduleLine.lower>, 
    Asc<ICSFSSScheduleLine.upper>, 
    Asc<ICSFSSScheduleLine.cutoffDate>>
    .View.Select(Base, itemExt.UsrScheduleID, 
            shipAddress.PostalCode.Substring(0, 3), 
            shipAddress.PostalCode.Substring(0, 3));

What I am doing now is using a foreach and selecting ALL records by Schedule ID and doing a string comparison to see if it is in the range.  

foreach (ICSFSSScheduleLine item in SelectFrom<ICSFSSScheduleLine>
    .InnerJoin<ICSFSSSchedule>
        .On<ICSFSSSchedule.scheduleID.IsEqual<ICSFSSScheduleLine.scheduleID>>
     .Where<ICSFSSScheduleLine.scheduleID.IsGreaterEqual<@P.AsString>
         .And<ICSFSSSchedule.active.IsEqual<True>>>
     .OrderBy<Asc<ICSFSSScheduleLine.lower>, 
          Asc<ICSFSSScheduleLine.upper>, 
          Asc<ICSFSSScheduleLine.cutoffDate>>
     .View.Select(Base, itemExt.UsrScheduleID))

{

                int lowerCompare = String.Compare(item.Lower, shipAddress.PostalCode.Substring(0, 3), comparisonType: StringComparison.OrdinalIgnoreCase);
                int upperCompare = String.Compare(shipAddress.PostalCode.Substring(0, 3), item.Upper, comparisonType: StringComparison.OrdinalIgnoreCase);


                if (lowerCompare >=0 && upperCompare < 0 && row.RequestDate >= prevCutoffDate && row.RequestDate <= currentCutoffDate)
                {
                 //do my stuff here }

}

 

Is there a way to use an IsGreaterEqual and IsLessEqual in a BQL statement on a String value?  That would be a much more efficient way to do this.

icon

Best answer by darylbowman 31 July 2023, 19:45

View original
Joe Schmucker, CPA

11 replies

D
Rank
Userlevel 7
Badge +5

What is the error that you receive?

 

Joe Schmucker
Rank
Userlevel 6
Badge +3

it isn’t really an “error”.  It just returns a null.  Sorry.  When I had the null issue, I went ahead with the foreach to evaluate it.  I forgot it wasn’t an actual error, it was just not returning a record.

This is a filtered view of the data in the table:

In debug, the parameter has a value of “100”

The BQL statement should be picking up one of those records.  

I know that the other clauses in the select are valid because it does find a record if I remove the two And statements in red.

Joe Schmucker, CPA
Joe Schmucker
Rank
Userlevel 6
Badge +3

FYI, I still have to do this in a foreach loop because I need to do a comparison of cut off dates with the previous record, but instead of returning 200 rows and cycling through them all, I’d like to just pull the 6 records that actually are in the zip code range.  

Please give your opinion if that is really not a big deal in terms of performance if I have to cycle through 200 records instead of just 10.  I guess I just want this to be more efficient, but the foreach I am using does work.

 

Joe Schmucker, CPA
Leonardo Justiniano
Rank
Userlevel 6
Badge +4

Hi @Joe Schmucker 

Generally speaking, yes you can. Have in consideration that the BQL translated to SQL Server or the core DB engine you are working, will be create as an expression like 

[view-query].[field] >= @P0

Where internally @P0 is handled as

declare @P0 nvarchar(10)

SELECT @P0 = 'your value'

The SQL Engine will perform an alphabetic testing of the value to determine if it is greater or equal to the field. This will be based on the collation set in the server.

Everything is executed using ExecuteSql store procedure.

 

You can take a look in your local by executing the SQL Server Profiler or the new and useful feature coming in Acumatica 

Then

 

After you finish your interactions with Acumatica you hit STOP and Acumatica will provide you with a downloaded zip file with all traces

You can see the query on the file SMPerformanceInfoSQL.log in a JSON format

Example

 

Leo
darylbowman
Rank
Badge +11

You can’t compare strings in this way because they’re strings and not numbers. See this post for a more in-depth explanation.

The best way to do this would be to TryParse the left three characters of each string (turning them into numbers) and then compare the numbers, which is not something those F-bql comparisons are going to do for you out-of-the-box. I’m sure someone really smart could tell you how to write a new F-bql comparison thingy that would do just what you need, but it’s not me. I’d recommend you do it similarly to what you are, but even that may be flawed, according to the linked post.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
D
Rank
Userlevel 7
Badge +5

If the string field contains three digits all the time and the underlying field length is three, then the comparison will work. You just have to make sure that you left pad with zeroes so that 10 is stored as 010.

Alternatively, if you’re stuck with the current structure, you could look at adding two fields that contain the integer value of upper and lower.  Have the graph update those new fields automatically which allow you to query against the integer fields without needing to worry about string values and collations (although I would hope that all collations sort 0..9).

There’s database design ideals that speak to database performance and then there’s database design ideals that make for easier reporting. They often aren’t the same thing.

 

Joe Schmucker
Rank
Userlevel 6
Badge +3

@darylbowman I tried doing it with a try parse.  The only problem is that a zip code starting with 100 evaluates to greater that a zip code of 030.  So 100 > 030 will evaluate to true.  I need the 100 in the table to be a starting cutoff.  100 needs to be between 100 and 140.  So the comparison of 100 greater than or equal to 100 and less than or equal to 140 will be true even if the table has an 030 as it converts 030 to 30.

Since the number of records will be a max of around 200 per schedule ID, looping through 200 may not be a big deal.

I think you are correct.  I should just leave it alone.

@Leonardo Justiniano I just tried that cool trick with the profiler.  Very neat!.  

"ParentId":52,"RecordId":1611,"RequestStartTime":2545.6305,"SqlTimeMs":2.8631,"NRows":1,"SqlId":1880521070,"StackTraceId":1785345347,"SQLParams":"declare @P0 as nvarchar(13) = 'BFF-33 (B169)'","RequestDateTime":"2023-07-31T14:09:38.973","QueryCache":false},
{"TableList":"ICSFSSScheduleLine,ICSFSSSchedule","QueryOrderId":341,"SQLHash":"29804867","SQLText":"SELECT /* SO.30.10.00, 29804867 */TOP (1) [ICSFSSScheduleLine].[ScheduleID], [ICSFSSScheduleLine].[CutoffDate], [ICSFSSScheduleLine].[ShipDate], [ICSFSSScheduleLine].[Lower], [ICSFSSScheduleLine].[Upper], [ICSFSSScheduleLine].[Active], [ICSFSSScheduleLine].[CreatedByID], [ICSFSSScheduleLine].[CreatedByScreenID], [ICSFSSScheduleLine].[CreatedDateTime], [ICSFSSScheduleLine].[LastModifiedByID], [ICSFSSScheduleLine].[LastModifiedByScreenID], [ICSFSSScheduleLine].[LastModifiedDateTime], [ICSFSSScheduleLine].[Tstamp], [ICSFSSScheduleLine].[Noteid], NULL, NULL, NULL, [ICSFSSSchedule].[ScheduleID], [ICSFSSSchedule].[Description], [ICSFSSSchedule].[Active], [ICSFSSSchedule].[CreatedByID], [ICSFSSSchedule].[CreatedByScreenID], [ICSFSSSchedule].[CreatedDateTime], [ICSFSSSchedule].[LastModifiedByID], [ICSFSSSchedule].[LastModifiedByScreenID], [ICSFSSSchedule].[LastModifiedDateTime], [ICSFSSSchedule].[Tstamp], [ICSFSSSchedule].[Noteid], NULL, NULL, NULL\r\nFROM [ICSFSSScheduleLine] [ICSFSSScheduleLine]\r\nINNER JOIN [ICSFSSSchedule] [ICSFSSSchedule] ON ( [ICSFSSSchedule].[CompanyID] = 2) AND [ICSFSSSchedule].[ScheduleID] = [ICSFSSScheduleLine].[ScheduleID]\r\nWHERE ( [ICSFSSScheduleLine].[CompanyID] = 2) AND ( [ICSFSSScheduleLine].[ScheduleID] = @P0 AND [ICSFSSScheduleLine].[Lower] >= @P1 AND [ICSFSSScheduleLine].[Upper] <= @P2 AND [ICSFSSSchedule].[Active] = CONVERT (BIT, 1))\r\nORDER BY [ICSFSSScheduleLine].[Lower], [ICSFSSScheduleLine].[Upper], [ICSFSSScheduleLine].[CutoffDate], [ICSFSSScheduleLine].[ScheduleID], [ICSFSSScheduleLine].[ShipDate] OPTION(OPTIMIZE FOR UNKNOWN)

 

, \r\n@P1 as nvarchar(3) = '100', \r\n@P2 as nvarchar(3) = '100'"

It looks like the parameters are being passed in as they should be.  

I should have done this to begin with.  The equivalent select statement in SQL is

SELECT * FROM ICSFSSScheduleLine 
WHERE 
    ScheduleID = 'BFF-33 (B169)'
    AND lower >= '100' 
    and upper <= '100'

That will never work.

The select statement needs to know what the actual upper value is and that is not going to be known at the time of the select.  

I don’t think there is a way to formulate a SQL statement to select the record unless I already know the lower constraint AND the upper constraint.

Edit: I CAN use the lower parameter to at least skip the records where the lower is greater than or equal to.  That will do some good.  Without knowing the upper value in the lookup table, I think that is the best I can do.

Thank you all for chiming in!!  I love you folks.  I’d be lost in the muck without this community.

 

Joe Schmucker, CPA
Leonardo Justiniano
Rank
Userlevel 6
Badge +4

Hi @Joe Schmucker 

 

Should it be

SELECT * FROM ICSFSSScheduleLine 
WHERE 
    ScheduleID = 'BFF-33 (B169)'
    AND lower <= '100' 
    and upper >= '100'
.And<ICSFSSScheduleLine.lower.IsLessEqual<@P.AsString>>
.And<ICSFSSScheduleLine.upper.IsGreaterEqual<@P.AsString>>

???

Leo
Joe Schmucker
Rank
Userlevel 6
Badge +3

@Leonardo Justiniano Nice try… HA!  It will pull values where the lower is equal to 010.  I’m going to leave it as is.

Thanks for the try though.  :-)

 

Joe Schmucker, CPA
R
Rank
Userlevel 6
Badge +5

I’ve suggested to the Acumatica dev team that in concert with migrating the view layer to Aurelia/Typescript, they should consider a migration path off of BQL as well. For example, a more lightweight query builder like this one would allow a migration away from BQL, while still maintaining an abstraction layer at the database level to accommodate multiple database types as well as strong typing in the business logic layer:

https://ts-sql-query.readthedocs.io/en/stable/queries/basic-query-structure/

Apropos to this conversation, on the fly type conversion is handled with this tool in this way:

https://ts-sql-query.readthedocs.io/en/stable/column-types/#type-adapters

Or, in regular T-SQL you would just be able to CAST() this column on the fly.

In BQL you are stuck with no options.

Heavyweight ORMs (like BQL), are very 2005, and I think the technical debt is weighing the product down from a performance perspective, in additional to driving overly complex workarounds to problems that are easy to solve with normal SQL or tools that provide a thinner abstraction layer.

 

Joe Schmucker
Rank
Userlevel 6
Badge +3

@rosenjon I could have easily done this with a SQL stored procedure.  I could almost get away with a View, but I need to pass a parameter.  In that case, I’d need a Stored procedure (which I cannot do).  Some of my solutions would be so much more efficient if I could use a stored procedure.

I understand that Acumatica wants to lock down the database.  I don’t blame them!  But there are some cases where an exception is 1000% better than the workaround.  

Joe Schmucker, CPA

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved