6.1 Query of Query Question. Ref to own Col

[smcgovern]

Hey

I'm running a QoQ using session variables - every thing works fine until I try
to refer to a QoQ column. Let me write the example:

1. application.qoq contains a valid dataset from a direct query to MS SQL

SELECT id,somenumber,#(2*somenumber)# AS result FROM application.qoq

This simply won't work - of course this sort of thing is normal in a direct dB
SQL query. Let me write an example:

2. table1 is a valid table in MS SQL

SELECT id,somenumber,(2*somenumber) AS result FROM table1

The things that don't work are:

3. SELECT id,somenumber,#(2*application.qoq.somenumber)# AS result FROM
application.qoq

Because the reference for application.qoq.somenumber always returns the 1st
column of the application.qoq stored dataset

There has to be an answer to this, as it can be done using structures (self
referall) and essentially the datasource object is a structure variant.

Help!

[MikerRoo]

This normally works for me.

Did you try correct SQL syntax?

EG Not:
"SELECT id,somenumber,#(2*somenumber)# AS result FROM application.qoq"

But:
"SELECT id,somenumber, (2*somenumber) AS result FROM application.qoq"

-- MikeR

[smcgovern]

Yep

It just comes back with 'Variable SOMENUMBER is undefined'

[smcgovern]

Let me add in that I also perform a function: e.g.,

SELECT id, somenumber, sin(2*somenumber) AS x FROM application.qoq

The problem seems to be that unless you encapsulate the function
sin(2*somenumber) in '#' marks it isn't calculated. But once you do, it thinks
somenumber is a variable from outside the record set.

[MikerRoo]

You've changed the problem!

Apparently, Q of Q does not support the SQL version of the sin() function -- a
reasonable design tradeoff but you might open a feature request.

You can use non QofQ-SQL functions in a QofQ query but with unexpected
results. For example,
#sin (2 * application.qoq.somenumber)# AS x
will work but always returns the calculation based on the first row.

The best thing is to perform "fancy" SQL in your RDBMS and only use QofQ for
sorting, repackaging, super-unions, etc.

Or you can loop through the query. Or you an use the undocumented meta
functions.

-- MikeR