RE: Conditional SQL processing against host variables

Darren,

Try the IN predicate?

/Free
Exec SQL Declare C1 Cursor for
Select TWS,
TUSER,
TTDTE
From TFILE
Where :Device IN( '*ANY', TWS )
and :User IN( '*ANY', TUSER );
/End-Free

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of darren@xxxxxxxxx
Sent: Thursday, August 11, 2011 1:28 PM
To: midrange-RPG RPG message board
Subject: Conditional SQL processing against host variables


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