Nesting nuSQL queries

[nickrth]

Hi there,

I'm hoping someone can please help advise, can you have a nuSQL refer to another nuSQL.

That is, I have a query, that relies on a sub query. And in fact, that sub query, also has a sub query. Rather than repeat that base SQL directly within each of the other two queries, I want to be able to refer to that base query, so that if I make changes I only have to make it in the one place.

In very basic pseudocode terms:
select * from table as qr1
select * from qry1 join table2 on id=id as qry2
select * from qry2 join table3 on id=id as qry3

I'm trying to avoid:
select * from table as qr1
select * from (select * from table as qr1) join table2 on id=id as qry2
select * from (select * from qry1 join table2 on id=id as (select * from table as qr1)) join table3 on id=id as qry3

Thanks,

Nick

[kev1n]

Hi,

Nested queries are not supported. Maybe you can use PHP to create the queries

[nickrth]

Thanks for your quick reply Kevin.

We've been trying that path too, but also struggling. We can't seem to get a procedure to call another procedure and build up the SQL properly.

Can a nuSQL refer to a temp table created by a procedure?

[kev1n]

Is the nuSQL used for reports?

[nickrth]

Both reports and forms.
We have a set of nesting procedures that each echo a string consisting of SQL code, and then a set of procedures that then run the query and create a temporary table.

We got it working Monday night, the trick was to use an output buffer when evaluating the result of the nuProcedure function, and to use unique function and variable names for each layer of the nesting.

Code: Select all

// function that loads another procedure and evaluates it, returning a string
function get_qry1($proc_name1) {
    $raw_qry1 = nuProcedure($proc_name1);
    ob_start();
    eval($raw_qry1);
    $evalqry1 = ob_get_contents();
    ob_end_clean();
    return $evalqry1;
}
//run query to create temporary table with result of function
nuRunQuery("CREATE TABLE #TABLE_ID# " . get_qry1('s_dinerserve'));