NamedQuery Parameter Sorting

Ignition
1

Hi all,
Looking for some help with sort of parameters passed in a named query. When I pass the below params, the response back is out of order. (returns 3, 4, 1, 2) -- when a try the sorted() function on the params to get the order i need, it comes back properly ordered, but looks like it's in java notation, which then the namequery throws error because if it. Any thoughts to best method here to pass in parameters in a certain order and get back the values in that order?

#tagCal values are set by tag reads in a few lines above
params = {"parCal1":tagCal1, "parCal2":tagCal2,"parCal3":tagCal3,"parCal4":tagCal4}
sortedDict = sorted(params.items())
print params
print sortedDict
query2 = system.db.runNamedQuery("nq_jconn3_mettrack_prod" , params)

parCal1 = str(query2.getValueAt(0,0))
DueDate1_java = str(query2.getValueAt(0,7))

parCal2 = str(query2.getValueAt(1,0))
DueDate2_java = str(query2.getValueAt(1,7))

parCal3 = str(query2.getValueAt(2,0))
DueDate3_java = str(query2.getValueAt(2,7))

parCal4 = str(query2.getValueAt(3,0))
DueDate4_java = str(query2.getValueAt(3,7))

results from para query
results from para query1313×395 63.2 KB

2

Why does the order of the params matter?

3

Thank Kevin. When I pass in a certain order, I"m using the below getValueAt(x,y)... so zero would be reply back to the first parameter passed. What is confusing me now is (0,0) is returning the values from parameter 3. I was expecting the values from parameter 1.

parCal1 = str(query2.getValueAt(0,0))
DueDate1_java = str(query2.getValueAt(0,7))

4

The order of the parameters you pass does not effect the results you get.

What does this named query look like?

5

named query
named query906×596 68.2 KB

6

Ok, you see how these parameter values are just pulled out by name, and the actual order is part of the query structure?

7

yeah I think I know where you are going here. The ORDER BY is controlling how the data retrieved.

8

Not just that, but the query you've written only guarantees you get 4 rows back, and that each row's I4201 column value matches one of your input parameters.

It does not guarantee that you get 1 row per input parameter, and certainly not in any order.

9

Thanks.. yeah I'm re-writing the query.
What I believe I want is a group by function, returning the latest calibration date, grouping by the ID.

10

FYI -- cleaned up the query and python scripting to which the order of the parameters doesn't matter. I set up a dictionary input (key, param_in) and dictionary output (key, query_out) --- join the results based on the key and parse the tuple array from the result:

Query (aggregate results):
SELECT iCalID, max(cDateDue) as DueDate, max(iDescription) as xDescription -- max(iManufacturer) as Manufacturer, max(iModel) as Model,
FROM (
    SELECT I4201 as iCalID, I4202 as iManufacturer, I4203 as iModel, I4204 as iDescription, I4225 as iCalStatus,  
    DATEFORMAT(C2301, 'YYYY-MM-DD') as cDateLastCal, C2323 as cCalPass, DATEFORMAT(C2303, 'YYYY-MM-DD') as cDateDue
    FROM "mt"."Inventory" INNER JOIN "mt"."Calibration" ON "mt"."Inventory".MTAG = "mt"."Calibration".MTAG 
    WHERE "mt"."Inventory"."I4201" = :param1 or  "mt"."Inventory"."I4201" = :param2
            or "mt"."Inventory"."I4201" = :param3 or "mt"."Inventory"."I4201" = :param4
    ) as results
    GROUP BY iCalID

Python scripting:


tagCal1 = str(system.tag.readBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/AFT/Station10/Cal-ID"])[0].value)
tagCal2 = str(system.tag.readBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/APS/Cal-ID"])[0].value)
tagCal3 = str(system.tag.readBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/LaserWelder/Cal-ID"])[0].value)
tagCal4 = str(system.tag.readBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/UltraSonicWelder/Cal-ID"])[0].value)

#pass values from tags into a dictionary for named query call
params = {"param1":tagCal1, "param2":tagCal2,"param3":tagCal3,"param4":tagCal4}
#print params

#Call the named query (found in project browser, named queries)
pyData = system.db.runNamedQuery("nq_jconn3_mettrack_prod" , params)
   
#brute force to get the results from the query
result_Cal1 = str(pyData.getValueAt(0,0))
result_Due1 = str(pyData.getValueAt(0,1))
result_Cal2 = str(pyData.getValueAt(1,0))
result_Due2 = str(pyData.getValueAt(1,1))
result_Cal3 = str(pyData.getValueAt(2,0))
result_Due3 = str(pyData.getValueAt(2,1))
result_Cal4 = str(pyData.getValueAt(3,0))
result_Due4 = str(pyData.getValueAt(3,1))

#define input and output dictionaries. We will key on CalID for final results
dict_input_swap = {v: k for k, v in params.items()} # swap key value pair
dict_output = {result_Cal1:result_Due1, result_Cal2:result_Due2, result_Cal3:result_Due3, result_Cal4:result_Due4}
#combine input and output dictionaries, key on CalID since it is common for both
from collections import defaultdict
dd = defaultdict(list)
for d in (dict_input_swap, dict_output):
    for key, value in d.items():
        dd[key].append(value)
final = dict(dd)

#Parse through a dictionary of tuples
#key = CalID, tuple[0] = parameter, tuple[1] = due date
for key in dd.keys():
	param = dd[key][0]
	calID = key
	expiryDate = dd[key][1]
	if param == "param1":
		system.tag.writeBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/AFT/Station10/Cal-Expiry"],[expiryDate])
	if param == "param2":
		system.tag.writeBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/APS/Cal-Expiry"],[expiryDate])
	if param == "param3":
		system.tag.writeBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/LaserWelder/Cal-Expiry"],[expiryDate])
	if param == "param4":
		system.tag.writeBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/UltraSonicWelder/Cal-Expiry"],[expiryDate])