SUM SQL Query In Same Session

[Brady Fearnow]

I am trying to get the sum of a column with in a session when a user scans a barcode. The following is on a SQL variable field. Basically, when the index is entered of the item, I want to get the sum of a column for all references to the index.

SELECT SUM(Session6.Field14) FROM Session6 WHERE Session6.Field8='999'

Above is what I am using. The session I am performing this in is also session 6. Can I do this? I set the field to a static number for testing purposes.

 

[Brady Fearnow]

I even tried just the following and no dice:

SELECT COUNT(*) FROM Session6;

There are 7 records currently. What am I doing wrong? Thanks.

 

[OliverR]

What type of values are you trying to sum? Are they integers or decimal values? I had a similar "problem" a while back where I wanted to calculate the sum of a field that contained decimal values. The trick then was to cast it as another datatype.

You can find the thread here. Maybe that helps you?

EDIT: I'm sorry, I just properly read your second post. If a simple count query isn't returning any results then the problem probably lies in the way that you're trying to do it. Do you get any value in your field? Any errors? Is the trigger field being triggered?

 

[Brady Fearnow]

Trigger field is being triggered. It returns '0' for the simple count query. I followed a tutorial for testing and made a button use the fieldinput button action, target control to a test field, and the test field is the trigger for the field with the SQL variable. Thanks for any help.

 

[Brady Fearnow]

Now I have even tried using form logic on an OnClick event for a button using:

SELECT COUNT(*) FROM Session6

and the Action Control ID updates from the default value of -1 to 0.

I even tried

SELECT 9 FROM Session6

thinking it might return the number 9 and no dice.

 

[Brady Fearnow]

Okay so I see some weird stuff for a couple of sessions. In the "View Data" on the device, the column headers are not making sense with what I have setup in tracerplus connect. I think there is a bigger issue at play. Waiting on support.

 

[Joseph Kraebel]

Hi Brady,

I just replied to your ticket regarding the grid issue but wanted to clear up the SQL issues you are having. It appears you are using Live mode which custom SQL is not supported in and why you are seeing issues with it. SQL statements can only be executed on local data stored on the device and would need to be used in a batch type mode to use them. I also commented on this in your ticket.