Re: Dynamic Select based on previous query

["Bhowmik, Bhaskar" <Bhaskar.Bhowmik@STAPLES.COM>]

Hello Jay,

Here is a quick procedure I put together for you. Hope it helps.

If you are not sure of the datatype and the number of columns. Then build
a dynamic Column list as $Column_list_1. Concatenate a field separator which
you
can strip later with an UNSTRING. You would have to build the data type
conversions
into the string because the select will return a big CHAR string.

If you are sure of the number of columns and datatype. Follow as in
$Column_list_2


!***************************************************************************
***
begin-procedure Dynamic-select1
!***************************************************************************
***

        display 'Flow - Dynamic-select1'

move '''' to $SQL-Text-Char

Let $Look_For = 'DIRECTDEL'
Let $Where_Clause = 'WHERE A.IC_PROG_ID = ' || $SQL-Text-Char || $Look_For
|| $SQL-Text-Char
Let $From_Table = ' PS_IC_GRP_MEM A'
Let $Column_list_1 = 'A.EMPLID ||' || $SQL-Text-Char || ':' ||
$SQL-Text-Char || ' || A.IC_GRP_ID'
Let $Column_list_2 = 'A.IC_UNITS'
begin-select
[$Column_list_1]                &Data_Fetched_1=CHAR
[$Column_list_2]                &Data_Fetched_2=NUMBER

        unstring &Data_Fetched_1 by ':' into $Col_1 $Col_2
        show $col_1 ' ' $Col_2 ' ' &Data_Fetched_2

FROM [$From_Table]
[$Where_Clause]
end-select

end-procedure Dynamic-select1


Regards...
BB


-----Original Message-----
From: Gottdenker, Jay S [mailto:jgottden@INDIANA.EDU]
Sent: February 18, 1999 9:15 AM
To: Multiple recipients of list SQR-USERS
Subject: Dynamic Select based on previous query


> Is there some way in sqr to achieve the equivalent of:
>
> **** code starts here *********
>
> let $allfields = 'field1, field2, field3, field4'
>
> begin-select
> [$allfields]
> from table1
> end-sql
>
> ******** code ends here ************
>
> note the following constraints:
>
> 1.- we set $allfields dynamically based on the results of a previous query
> 2.- we need to be able to get to the data in the fields in $allfields, so
> we can't just use begin-sql
>
>