BAQ Fieldname IN Filter Value nvarchar x(50) limitation

Kinetic ERP
1

I am creating a BAQ that I want to return results from the PartRev table.
I have a parameter ‘D’ that is a Item List (nvarchar x100)
image

I am trying to see if the custom column DrawingNumber_C (nvarchar x100)
is IN the parameter 'D’s list.

image
image904×124 10.2 KB

I pass in multiple drawing numbers and expect to get all the matching results back.

If I pass in drawing numbers that are 50 characters or less it all works as intended. As soon as I pass in a drawing number that is > 50 characters it fails.
with an unhelpful error message: "Severity: Error, Text: Bad SQL statement. Review the server event logs for details. "

Same BAQ but instead of a list I do D = DrawingNumber_C with a 100 character string it works fine.

anyone know how to get around this issue?

3 Likes
2

Just for giggles, make it an x(1000). :popcorn:

3

x1000 has the same issue. Apparently not everything can be fixed the Texas way / making everything bigger. lol

1 Like
4

Go back and give us the broader picture. We are masters at workarounds.

1 Like
5

I am trying to return all the records from PartRev that match any of the incoming parameter values for DrawingNumber. The default drawingNumber field for that table was way to short for us so we have a custom one that is x(100) DrawingNumber_C.

I am sending multiple DrawingNumbers at once because I am trying to get all the relevant records for a bill of material at once rather than having a linear # of calls per bom item which would add a lot more overhead.

I could always re create this BAQ that only takes one parameter D instead of a list and compare that way for any item that has a drawingNumber > 50 but performance will take a severe hit for that.

6

How are you invoking the BAQ?

7

I did a test using a case statement to check the filter param.

I made a calculated field called Found bit with a value of 1

Then made this expression to look for the DrawNum in the DrawList and return 1 if found

Case when @DrawList like Concat('%',PartRev.DrawNum,'%') then 1 Else 0 End

image
image894×201 71.4 KB

it returned good data with a space separated list of 6 drawnums.

1 Like
8

@josecgomez I am invoking the BAQ through the EpicorRESTAPICore BAQ method
but same result when invoking the BAQ in the BAQ Designer in Epicor as well.

@gpayne When I try the expression you sent it complains about the @D parameter not being scalar
Must declare the scalar variable “@D”.

Case when @D like Concat('%',PartRev.DrawingNumber_c,'%') then 1 Else 0 End

Did you create a new parameter @DrawList that was not a list type and just dumped all the drawing numbers in one value?

9

Yes, DrawList was just a varchar 1000 space delimited parameter. You should see D in your Parameters.

image