For an application I am using a barcode scanner that returns 16 digits numbers like this:
1306048,012,01,0001
This number is going to be stored in a numeric text field and then has to be broken down into 4 tags, which I already did thanks to all people who helped me here. Something like the table below: (all 4 highlighted tags and zeros have to be deducted). I use those 4 tags to query from an SQL table
I used the code below to split the 16 digits number into 4 tags:
if previousValue.value != currentValue.value:
numIn = currentValue.value
textIn = str(numIn)
slices = [0,7,10,12,len(textIn)]
values = []
for i, j in zip(slices,slices[1:]):
val = int(textIn[i:j])
values.append(val)
system.tag.writeBlocking(["[~]ordno"],[values[0]])
system.tag.writeBlocking(["[~]mdlcnt"],[values[1]])
system.tag.writeBlocking(["[~]patpos"],[values[2]])
system.tag.writeBlocking(["[~]qty"],[values[3]])
Now, I want to be able to update that 16 digits number when I enter Lot No and Bin data. So that I can query from the SQL table based on new data of those 4 tags.
In the SQL table, I can select the row based on Lot and Bin data but this is not what I want, I want to be able to get a single number which is the combination of ord no, mdl cnt, pat pos, and qty with all zeros (16 digits in total).
Do you know what would be the best approach to tackle this question?
It was hard to explain this issue well and even select a subject for this question but I hope I was successful in doing it!
Thank you!
First off, when you write your tags, do them all at once for more efficiency
if previousValue.value != currentValue.value:
numIn = currentValue.value
textIn = str(numIn)
slices = [0,7,10,12,len(textIn)]
values = []
for i, j in zip(slices,slices[1:]):
val = int(textIn[i:j])
values.append(val)
paths = ["[~]ordno","[~]mdlcnt","[~]patpos","[~]qty"]
system.tag.writeBlocking(paths,values)
As for the question, it seems like you want to do the opposite of splitting the scan and query. Now you want to create a barcode serial of 16-digits from the the database from a lot and bin number ?
That would be something like the following as a scalar query (return a value only)…
select concat(lpad(ordno,7,0), lpad(mdlcnt,3,0), lpad(patpos,2,0),lpad(qty,4,0)) as barcode
from your_table
where lotno = :lot and bin = :bin
This query is my_sql/mariad_db syntax and could be different for other DBs.
I am running this query from a query tag called “barcode manual”.
I have my Lot and Bin tag as a memory tag just to use it as an example later on I will make sure that the user can assign this number from a numeric text field.
Hmmm, hard to tell. The error says Invalid column name '112742'. For some reason, it thinks the lot no is a column, probably a syntax error in the query some where?
I checked there was nothing. Actually, I had other querries before with this code and it worked fine:
WHERE
"ord no"= {[default]ordno.value}
So I think not having the path in quotes would work, the thing is if I do not put the path in quotes I get a zero as my value and if I put it in quotes I get the error.
I even tried to change the tag data type to Long so that if it returns 16 digits it uses the correct data type.
And this is my query:
SELECT CONCAT (RIGHT ('000'+ 'ord no',7), RIGHT ('00'+ 'mdl cnt',3), RIGHT ('0'+ 'pat pos',2), RIGHT('000'+ 'qty',4))
FROM Sheet1$
WHERE "Lot No" = {[~]LotManual.value}
AND
"Bin" = {[~]BinManual.value}
copy the query into the database query browser tool and hard code your tag values, just use a value not a tag path, and see if you get expected results. If you don’t, modify the query until you get the expected result. Then copy the query back to the tag and insert your tag path variables.
And for the result, I got this which is basically concatenating the column name, not their numbers and it is doing that based on the numbers I assigned for it (i.e., RIGHT ('00'+ 'mdl cnt',3). and it is returning “cnt” only.
I am trying to figure this out and see how to return the column values instead of their names and I will post the solution here.
It seems it is treating your column names as strings and not columns because of the quotes. I do not like using spaces in my column names so I can avoid this. I would recommend naming your columns with underscores instead of spaces, no spaces and camel-case naming conventions, or something along those lines.
Instead of that code, I wrote this in the SQL server database query browser:
SELECT CONCAT (RIGHT('000'+CAST([ord no] AS int),7), RIGHT('000'+CAST([mdl cnt] AS VARCHAR(3)),3), RIGHT('00'+CAST([PAT POS] AS VARCHAR(3)),2), RIGHT('0000'+CAST([qty] AS VARCHAR(4)),4)) as Barcode
FROM [Table Name].[dbo].[Sheet1$]
WHERE
"Lot No" = 112742
AND
"Bin" = 26
This is also the equivalent code in SQL query Ignition:
SELECT CONCAT (RIGHT('000'+CAST("ord no" AS int),7), RIGHT('000'+CAST("mdl cnt" AS VARCHAR(3)),3), RIGHT('00'+CAST("PAT POS" AS VARCHAR(3)),2), RIGHT('0000'+CAST(qty AS VARCHAR(4)),4)) as Barcode
FROM Sheet1$
WHERE
"Lot No" = 112742
AND
"Bin" = 26
And it worked!!! Now I have the new 16 digits barcode.
Just a FYI… it would be best practices to either create a computed column or a view to return the barcode value rather than having to remember that formula everytime you needed the barcode value.
