Re: Conditional SQL processing against host variables

[Jeff Young <jyoung0950@xxxxxxxxx>]

Try using a Low / High range in your where statement as follows:
If Device = *ANY;
  From_Device = *Blank;
  To_Device = '99999'; // as many as needed
Else;
  From_Device = Device;
  To_Device = Device;
EndIf;

SQL Where -
... Where TWS between :From_Device and :To_Device .....

This will allow you to select all records when *ANY is entered, or just the
record you want if a value is entered.

Jeff Young
Sr. Programmer Analyst



On Thu, Aug 11, 2011 at 1:27 PM, <darren@xxxxxxxxx> wrote:

> Using something like the following statement, we take a pretty high
> performance hit compared to writing multiple cursors for times when the
> user has selected a wildcard (*ANY) as shown below.  I suspect that the SQL
> is actually looking for TUSER='*ANY' even though it will never find it, and
> its not necessary.  Is there a better way to condition this type of
> operation?  I'm aware of dynamic SQL, but its a bit of a pain for larger
> statements.
>
>
> /FREE
> exec sql declare C1 cursor for
>        Select TWS
>               TUSER,
>               TTDTE
>        from TFILE
>        where (TWS = :Device or :Device = '*ANY')
>        And (TUser = :User or :User = '*ANY')
> /END-FREE
>
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.