Common Table Expressions

Temporary table definitions as work files

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE statement. A CTE is similar to a temporary work file--something that is not stored as a permanent object, used only for the duration of the query. This example is a list of nested CTE's building a temporary file that will be used to output a report. The syntax WITH (name) AS identifies the selections as a function of the CTE. 


         Exec SQL
           Drop Table QTemp/VEND99;

         Exec SQL
           Create Table QTemp/VEND99 (
             Vendor  Char( 12 ) CCSID 37 Not Null Default '',
             Title   Char( 40 ) CCSID 37 Not Null Default '',
             VATRef  Char( 20 ) CCSID 37 Not Null Default '',
             TxRef1  Char( 18 ) CCSID 37 Not Null Default '',
             AdrAtn  Char( 80 ) CCSID 37 Not Null Default '',
             Add1    Char( 35 ) CCSID 37 Not Null Default '',
             Add2    Char( 35 ) CCSID 37 Not Null Default '',
             Add3    Char( 35 ) CCSID 37 Not Null Default '',
             Add4    Char( 35 ) CCSID 37 Not Null Default '',
             Add5    Char( 35 ) CCSID 37 Not Null Default '',
             PostCode  Char( 12 ) CCSID 37 Not Null Default '',
             Amount  Numeric( 16, 2 )    Not Null Default 0
           );

         Exec SQL
           Insert Into QTemp/VEND99
             with Cancelled As (
               Select a.el1, a.DocCode, a.DocNum, a.statpayint, a.el2,
                          a.el3, b.Yr, a.valueHome, b.Period
                  From oas_docline a, oas_docHead b
                  Where a.cmpCode = b.cmpCode and a.docCode = b.docCode and
                        a.docNum = b.DocNum   and
                        a.docCode = 'CANC'    and
                        Substr( el3, 1, 1 ) = 'V'  and
                        b.Yr = :Year And b.Period Between 1 And 12
                              And b.docDate Between :FromDate And :ToDate
                              And subString(a.el1,1,3) = :Company 
            ),
            Payments As (
               Select a.el1, a.docCode, a.DocNum, a.statPayInt,
                      a.el2, a.el3, b.yr, a.valueHome, b.Period
                  From oas_docline a, oas_dochead b
                  Where a.cmpCode = b.cmpCode and a.docCode = b.docCode and
                        a.docNum = b.DocNum   and
                        a.docCode In ( :Dtype1, :DType2 )
                             And Substr( a.el3, 1, 1 ) = 'V'
                             And b.YR = :Year And b.Period Between 1 And 12
                             And b.docDate Between :FromDate And :ToDate
                             And subString(a.el1,1,3) = :Company  
            ),
            Unmatched_Payments As (
              Select a.*
                 From Payments a
                 Left Join Cancelled b
                       On a.el1 = b.el1
                            And a.el2 = b.el2
                            And a.el3 = b.el3
                            And a.statPayInt = b.statPayInt  
            ),
            Unmatched_Summary As (
               Select a.el1, a.el3, Cast( Sum( a.valuehome ) As Dec( 16, 2 ))
                           as Amount
                   From Unmatched_Payments a
                   Group By a.el1, a.el3
                   Order By a.el1, a.el3  
            ),
            Report_Data As (
              Select a.el1, a.el3, b.VAT,
                 Case When ( b.federaltax = ' ' )
                      Then b.socialSec
                      Else b.federaltax
                      End As txRef1,
                      a.Amount
                  From Unmatched_Summary a
                     Join oas_elmTaxes b
                           On a.el3 = b.elmcode
                  Where a.Amount >= 600
                              and b.elmLevel = 3
                              And b.Vat In ( ' ', 'I' )
                              And b.cmpcode = :dftcomp  
            ),

            Output_File As (
               Select substring(a.el3,1,12) as Vendor,
                          b.name, a.VAT, a.TxRef1,
                          b.name as AdrAtn,
                          b.Add1, b.Add2, b.Add3, b.Add4, b.Add5,
                          b.PostCode, a.Amount
                   From Report_Data a
                      Join oas_elmAddrList b
                            On a.el3 = b.elmcode
                   Where b.CmpCode = :dftComp
                         and b.elmlevel = 3
            )

            Select * From Output_File;

It is permissible to have multiple CTEs in one query. The example above may be termed a nested CTE since, one CTE is used to reference another CTE. A CTE can also reference itself, resulting in a recursive CTE.