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

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