Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Unanswered: DB2 Function operands not compatible error

    Hi all

    Im new to db2 functions...
    i want to create a function to input a date and check the input with database and if the value is null then return the input with con-cat of chars else db value + 1

    here is what i wrote

    CREATE FUNCTION "DB2ADMIN"."GET_NEXT_SCH_ID" ( "PDATE" INTEGER )
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL

    BEGIN ATOMIC

    DECLARE X INTEGER;

    SET X = (SELECT MAX(SCHEDULE_ID)FROM PROGRAMME_SCHEDULE_HDR WHERE SCHEDULE_DATE = PDATE);

    IF (X = NULL) THEN
    RETURN SELECT RTRIM(REPLACE(CHAR(PDATE),'-',''))||'0001'FROM SYSIBM.SYSDUMMY1;

    ELSE

    RETURN (X + 1);

    END IF;

    END;

    im getting a error of
    DB2 Database Error: ERROR [42818] [IBM][DB2/NT] SQL0401N The data types of the operands for the operation "=" are not compatible. LINE NUMBER=10. SQLSTATE=42818

    Plz provide your expert reviews

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use the IS operator to compare with a NULL value.

  3. #3
    Join Date
    Sep 2011
    Posts
    85

    Same error

    Thanx for the quick reply

    i change the code as follows


    CREATE FUNCTION "DB2ADMIN"."GET_NEXT_SCH_ID" ( "PDATE" INTEGER )
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL

    BEGIN ATOMIC

    DECLARE X INTEGER;

    SET X = (SELECT MAX(SCHEDULE_ID)FROM PROGRAMME_SCHEDULE_HDR WHERE SCHEDULE_DATE = PDATE);

    IF (X IS NULL) THEN
    RETURN SELECT RTRIM(REPLACE(CHAR(PDATE),'-',''))||'0001'FROM SYSIBM.SYSDUMMY1;

    ELSE

    RETURN (X + 1);

    END IF;

    END;

    but still same result

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What is the schedule_date datatype in the database? Is it integer?

    Another point, you define 'return int' but returning a character.

    ____

  5. #5
    Join Date
    Sep 2011
    Posts
    85

    When try to cast to INTEGER overflow occured

    I try to cast it to INTEGER using follows

    SELECT CAST((RTRIM(REPLACE(CHAR(PDATE),'-',''))||'0001')AS INTEGER)FROM SYSIBM.SYSDUMMY1

    but this will return

    DB2 Database Error: ERROR [22003] [IBM][DB2/NT] SQL0413N Overflow occurred during numeric data type conversion. SQLSTATE=22003

    Other question was about SCHEDULE_DATE. Its TIMESTAMP

    If you asking the SCHEDULE_ID its a integer column

    any solutions...???

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Schedule_date is compared against int. Thats what your original prob was about. I didnt mean to ask a question, put tried to prompt you to look for soln :-)

  7. #7
    Join Date
    Sep 2011
    Posts
    85
    THANK FOR THE HELP sathyaram_s

    So iz there is a solution for this one..
    I mean if you can give a solution through a example that would be easy..

    Thanx in advance

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There are some problems in your original function definition.

    1) Do not use double quotations.
    Not all features that are legal are encouraged....
    comp.databases.ibm-db2 | Google Groups
    http://www.dbforums.com/db2/1667188-...ves-error.html

    2) Although PDATE was decared INTEGER,
    you used expression REPLACE(CHAR(PDATE),'-','').
    So, I guessed that you want to declare PDATE as DATE datatype.

    2-1) It is understandable to get overflow for CAST((RTRIM(REPLACE(CHAR(PDATE),'-',''))||'0001')AS INTEGER).
    Because, if PDATE was INTEGER it may return 14(= 10 + 4) characters,
    if PDATE was DATE it may return 12(= 8 + 4) characters.
    But, max number of digits in INTEGER is 10.

    3) You accessed a table.
    So you should specify READS SQL DATA instead of CONTAINS SQL.

    4) Please don not declare and use unnecessary variables.
    This is a good programming practice in almost every languages, not specific to SQL.

    Note: If an expression was used twice or more(and the expression was not too simple), declare a variable for the expression.


    Example of revised function:
    (Not tested. Datatypes were guessed.)
    Code:
    CREATE FUNCTION DB2ADMIN.get_next_sch_id( pdate DATE )
    RETURNS BIGINT 
    LANGUAGE SQL
    DETERMINISTIC NO EXTERNAL ACTION
    READS SQL DATA
    
    RETURN
    COALESCE(
       1 + (SELECT MAX(schedule_id)
              FROM programme_schedule_hdr
             WHERE schedule_date = pdate )
     , BIGINT( RTRIM( REPLACE( CHAR(pdate) , '-' , '' ) ) || '0001' )
    )
    ;
    Last edited by tonkuma; 09-02-11 at 18:05. Reason: Add Note for 4).

  9. #9
    Join Date
    Sep 2011
    Posts
    85

    To tonkuma

    Thanx for the wonderful advice and example

    It's now working and i got a good chance to make my mistakes correct..

    Thank you for all who helped me...

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Alternate expressions for
    Code:
     , BIGINT( RTRIM( REPLACE( CHAR(pdate) , '-' , '' ) ) || '0001' )
    may be
    Code:
     , BIGINT(HEX(pdate)) * 10000 + 1
    /* or */
     , BIGINT(HEX(pdate) || '0001')
    Another alternative way may be replacing whole return statement by
    Code:
    RETURN
    SELECT COALESCE( MAX(schedule_id) , BIGINT(HEX(pdate)) * 10000 ) + 1
      FROM programme_schedule_hdr
     WHERE schedule_date = pdate
    Last edited by tonkuma; 09-03-11 at 04:51. Reason: Remove outmost parentheses in the last sample of RETURN statement. Replace whole return statement in the last example.

Posting Permissions

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