RE: Returning Values from two different Tables

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

Duane,

I've noticed similar performance improvements when using CTE constructs.  I
suppose I always assumed that the CTE expressions were faster because it
potentially reduces the number of rows to process, which allows the joins to
process faster.  I generally use CTE to pre-summarize sales data before
producing report data...

with SlsCy (Cust, Sales, Cost, TrnCnt) as (
        Select Cst#, sum(Extd-Disc), sum(Cost), count(distinct order#)
        from saleshist where tdate >= '2005-10-01' and tdate <= '2005-10-31'
),
     SlsLy (Cust, Sales, Cost, TrnCnt) as (
        Select Cst#, sum(Extd-Disc), sum(Cost), count(distinct order#)
        from saleshist where tdate >= '2004-10-01' and tdate <= '2004-10-31'
)

/* Show all current-year customers, with their sales from last year.... */

Select SlsCy.Cust, SlsCy.Sales, SlsLy.Sales ....
>From SlsCy                                             left outer join 
     SlsLy on (SlsCy.Cust=SlsLy.Cust)

Union All

/* Now, add in the customers with sales last year, but not this year.......
*/

Select SlsCy.Cust, SlsCy.Sales, SlsLy.Sales ....
>From SlsLy                                             exception join 
     SlsCy on (SlsCy.Cust=SlsLy.Cust)



(I wish DB2/400 supported full join......)

A similar benefit seems to occur when you move filtering in a join statement
from the Where clause into the ON join criteria.  It's particularly
noticable when joining very large files into a summary result set, such as
for a dashboard sales report.  



Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Christen, Duane J.
Sent: Thursday, November 10, 2005 12:40 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Returning Values from two different Tables


Normally no, and I would have excluded it from the post if I would have
thought before posting, but with the common table expression I get better
response times than without. I ran both situations through visual explain
which didn't explain why the CTE was faster. At a guess the CTE hung around
in the background longenough to be reused.

Duane Christen 

-----Original Message-----
From: Wilt, Charles [mailto:CWilt@xxxxxxxxxxxx]
Sent: Thursday, November 10, 2005 12:11 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Returning Values from two different Tables


Duane,

You didn't need the common table expression.

This would have worked:
 C/Exec Sql                                                            
 C+ declare carelistcursor scroll cursor for                           
 C+    select pcpkey, pcseq#, pcstatus, pctrancode, pcstatusid, pcocn, 
 C+           pcdate, pcbtn, pcani, 0 as pcarcseq#                     
 C+    from pdpcare a                                                  
 C+    where pcprod = :product and pcpkey = :pKey 
 C+   union all                                                        
 C+    select pcpkey, pcseq#, pcstatus, pctrancode, pcstatusid, pcocn, 
 C+           pcdate, pcbtn, pcani, pcarcseq#                          
 C+    from papcare                                                    
 C+    where pcprod = :product and pcpkey = :pKey 
 C+  order by pcseq#, pcarcseq#                                        
 C+  for read only                                                     
 C/End-Exec                                                             

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Christen, Duane J.
Sent: Thursday, November 10, 2005 11:22 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Returning Values from two different Tables

Brian;

I just promoted a project this week with the same requirements. This is
the
embeded sql I built for my project. You can modify it for your needs.

 C/Exec Sql                                                            
 C+ declare carelistcursor scroll cursor for                           
 C+   with carelist as                                                 
 C+   (                                                                
 C+    select pcpkey, pcseq#, pcstatus, pctrancode, pcstatusid, pcocn, 
 C+           pcdate, pcbtn, pcani, 0 as pcarcseq#                     
 C+    from pdpcare a                                                  
 C+    where pcprod = :product                                         
 C+   union all                                                        
 C+    select pcpkey, pcseq#, pcstatus, pctrancode, pcstatusid, pcocn, 
 C+           pcdate, pcbtn, pcani, pcarcseq#                          
 C+    from papcare                                                    
 C+    where pcprod = :product                                         
 C+   )                                                                
 C+  select *                                                          
 C+  from carelist                                                     
 C+  where pcpkey = :pKey                                              
 C+  order by pcseq#, pcarcseq#                                        
 C+  for read only                                                     
 C/End-Exec                                                            

Duane Christen

-----Original Message-----
From: Brian Piotrowski [mailto:bpiotrowski@xxxxxxxxxxxxxxx]
Sent: Thursday, November 10, 2005 9:04 AM
To: RPG programming on the AS400 / iSeries
Subject: Returning Values from two different Tables


Hi All,

 

I have some SQL code in my RPG program that pulls data from two
different tables.  For example, in TableA I have some records that are
in a specific date range.  In TableB, I have some records that match the
same date range plus and additional parameter (ie: fieldb = 'XX').  I
then need to concatenate the results of both of these tables into a
single list.  So the data would look something like this:

 

Data Results:

==========

(TableA) 2005/11/04 11:04am

(TableA) 2005/11/04 11:06am

(TableB) 2005/11/04 11:07am

(TableA) 2005/11/04 11:09am

(TableB) 2005/11/04 11:10am

Etc..

 

Would I be better off to bring the select results into an array, sort
and display the results, or could I do this through and SQL statement
without having to worry about arrays and such?

 

Thanks for your thoughts.


Brian.

 

 

-=-=-=-=-=-=-=-=-=-=-=-=-=-

Brian Piotrowski

Specialist - I.T.

Simcoe Parts Service, Inc.

PH: 705-435-7814

FX: 705-435-6746

-=-=-=-=-=-=-=-=-=-=-=-=-=-