AW: AW: Syntax for Insert into using an rpg data structure, 1 row

Where :HostInput1 in (0,Column1)
and :HostInput2 in (' ', Column2)

With your solution, the optimizer cannot use any access path over column1
and column2!

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 [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von
darren@xxxxxxxxx
Gesendet: Thursday, 01.10 2015 17:09
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: AW: Syntax for Insert into using an rpg data structure, 1 row

Declare MyCursor Cursor For
Select ...
From ...
Where Column1 = Case when :HostInput1 <> 0 then :HostInput1 Else
Column1 End
And Column2 = Case When :HostInput <> '' then :HostInput2
Else
Column2 End
And Column3 > '2000-01-01' Then :HostInput3 else Column3 End
....
Order By Case When :Sort = 1 Then Column1 End, Case When :Sort = 1

Then

Column4 End

I've discovered a little trick as an alternative to the case statement for
equal type comparisons. It reverses the traditional usage of host
variables. Here is the statement rewritten as an example:

Declare MyCursor Cursor For
Select ...
From ...
Where :HostInput1 in (0,Column1)
and :HostInput2 in (' ', Column2)

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (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.