Is it possible to check for a number parameter in named query?

Ignition
1

Hello all,

I am trying to check for a value in my named query and that value is an integer parameter.
The parameter is called partNo
The name query is where I am checking for the word SN and the parameter partNo integer value.
As an example, the serial number is "SN 1527"

SELECT * FROM orderData
WHERE serialNo Like '%SN% :partNo'

I am not able to find the correct syntax for this and I tried so many different syntaxes but none f them worked so far. I would appreciate your help in this regard.

2

I think the issue is the query itself? Best case scenario, you query would be

SELECT * FROM orderData
WHERE serialNo Like '%SN% 1527'

which I’m not sure makes sense. Or maybe I am not well versed enough on the LIKE operator, but normally a % is used in the beginning or the end to allow any wildcard characters before and after, so to then put a hardcoded value after that seems counter to allowing wildcard. But maybe this is valid.

Does this SELECT * FROM orderData WHERE serialNo Like '%SN% 1527' query work correctly if you run it directly?

1 Like
3

Thank you very much!
The parameter is dynamic so it will not be always 1527 and the operator change that in a vision view.
I even tried ‘%SN% 1527’ but it did not return any rows.

4

You can use concat to merge strings

SELECT * FROM orderData
WHERE serialNo Like concat('%SN%',  :partNo)
2 Likes
5

Right! concat should work!
Now I do not get any errors but I still cannot see any rows returned.
So the complete example is:
serialNo: SN 1527 Pallet20

I still need to have % for pallet20, right?

SELECT * FROM orderData
WHERE serialNo Like concat('%SN%',  :partNo, '%')
6

I just tried the last one and it is working!!! Thank you so much everyone.

7

The % character used in this manner is valid. You are saying return any value begining with any combination of characters followed by SN followed by any combination of characters and ending with 1527.

That should work so long as you're only concerned with the partNo matching.

2 Likes
8

Thank you so much!!! I just tried that and it worked :slight_smile:

9

Be careful with %!
if your palet also contain number you will get a hit on the numbers in palet too

if you are looking for the number right after SN use _ or just a space

SELECT * FROM orderData
WHERE serialNo Like concat('%SN_',  :partNo, '%')

SELECT * FROM orderData
WHERE serialNo Like concat('%SN ',  :partNo, '%')
1 Like
10

Correct! I used “_” instead of “%”.
Thank you, Victor. Appreciate your information.

1 Like
11

There is just a problem with number 1 only so when the operator checks for partNo=1

it returns:

SN 1 Pallet…
SN 11 Pallet…
SN 1527 Pallet…

For this matter, the new script should be:
(I added a space before the last %)

SELECT * FROM orderData
WHERE serialNo Like concat('%SN_',  :partNo, ' %')
12

Using LIKE is not always the best practice for queries. I would make your db serial column hold only the integer and not the SN part. Then you would have another column named pallet. This will make your life much easier and your queries more efficient and accurate. Then if you want to display it as SN 1527 Pallet20 you would use

select concat('SN ', serial, ' Pallet ', pallet) as serialNo
from orderData 
where serial = :partNo
6 Likes
13

Hardly ever, I would say. Using LIKE is a big flag that the table doesn't have a good structure.

5 Likes
14

Another option if you cant restructure your table would be to use `SUBSTRING()’

Something like:

SELECT * FROM orderData
WHERE SUBSTRING(serialNo,4,LEN(:partNo)) = :partNo
2 Likes
15

I agree! I will try to adjust the table column in the future if It will be possible. That would make everything easier to interact with. Thank you!!!

16

Good information!! Appreciate it.

17

Great information. I was not aware of this! Thank you, Phill!