Pulling Data with 2 index columns from DB

I’m trying to pull data from a database using a process order # column as a primary key. the hurdle I am trying to get through is I need to a lot the material number to a tag in the PLC. I’ve attached a screen shot of my database for reference.

for example in the table I use the process order number as my index column but I need to also look at the material number and put the quantity of the ingredient in the right register in the PLC

Process order(idpk_test) # 2210 Material# 50002500 I need to evaluate both as index columns and move the quantity in value_column1 (1252) into the PLC tag.

any thoughts? this one has thrown me for a loop.

I’m not sure exactly what you mean. It sounds like all you’re looking for is

SELECT value_column1 FROM myTable WHERE idpk_test = 2210 AND material_num = 50002500

possibly adding [tt]LIMIT 1[/tt] to the end if you only need to retrieve one row.

Maybe I’m misunderstanding your question.

Hi Ted,

thanks for the reply. I will try to explain the scenario better. I’m using a transaction group on an event to search the database and move the ingredients for the associated process order number to tags in the PLC. I can use a group transaction with the process number 2210 as a index column. this works fine I pull the data into my tags from top to bottom. the issue is they may not put the ingredients in the same order every time (this would make my life too easy). so when I move my DB tags to my OPC for example I want material #1 to be word 0 of my array and material #2 to be word 1 etc. but if they put material #1 in material #2 position then when I move the quantity to the PLC I will have the values in the wrong spot. this is why I am trying to also evaluate the process order # with the material number to insure the quantity for material #1 say gets to the proper spot in the PLC.

so the variables I am dealing with are - process order # , Material #, Quantity. I am fairly new to ignition and database transactions but I cant seem to find a work around for this issue yet.