midrange.com code scratchpad
Name:
How IBM DB2 builds SQL Join from DDS JOIN
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
10/03/2012 11:44:06 pm
IP:
Logged
Description:
A message on MIDRANGE-L list raised the question of how to achieve a particular DDS-coded join in SQL.

It got me wondering how IBM would do it. This answers that question. First two PF are created (PFILE1 and PFILE2). Then they are joined by JFILE1. Then the QSQGNDDL API is used to retrieve the SQL source of JFILE1. Details and results below.
Code:
  1. PFILE1:
  2.  
  3.      A          R RPFILE1              
  4.      A            U01095         9S 0  
  5.      A            FIELDA         5     
  6.      A            FIELDB         8     
  7.  
  8. ----------------------------------------------------------
  9.  
  10. PFILE2:
  11.  
  12.      A          R RPFILE2             
  13.      A            ARFBEX         3S 0 
  14.      A            ARFBLN         5S 0 
  15.      A            ARFBRS         1S 0 
  16.      A            F2FLDA         6    
  17.  
  18. ----------------------------------------------------------
  19.  
  20. JFILE1:
  21.  
  22.       A          R JOINED                    JFILE(PFILE1 PFILE2)        
  23.       A          J                           JOIN(PFILE1 PFILE2)         
  24.       A                                      JFLD(U01095 ARFBEXLNRS)     
  25.       A            ARFBEXLNRS                CONCAT(ARFBEX ARFBLN ARFBRS)
  26.       A            FIELDA                                                
  27.       A            FIELDB                                                
  28.       A            F2FLDA                                                
  29.  
  30. ----------------------------------------------------------
  31.  
  32. Retrieved SQL source:
  33.  
  34. --  Generate SQL                                        
  35. --  Version:                   V5R3M0 040528            
  36. --  Generated on:              10/04/12 01:33:27        
  37. --  Relational Database:                                
  38. --  Standards Option:          DB2 UDB iSeries          
  39.                                                         
  40. DROP VIEW <lib>/JFILE1 ;                                
  41.                                                         
  42. CREATE VIEW <lib>/JFILE1 (                              
  43.   ARFBEXLNRS ,                                          
  44.   FIELDA ,                                              
  45.   FIELDB ,                                              
  46.   F2FLDA )                                              
  47.   AS                                                    
  48.   SELECT                                                
  49.   CAST (  Q02.ARFBEX AS NUMERIC(3, 0) ) ,               
  50.   Q01.FIELDA ,                                          
  51.   Q01.FIELDB ,                                          
  52.   Q02.F2FLDA                                            
  53.   FROM <lib>/PFILE1 AS Q01 INNER JOIN                   
  54.   <lib>/PFILE2 AS Q02 ON ( Q01.U01095 = CAST (  Q02.ARFBEX CONCAT 
  55.   Q02.ARFBLN CONCAT Q02.ARFBRS AS NUMERIC(9, 0) ) )               
  56.  
© 2004-2019 by midrange.com generated in 0.006s valid xhtml & css