Re: Procedure: Selection from different schemas

[Joy Hoyte <JoyHoyte_sqr@HOTMAIL.COM>]

Andrew,

The If statement around the column name won't work because in dealing with
the Select statement, SQR first pulls out the SQL and builds a result set,
then goes back to process that result set row by row according to the SQR
commands within the Begin-Select.  So at the time it is actually
interpreting the SQL, it is not yet looking at any SQR commands that may be
within the begin-select.

To achieve what you are wanting to do,  you could set up a begin-select with
dynamic column variables.  In the same way that you use a variable for the
schema name, you can use variables for the column names.  Something like the
following example:

begin-select
[$column1]  &col1=number
[$column2]  &col2=char
etc.
    sqr commands

from [$schemaName].tbName
end-select

You can have as many column variables as you wish, but note that you have to
provide an &alias as well as the datatype for each variable.

Then BEFORE you call the procedure containing this select statement, you can
do the if-logic to assign the appropriate column names to the variables
depending on the schema that is in force.

E.g.

if $schemaName = '{THISSCHEMA}'
    move 'tbName.fieldName' to $column1
    move 'xyz' to $column2
    etc.
end-if

Hope that helps.

Joy Hoyte

----- Original Message -----
From: "Andrew Rivers" <andrew.rivers@CWCOM.NET>
To: <SQR-USERS@list.iex.net>
Sent: Friday, January 18, 2002 10:13 AM
Subject: Procedure: Selection from different schemas


> Hi
> I need to run an sqr program at different times against different schemas
and
> select fields which may be in one schema but not in another schema, for
example:
>
> begin-procedure getData local
> ...
> begin-select
> ...
>     !select this field which only occurs in this $schemaName
>     if $schemaName = {THISSCHEMA}
> tbName.fieldName           &tbNameFieldName
>     end-if
> ...
> from $schemaName.tbName
> ...
> end-select
> ...
> end-procedure
>
> The compiler sees the field tbName.fieldName and throws an exception
ignoring
> the condition.
>
> The work around is to select the procedure depending on which schema is
> connected:
>
> ...
> if $schemaName = {THISSCHEMA}
>       do getDataForSchema1
> else
>       do getDataForSchema2
> end-if
> ...
>
> Where the SQL selection in each of the procedures
> (getDataForSchema1/getDataForSchema) is specific to the fields in the
different
> schemas.
> This is not a good workaround since two very similar procedure need to be
> maintained.
>
> I have tried various solutions to pass without any success.
> If anyone has a solution/suggestion, I would be pleased to know.
>
> Many thanks.
>
> Andrew
>