AW: Conditional SQL processing against host variables

... another way doing this with static SQL is:

Exec SQL Declare ....
Select ...
From ...
Where TWS = Case When :Device <> '*ANY' Then :Device Else TWS End
And TUser Case When :User <> '*ANY' Then :User Else TUser End

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von darren@xxxxxxxxx
Gesendet: Thursday, 11. August 2011 19:28
An: midrange-RPG RPG message board
Betreff: 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