Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: Semicolon in UDF

    Hallo everyone,

    I have built this UDF

    Code:
    CREATE OR REPLACE FUNCTION "MYSCHEMA"."FUNC" (account varchar(25), account_other varchar(25)int, amount int) RETURNS VARCHAR(10)
    LANGUAGE SQL 
    CONTAINS SQL 
    NO EXTERNAL ACTION 
    DETERMINISTIC
    
    BEGIN ATOMIC
    	return
    	
    	case when account != account_other then 'ignored'
    		else 
    			CASE when amount like '25>%' then 'yes'
    			END
    	END;
    END@
    This Function can be processed from DB2 without problem. Well, a code fragment looks like this:

    Code:
                            END
    	END;
    END@
    I dont understand why it is not like this:
    Code:
                            END;
    	END;
    END@
    Because, the first 2 ENDs are for CASEs.

    Thank you for your help..

    Regards,

    Ratna

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    CASE is not necessarily a Statement, but an expression. So its corresponding END does not need the semicolon. Only when the CASE becomes a Statement (like your first one) does the END need the semicolon.

    Andy

  3. #3
    Join Date
    May 2012
    Posts
    155
    Hallo Andy,

    thank you for the reply. After your hints I looked at the documentation and found out that a CASE-Statement is normally closed with END CASE. Thus, it must be:

    Code:
                            END
    	; END CASE
    END@
    So that it must look like this:

    Code:
    CREATE OR REPLACE FUNCTION "MYSCHEMA"."FUNC" (account varchar(25), account_other varchar(25)int, amount int) RETURNS VARCHAR(10)
    LANGUAGE SQL 
    CONTAINS SQL 
    NO EXTERNAL ACTION 
    DETERMINISTIC
    
    BEGIN ATOMIC
    	return
    	
    	case when account != account_other then 'ignored'
    		else 
    			CASE when amount like '25>%' then 'yes'
    			END
    	;END CASE
    END@
    I am reffering from the documentation example:

    Code:
    CASE
     WHEN v_workdept < 'B01'
      THEN UPDATE DEPT SET
       DEPTNAME = 'DATA ACCESS 1';
     WHEN v_workdept < 'C01'
      THEN UPDATE DEPT SET
       DEPTNAME = 'DATA ACCESS 2';
     ELSE UPDATE DEPT SET
       DEPTNAME = 'DATA ACCESS 3';
    END CASE
    But my code doesnt work anymore, any clue? Thank you..

    Regards,

    Ratna

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought a little differently.
    Both of your CASEs were expressions.
    The semicolon after the second END might be for RETURN statement.


    By the way,

    (1) There might be some unnecessary complexity.
    (1-1) If function-body was a RETUEN statement,
    BEGIN ... END might be not neccesary,
    (1-2) Nesting of CASE might be unnecessary,
    because each WHEN clauses would be evaluaed sequentially left to right.

    As a consequence, your CREATE FUNCTION might be equivalent to...
    Code:
    CREATE OR REPLACE FUNCTION MYSCHEMA.FUNC
    ( account       varchar(25)
    , account_other varchar(25)
    , amount        int
    )
      RETURNS VARCHAR(10)
      LANGUAGE SQL 
      CONTAINS SQL 
      NO EXTERNAL ACTION 
      DETERMINISTIC
    RETURN
    CASE
    WHEN account <> account_other THEN
         'ignored'
    WHEN amount  LIKE '25>%'      THEN
         'yes'
    END
    ;

    (2) amount LIKE '25>%'
    This predicate must be always false.
    Because amount was INT, then it never icludes '>'.
    Last edited by tonkuma; 01-23-13 at 11:51. Reason: Remove nor '%' in last statement.

  5. #5
    Join Date
    May 2012
    Posts
    155
    Hallo Tonkuma,

    thank you for the reply. Yes, you are right, there is an unnecessary complexity. Actually, it is not about the code complexity, it is more about the implementation of END CASE like in the documentation stated. In the documentation, we saw the example, that a CASE statement is closed with END CASE. But if I do that in my code, it ends up to failure.

    Regards,
    Ratna

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There are 2 CASE objects. One is a Statement and one is an Expression. You are using the expression so the END CASE is wrong. You can look in the documentation on the difference.

    Andy

Posting Permissions

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