Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    24

    Unanswered: Can we Temp tables in Stored procs thsi way?

    HI i wanted to use this in Stored procedure
    but the following doesnt work , cant we use temp tables in SP's?

    With test as ( SELECT DISTINCT ODTEST.ITEM_OBJID, S_ITEMI, S_IDCNG FROM EREIMP.ODTEST AS ODTEST JOIN EREIMP.PLTEST AS PLTEST ON ODTEST.ITEM_OBJID = PLTEST.ITEM_OBJID WHERE ODTEST.Q_PMSYNC='Y' AND ODTEST.S_DC='RD' AND ODTEST.Q_DATEPMSYNC < ODTEST.Q_LASTMOD) SELECT count(*) INTO num_rows FROM test;


    Thanks
    Prashant

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by cprash.aggarwal
    With test as ( .... ) SELECT count(*) INTO num_rows FROM test;
    The "WITH" subclause (common table expression) is not available for the "SELECT INTO" clause, only for (multi-row) "SELECT".
    On the other hand, it's also available as part of the SELECT in a CREATE VIEW or INSERT clause.
    These restrictions will no doubt go away with future versions of DB2.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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