Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    16

    Unanswered: Need help with Load from cursor

    I am trying to insert the following into table WFRS.UPP_PROD_STAGG

    The first column in the WFRS.UPP_PROD_STAGG is an identity colum with Generated with default

    i Keep running into the errors with date format. The query runs fine by itself. Any help appreciated.

    DECLARE MA CURSOR FOR SELECT UPP_PROV_ID, UPP_PROD_ID, UPP_MO_ID, DISC_PROC_DT, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT FROM (SELECT UPP_PROV_ID, UPP_MO_ID, DISC_PROC_DT, MA.PROV_NBR, REMBMT_PROD_TYP_CD, PROC_MO_END_DT, HOSP_FY_END_DT AS HOSP_FISC_YR_END_DT, DISCAMT AS DISC_AMT FROM (SELECT UPP_MO_ID, DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, PROC_MO_END_DT, HOSP_FY_END_DT,DISCAMT FROM (SELECT DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, (DISC_PROC_DT+ 1 MONTH-1 DAY) PRD_MO_END_DT, DATE(SUBSTR(CHAR(PROC_DT+1 MONTH),1,7) || '-01')-1 DAY PROC_MO_END_DT, DATE(SUBSTR(CHAR(PROC_DT),1,4) || '-12-31') HOSP_FY_END_DT, SUM(DISC_AMT) DISCAMT FROM ICAD.REMBMT_DISC_CLM DM WHERE DISC_PROC_DT ='2009-02-01' GROUP BY DISC_PROC_DT, PROV_NBR, REMBMT_PROD_TYP_CD, (DISC_PROC_DT+ 1 MONTH-1 DAY), DATE(SUBSTR(CHAR(PROC_DT+1 MONTH),1,7) || '-01')-1 DAY, DATE(SUBSTR(CHAR(PROC_DT),1,4) || '-12-31') ORDER BY 1) MA LEFT OUTER JOIN UPP.UPP_MO M ON MA.PRD_MO_END_DT=M.MO_END_DT) MA LEFT OUTER JOIN UPP.UPP_PROV P ON MA.PROV_NBR=P.PROV_NBR) MA LEFT OUTER JOIN UPP.UPP_PROD P ON MA.REMBMT_PROD_TYP_CD=P.UPP_PROD_ID_DESC
    DB20000I The SQL command completed successfully.

    LOAD FROM MA OF CURSOR METHOD N(UPP_PROV_ID, UPP_PROD_ID, UPP_MO_ID, DISC_PROC_DT, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT) INSERT INTO WFRS.UPP_PROD_STAGG (UPP_PROV_ID, UPP_PROD_ID, UPP_MO_ID, DISC_PROC_DT, PROC_MO_END_DT, HOSP_FISC_YR_END_DT, DISC_AMT)
    SQL3501W The table space(s) in which the table resides will not be placed in
    backup pending state since forward recovery is disabled for the database.

    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007

    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007

  2. #2
    Join Date
    Mar 2009
    Posts
    16

    Need help with Load from cursor

    Platform : Windows DB2 v9.5

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about CHAR(PROC_DT, ISO) and CHAR(PROC_DT+1 MONTH, ISO)?
    Last edited by tonkuma; 03-27-09 at 21:45.

  4. #4
    Join Date
    Mar 2009
    Posts
    16

    Resolved

    Thanks it worked with a combination of DATE(column, ISO) ..!!!

  5. #5
    Join Date
    Mar 2009
    Posts
    16

    Smile Thanks it worked!!!

    Thanks it worked!!!

Posting Permissions

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