As I was reading through the responses I was wondering if anyone would
suggest this. Thanks to Birgitta, from many times before, this is the
technique that I use for this type of thing all the time. It works very
well is very fast.
Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777
"Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
08/12/2011 01:17 AM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
To
"'RPG programming on the IBM i / System i'" <rpg400-l@xxxxxxxxxxxx>
cc
Subject
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
--
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.
--
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.
--- This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us and destroy this message immediately. ---
midrange.com
