I just did a test with visual explain, three statements to see the difference. In each case I am using a constant (71) where I would embed a host variable reference if I were doing this from RPG.
in predicate with column name on left:
select * from jcjobf
where cusno in (0, 71)
Visual Explain: index probe -> rrn Scan -> Table Probe -> Final Select
in predicate with column name on right
select * from jcjobf
where 71 in (0, cusno)
Visual Explain: Table Scan -> Final Select (as you suggested would be the case)
finally using case expression to filter out missing values
select * from jcjobf
where cusno = case when 71 = 0 then cusno else 71 end
Visual Explain: Table Scan -> Final Select
As you can see, using a case expression does not allow the optimizer to use an access path either. Maybe a dynamic sql with the appropriate where clause would perform better in some cases.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----"Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx> wrote: -----
To: "'RPG programming on the IBM i \(AS/400 and iSeries\)'" <rpg400-l@xxxxxxxxxxxx>
From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
Date: 10/01/2015 01:05PM
Subject: 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.
midrange.com
