Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2011
    Posts
    4

    Red face Unanswered: How to handle this db2 sql error?

    I AM EXPERIENCING AN ERROR WHILE PERFORMING A FUNCTION ON A COULMN DATA OF DATA TYPE VARCHAR. THE FUNCTION I AM PERFORMING IS:
    1: CONVERTING INTO DOUBLE
    2: OPERATING A SUM FUNCTION
    3: USING A COALESCE FUNCTION BECAUSE SOME FIELDS ARE NULL IN THAT COLUMN

    THE MESSAGE I AM RECEIVING IS:

    Message: Routine "SYSFUN.DOUBLE" (specific name "CHARTODOUBLE") has returned an error SQLSTATE with diagnostic text "SYSFUN:11".

    SOMEBODY PLEASE HELP ME OUT.
    MY WHOLE PROGRESS IS STUCK BECAUSE OF THIS.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    could you publish the function - the complete error message and indicate if this happened with specific data or at random
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Dec 2011
    Posts
    4
    Quote Originally Posted by przytula_guy View Post
    could you publish the function - the complete error message and indicate if this happened with specific data or at random
    this is the query:

    FOR B3 AS SELECT MSISDN_NO, SUM(DOUBLE(VOICE_DECREMENT)) AS VODEC, SUM(DOUBLE(GPRS_DECREMENT)) AS GPDEC,SUM(DOUBLE(SMS_DECREMENT)) AS SMDEC FROM TEMP_IN_DECREMENT GROUP BY MSISDN_NO
    DO
    IF VOICE_DECREMENT IS NULL
    THEN
    SET lv_VODEC = 0;
    ELSE
    SET lv_VODEC = VODEC;
    SET lv_GPDEC = GPDEC;
    SET lv_SMDEC = SMDEC;
    SET lv_TODEC = lv_VODEC + lv_GPDEC + lv_SMDEC;
    UPDATE USAGE_DATA SET (VOICE_DECREMENT, GPRS_DECREMENT, SMS_DECREMENT, TOTAL_DECREMENT) = (lv_VODEC, lv_GPDEC, lv_SMDEC, lv_TODEC) WHERE MSISDN = B3.MSISDN_NO;
    END FOR;

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    just publishing the query does not help anybody
    try to publish as much as possible details, so we can TRY to understand what is going on...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Dec 2011
    Posts
    4
    Quote Originally Posted by przytula_guy View Post
    just publishing the query does not help anybody
    try to publish as much as possible details, so we can TRY to understand what is going on...
    i am trying to update a table named USAGE_DATA with the fields named in the bracket in the query with datatype as DOUBLE.
    the TEMP_IN_DECREMENT table has the same column names but with a different datatype as VARCHAR.

    so i have to update the data_usage table with this data converting it into double and performing a function sum side by side...

    but i am not able to perform these functions.. this is all i can elaborate.. kindly manage

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by przytula_guy View Post
    could you publish the function - the complete error message and indicate if this happened with specific data or at random
    Still, you didn't publish the complete error message.
    Where is difficulty for you?

    If you want to save your time and work,
    I also save my time and work to answer your question.


    Anyway, there is an IF statement in your code...
    IF VOICE_DECREMENT IS NULL
    ...
    What is VOICE_DECREMENT?

    It is not declared nor in SELECT list.
    Last edited by tonkuma; 12-15-11 at 08:13. Reason: Add last sentence "It is not declared nor in SELECT list."

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Different question: why are you doing this processing in a loop in your application? You could push-down everything into a single UPDATE statement. That should perform much better, too.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes! Stolze.

    I thought a MERGE statement might be better.

    More to said,
    the table name TEMP_IN_DECREMENT suggested that it may be a temporary table.
    If so, the process to popurate into the temporary table might be able to put into an UPDATE or a MERGE statement, too.
    Last edited by tonkuma; 12-15-11 at 12:16.

Posting Permissions

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