Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011
    Posts
    11

    Unanswered: Date handling in Insert statement

    Hi All,

    In DataStage tool i have to load the data into DB2 database from Sequential fle.
    My requiremnt is like, from source, some columns are coming as char datatype, i have to isnert that data into Db2 table.
    In have to write insert query with some date validation,
    in my file for date filed it is coming as spaces, if space comes i have to load as null ('') . if date comes i have to load into table. form source it is coming as YYYYMMDD.

    My INSERT statemetn is "Insert into STG_MER(SE_NO, FEE_CD, FEE_RT, EFF_DT, END_DT,) VALUES(ORCHESTRATE.SE_NO, ORCHESTRATE.FEE_CD, ORCHESTRATE.FEE_RT, ORCHESTRATE.EFF_DT,(case when ORCHESTRATE.END_DT = ' ' then \'\' else to_date(ORCHESTRATE.END_DT,\'YYYY-MM-DD\') end ) )"

    I am getting below error.
    "Unable to determine association between statement parameters and table columns. The connector will not be able to obtain external schema and only limited schema reconciliation will be performed"

    Please help me, how to solve this problem.

    Thanks in Advance.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by madsongtel View Post
    Insert into STG_MER(SE_NO, FEE_CD, FEE_RT, EFF_DT, END_DT,)
    VALUES(ORCHESTRATE.SE_NO, ORCHESTRATE.FEE_CD, ORCHESTRATE.FEE_RT, ORCHESTRATE.EFF_DT,
    (case when ORCHESTRATE.END_DT = ' ' then \'\' else to_date(ORCHESTRATE.END_DT,\'YYYY-MM-DD\') end ) )
    I don't have DataStage, so I cannot try this out, but I see two apparent potential problems with your query:
    - The comma at the end of line one (just before the ")") should be removed.
    - The first case of the case statement, \'\', is not a valid date.
    Try using either "NULL" (without the quotes) or something like
    \'0001-01-01\'
    --_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
  •