Error Building Dynamic SQL DB2 Statement

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
bmeansdfw
Member
Posts: 3
Joined: Fri Sep 12, 2014 10:50 pm

Error Building Dynamic SQL DB2 Statement

Post by bmeansdfw » Fri Oct 31, 2014 8:41 pm

I am trying to build some dynamic SQL for a batch job. The following SQL works perfectly:

Code: Select all

      , ITABLE4  AS                                             
          ( SELECT    '                                        '
                      CONCAT                                    
                      X'7D'                           CONCAT    
                      CHAR ( CURRENT_TIMESTAMP - 30 DAYS        
                           )                          CONCAT    
                      X'7D'                AS  WHENCLAUSE       
                    , '04'                 AS  SORTSEQ          
            FROM      SYSIBM.SYSDUMMY1                          
            FETCH FIRST 1 ROW ONLY                              
          )                                                     
However, I want to use the following instead, but I get an "invalid keyword" error and the job fails each time.

Code: Select all

                   , ITABLE4  AS                                             
          ( SELECT    '                                        '
                      CONCAT                                    
                      X'7D'                           CONCAT    
                      CHAR ( LAST_EXTRACT_TS                    
                           )                          CONCAT    
                      X'7D'                AS  WHENCLAUSE       
                    , '04'                 AS  SORTSEQ          

            FROM      DCSUD1.EXTRACT_CONTROL                    
            WHERE     TABLE_NAME    = 'DCSXXXX'                 
              AND     RECORD_STATUS = 'A'                       
            FETCH FIRST 1 ROW ONLY                              
          )                                        
The only difference is the source of the timestamp--one from sysdummy and one from an actual table.

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Sat Nov 08, 2014 2:11 pm

You need to post the full query and not just an extract and the error messages - not just your interpretation of them.
Regards
Nic

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others