Re: SQL Problem

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

Hi Rob,

>        stmt='Select cmpny, cmpnam, cmpost ' +
>             'from rco ' +
>             'where cmpny between :lowCmpny and :highCmpny ' +
>               'and cmpnam between :lowCmpnam and :highCmpnam ' +
>               'and cmpost between :lowCmpost and :highCmpost ' +
>               'order by ?';

No, this isn't valid.   Since you are building the SQL statement 
on-the-fly, you can't use :lowCmpny or any other variable that starts 
with a colon.  That code is normally handled at compile-time by the SQL 
precompiler -- and nothing handled at compile-time will work in this 
case, because you're building the SQL statement using string 
concatenation at run-time.   You could, however, replace those with 
parameter markers (question marks) and fill them in that way.

You also can't use a parameter marker (the question mark) for "order 
by".  Parameter markers can only replace variable/column values.  they 
can't replace code that's used to develop the query plan.

in any case, your statement is a bad example of dynamic SQL because it 
could be done (actually easier) as a static statement.  (Notice how I 
handle the order by:)

  exec SQL declare C1 cursor for
      Select cmpny, cmpnam, cmpost
        from rco
       where cmpny between :lowCmpny and :highCmpny
         and cmpnam between :lowCmpnam and :highCmpnam
         and cmpost between :lowCmpost and :highCmpost
       order by case
                when :sortby = '1' then cmpny
                when :sortby = '2' then cmpnam
                when :sortby = '3' then cmpost
                end