Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2007
    Posts
    7

    Unanswered: SQL0104N.SQLSTATE=42601 Error

    Hi,
    I am trying to run the following query from Command center ( I have DB2 Ver 7 )

    DECLARE GLOBAL TEMPORARY TABLE IBRTEST AS
    (SELECT 'MissingSrNo' AS rec_type, 'Internal Tickets' AS tickets,
    TDL.TK_SERIAL TDL_TK_SERIAL, TSD.TK_SERIAL TSD_TK_SERIAL,
    TDL.USH_REVENUE TDL_USH_REVENUE, TSD.USH_REVENUE TSD_USH_REVENUE,
    TSD.QTY_TIX_REP, tsd.ush_revenue * tsd.qty_tix_rep tsd_ush_revenue_qtytixrep, TDL.FLAGS, TSD.VOID_CODE,
    TSD.VOID_DATE, TDL.TCK_SCAN_QTY, TDL.WST_CODE, TDL.DATE,
    TDL.TCK_ISSUE_DATE
    FROM TK.TURN_DAILY_DETAIL TDL LEFT OUTER JOIN TK.TCK_SALE_DETAILS TSD
    ON TDL.TK_SERIAL = TSD.TK_SERIAL
    WHERE TDL.WST_CODE <> 'USH:CAS042'
    AND TSD.TK_SERIAL IS NULL
    and TDL.DATE in ('2000-06-21')
    UNION ALL
    SELECT 'MissingSrNo' AS REC_TYPE, 'Imported Tickets' AS TICKETS,
    TDL.TK_SERIAL TDL_TK_SERIAL, TSD.TK_SERIAL TSD_TK_SERIAL,
    TDL.USH_REVENUE TDL_USH_REVENUE, TSD.USH_REVENUE TSD_USH_REVENUE,
    TSD.QTY_TIX_REP, tsd.ush_revenue * tsd.qty_tix_rep tsd_ush_revenue_qtytixrep, TDL.FLAGS, TSD.VOID_CODE,
    TSD.VOID_DATE, TDL.TCK_SCAN_QTY, TDL.WST_CODE, TDL.DATE,
    TDL.TCK_ISSUE_DATE
    FROM TK.TCK_SALE_DETAILS TSD RIGHT OUTER JOIN TK.TURN_DAILY_DETAIL TDL
    ON TSD.WORKCODE = TDL.WST_CODE
    AND TSD.DATE = TDL.TCK_ISSUE_DATE
    AND TSD.TCK_NBR = TDL.TCK_NBR
    WHERE TDL.WST_CODE = 'USH:CAS042'
    and TSD.TK_SERIAL is null
    and TDL.DATE in ('2000-06-21' )
    ) DEFINITION ONLY;

    When I run this query I get the following error :

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returnedQL0104N An unexpected token "ONLY" was found following "-21' ) ) DEFINITION". Expected tokens may include: "END-OF-STATEMENT".
    SQLSTATE=42601

    The OS that I have is MS Windows XP, and the DB2 server is on the network.

    Pls. Help
    Thanks In advance,

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    SQL0104 is a syntax error. Does the SQL Reference for DB2 Version 7 state that DEFINITION ONLY is a valid clause for a DECLARE GLOBAL TEMPORARY TABLE statement?

    I can't be bothered to look it up myself (V7 is out of service for nearly 2 years now).

    Also, what should this do? It looks like the definition of a materialized query table (MQT aka materialized view), especially with the nested UNION ALL. But then you say DEFINITION ONLY, i.e. DB2 should not copy any data. What would be the UNION ALL for?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •