NamedQuery help

Ignition
,
1

Good day, I have this named query:

SELECT TOP (1) *
FROM Buggy_Corral_Contents
WHERE 
     Lot_Num LIKE :LotLabel1
    OR
    Lot_Num LIKE :LotLabel2
    OR
    Lot_Num LIKE :LotLabel3
    OR
    Lot_Num LIKE :LotLabel4

That populates a table. My issue is when I have 4 parameters to pass in it returns results but when I have anything but, it doesnt return results. How can I modify this return even if there is less parameters to pass in? The parameters come from a label that looks like this:
image
image

image
image565×511 16.9 KB

2

Like is a pretty poor parameter in sql.
If the lots are actually equal then you can do the query as

WHERE Lot_Num IN (:LotLabel1,:LotLabel2,:LotLabel3,:LotLabel4)

3

Wrap each LIKE parameter in the SQL wildcard character, which is the percent sign ( % ). Instead of querying for LIKE 'O5311G16', you will need to query for LIKE '%O5311G16%'. This means, if there is no lot number inputted, it will filter the query to only find things LIKE '%%', which is the same thing as not filtering at all. Hopefully this makes sense. You may need to play around with the formatting of the named query or the script transform to get that to work, maybe by changing "Value" to "QueryString" in the named query authoring section or by adding some lines of code to modify your lot numbers before passing into the named query.

4

Well, I tried both suggestions and I am still not returning results. I appreciate the ideas but need some more help. I was wondering if I could do something like this:

if({../LotLabel.props.text[2]} = null,'No Lot',{../LotLabel.props.text[2]})

That way if there was only 2 lots and not a third then it would make the missing lot 'No Lot', so it would atleast have some value to filter off of. BUT the expression returns a Bad_Stale error.

Anyway around that?

5

I found a solution. Its not ideal but it works and needed it to, lol.

1 Like