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

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