AnsweredAssumed Answered

Execute SQL function

Question asked by tays01s on Feb 22, 2015
Latest reply on Feb 28, 2015 by philmodjunk

Title

Execute SQL function

Post

A couple of queries:

1. I'd got this calc field running:

If(ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed WHERE (Route='G' or Route= 'I') AND \"_IOID\"=?";"";"";IO 2::__ID)="";0; ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed WHERE (Route='G' or Route= 'I') AND \"_IOID\"=?";"";"";IO 2::__ID) )

I understand the need to use \"_IOID\" because of the underscore but I hadn't noticed the whole \"_IOID\"=?" has 3 " as opposed to the normal "XX" bracketing. Why is this?

2. I later realised that I really need not (Route='G' or Route= 'I') but a different indicator of route [Feeds::Route="e"]. However, this means referring to 2 fields from different tables. I've attempted the syntax below, but not getting it right.

If(ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed WHERE Route=? AND \"_IOID\"=?";"";"";Feeds::Route="e";IO 2::__ID)="";0;
ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed WHERE Route=? AND \"_IOID\"=?";"";"";Feeds::Route="e";IO 2::__ID)
)

All I get is '0' when the sum definitely isn't 0.

Outcomes