Re: Procedure: Selection from different schemas

[Andrew Rivers <andrew.rivers@CWCOM.NET>]

Joy
Thanks for your suggestion.
I have tried your suggestion.

The problem is that the table has different fields in the two schemas and some
fields are only in one schema.
I had tried moving a null value into the dynamic variable for the case where
the schema did not have the particular field – This didn't work.

However,  your suggestion broke my mental block and I can see a resolution...
I can move a duplicate field into the dynamic variable for the case where the
schema does not contain the field which is only in the other schema.

I have every confidence that the solution will work...

Many thanks indeed.

Andrew

_____________________________________________
Quoting 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
> >
>