locked
OLE DB was unable to convert a value to the data type requested for column 0. RRS feed

  • Question

  • I created the named  calculation in DSV for year, quarter, month based on budget date and got the following error:

     

    • The following system error occurred:  Out of present range. .
    • Errors in the back-end database access module. OLE DB was unable to convert a value to the data type requested for column 0.

     

    In test, I do not have any issue because of this named calculation but in prod, I received the following error.

    I do not see any null or 0 value in budget date field and do not fully understand the error.

    Any kinds of advice to resolve this issue would be greatly appreciated.

    Thanks.

     

    The following is code for year, quarter, and month:

     

    YEAR

     

    Code Snippet

     

    (TO_NUMBER(TO_CHAR(DECODE(SIGN(  TO_NUMBER(TO_CHAR("table name", 'MM')) - 6), -1, "table name"."BDGT_DT",DECODE(TO_NUMBER(TO_CHAR("table name"."BDGT_DT", 'MM')), 6,DECODE(SIGN(  TO_NUMBER(TO_CHAR("DSO_BUDGET"."table name"."BDGT_DT", 'DD')) - 1), -1, "table name"."BDGT_DT",ADD_MONTHS("table name"."BDGT_DT", 12)), ADD_MONTHS("table name"."BDGT_DT", 12))), 'YYYY')))

     

     

     

    QUARTER

     

    Code Snippet

     

    CEIL((MOD(12 + TO_NUMBER(TO_CHAR("DSO_BUDGET"."PIANOMAN_TCTCL_BDGT"."BDGT_DT", 'MM')) - DECODE( SIGN(TO_NUMBER(TO_CHAR("DSO_BUDGET"."PIANOMAN_TCTCL_BDGT"."BDGT_DT", 'DD')) - 1), -1, 6 + 1, 6) , 12) + 1) / 3.0)

     

     

     

    MONTH

     

    Code Snippet

     

    TO_CHAR(bdgt_dt, 'MON') || ' ' || TO_CHAR(bdgt_dt, 'YY')

     

     

    Wednesday, September 3, 2008 10:01 PM

Answers

  • I just created the named query , and converted the data type as INT using CAST function for year, quarter then it works fine now.

     

    Does anybody know why datatype is set to byte even if it is showing NUMBER in oracle database?

    Any kinds of advice would be appreciated.

    Thanks.

    Thursday, September 4, 2008 5:32 PM

All replies

  • I found out some differences between test and prod when checking datatype.

    In test it is showing as int64 for year and quarter and string for month.

    But in prod, it is showing as byte for year and quarter.

     

    When I created the named calculation in DSV, I can't modify datatype.

    Does anyone have any idea?

    Please let me know.

    Thanks.

    Thursday, September 4, 2008 4:03 PM
  • I just created the named query , and converted the data type as INT using CAST function for year, quarter then it works fine now.

     

    Does anybody know why datatype is set to byte even if it is showing NUMBER in oracle database?

    Any kinds of advice would be appreciated.

    Thanks.

    Thursday, September 4, 2008 5:32 PM