Named query returns null for some columns only if run in a script

Ignition
1

I have a named query which returns valid values for all columns in the named query editor. If I crated a binding on a table as a named query binding, it also works fine. If I call the named query in a script using system.db.runNamedQuery, the last two columns return null. The first 7 columns return the same values. I have verified that the parameters are the same in both methods, even though I had no reason to suspect them (since 7 of the 9 column values match.)

2

Very unlikely anyone can tell you what, if anything, is wrong with your script without seeing it.

Please post it using the preformatted text (</>)

3

Without the script it’s hard to tell, my only guess is that no values return for those columns, named query will return an empty list while the python might see it as a None value and return a null.

Maybe modify the script to check for None values and fill it with a zero or some place holder string

4

I didn’t include the script and other methods because I was really expecting this to be some known bug, not a problem with my script. I’ll include all of my supporting evidence though to help figure if that is true, or if I have made some mistake.
FYI, this is an Oracle DB and the two columns that I am having an issue with are aggregate functions.
From the Named Query editor, this is the SELECT portion of the query:
SELECT B.BUSINESS_UNIT, C.DESCR60, C.UNIT_MEASURE_STD, D.INV_ITEM_HEIGHT, D.INV_ITEM_WIDTH, D.INV_ITEM_LENGTH, B.ITEM_FIELD_N12_A, SUM( E.TL_COST) AS COST_1, SUM( E.LL_COST) AS COST_2
It is COST_1 and COST_2 that are the issue.
When running from the editor:

image
image1410×472 37.9 KB

In the script:

	params = {'database':self.database, 'BU':self.BU, 'ItemID':CompID, 'version':self.version}
	details = system.db.runNamedQuery('AMP_IG_COMPONENTS DETAILS',params)
	self.testDS = details
	print params
	print details.getValueAt(0,'COST_1')
	print details.getValueAt(0,7) # just in case it doesn't like the col name
	return details

The printed reultts from the output console:
{‘ItemID’: u’R907022’, ‘version’: u’202201’, ‘database’: u’Peoplesoft_TST’, ‘BU’: u’EGRMI’}
None
None

And the actual query results from the scripted version placed in a dataset parameter:

image
image1384×93 9.04 KB

I think this shows that I am feeding the same parameter values using both methods but getting different results.

5

Also, I tried removing the ‘AS COST_1’ part but that only changed the col name in the result (as expected). The values were still null.
version is: 8.1.9

6

Check case of Version versus version.

1 Like
7

You didn’t include the WHERE and GROUP BY statements for the QUERY.

The named query calls for 4 parameters, but you didn’t show where and how those are used in the query itself.

Also, when supplying the Keys for the params dictionary the case must match what was used in the Named query.

I suspect that the Version parameter has some effect on the grouping for the aggregate functions and since the script (at least the version you provided) is not suppling a Version key then that parameter is effectively null.

8

You are correct. I supplied version instead of Version. Thank-you.

1 Like