Running A Sum On Named Query

I want a label to display the sum of a singular column of a named query. I am doing it in the expression for binding and have written this be it is not working.

"Total Hours Remaining: " + sum(system.db.runNameQuery("Select_DispatchList_Assembly", "5734"), 5) 

5 is the index of the column and the 5734 is a parameter (and only parameter in the named query) that I am passing through

  1. It is system.db.runNamedQuery, with a d in named
  2. system.db.runNamedQuery returns a Dataset, not a PyDataset, which is what you need to use a sum (and a column number)

So something like this would work

ds = system.dataset.toPyDataSet(system.db.runNamedQuery('someQuery'))
result = sum(ds[0])

You can't mix the expression language with the scripting language in Ignition either.

1 Like

sum is a python/jython keyword you can use on PyDataSet.

However now that you say that yes I see how they are doing it is wrong.

it shouldbe system.dataset.toPyDataSet(system.db.runNameQuery("Select_DispatchList_Assembly", "5734"))[5]

But the way it's used here (with the column index as second argument) suggests a mix up, as this is the signature for the expression function

1 Like

I'm talking about the running of a named query in an expression.

1 Like

Oh boy I need to get more coffee before I give anyone else any advice. That's now two things I missed I shouldn't have ha.

2 Likes

I'd use getColumnAsList instead of converting to a pyDataSet.

No, actually, I'd use an expression.

Thank you so much I can't believe I forgot the d in Named with the amount of times I've written that.

I will try running the code listed in a event configuration and set result to a custom property so that I can reference it in the expression like dkhayes117 pointed out.

Thank you all so much!

To be clear, you can mix the expression language with scripting indirectly, you just have to use runScript() to do it.

I would just make an additional custom Named Query binding property with your NQ and reference the dataset with sum(myNewCustomPropDS, 5) that way and avoid runScript for this particular use case.

1 Like

Oh I didn't know that you could do that. This might be a dumb question but would I encapsulate the script in the runScript parenthesis? Or just after the script as call?

Frankly, you don't need events.

Show us the whole thing, because I feel like there's a very simple and clean way to do this.

I agree, but a secondary property might not even be needed, depending on what the original binding is.

No, you define it as a project script function, then call it with the runScript syntax. However, this is probably not the answer for this problem.

2 Likes

I can totally see a simple way of doing it. There aren't alot of components to it but I have below the full named Query:

SELECT
	sch.job 
	,sch.oper_num 
	,job.job_date orig_job_date
	,sch.pcs_per_lbr_hr pcs_per_hr
	, route.qty_received / sch.pcs_per_lbr_hr * 1.0 total_planned_hrs	
	,item.item 
	,item.description item_description
 
FROM
	jrt_sch_mst sch
	LEFT JOIN job_mst job on sch.job = job.job and sch.suffix = job.suffix 
	LEFT JOIN jobroute_mst route on sch.job = route.job and sch.suffix = route.suffix and sch.oper_num = route.oper_num
	LEFT JOIN JER_BI_Items item on job.item = item.item
	LEFT JOIN JER_BI_WCs wc on route.wc = wc.wc
	LEFT JOIN (SELECT distinct
					job
					, suffix
					, oper_num
					, next_oper
				FROM 
					jobtran_mst 
				WHERE 
					next_oper is not null
				) jtrans on sch.job = jtrans.job and sch.suffix = jtrans.suffix and sch.oper_num = jtrans.next_oper
	LEFT JOIN jobroute_mst prevRoute on jtrans.job = prevRoute.job and jtrans.suffix = prevRoute.suffix and jtrans.oper_num = prevRoute.oper_num

WHERE 
	route.wc = :wc
	and job.stat = 'R' 
	and prevRoute.complete = 1

	and route.complete <> 1
	and route.qty_received > 0
	
ORDER BY
	job_date -- -- Sorted by Earliest Job Start Date 

and I am just trying to take the 6 column or 5th index and sum that.

From there in a view I want to display this sum, for right now this project is in basic stage so we are not pulling all the wc and just starting with 5734

I don't know if this helps any to understand more?

Change your binding type to a query binding, then add an expression transform like this:

sum(value, 5)

Is this perspective? He hasn't said.

IMO and I know it's just a style thing but I think 2 custom properties is the cleanest nicest way to do it.

1 custom property for your dataset

1 custom property summing your dataset

If you ever get results that seem weird you can open up designer and manually inspect the dataset now (or maybe you display it for people with special roles for troubleshooting no designer access required).

I always like to be able to inspect the dataset that is generating the result for debugging.

it is perspective so sorry

Thank you so much. I knew it would be more simple than what I was making it out to be.

This worked perfectly!