Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2013
    Posts
    1

    Unanswered: UDF : Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause

    Hello all,

    When I execute the statements below I always get this error:
    Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present.. SQLCODE=-628, SQLSTATE=42613
    I succeeded creating the UDF with returns table (valeur decimal (8, 0)), but I try to create a scalar UDF for more ease of use. The result of the select statement is scalar since it's 1col/1row.
    Is there a way to do what I want (on DB2 10.1)

    Code:
    create function incrscalar(in pdomaine varchar(10), in pas integer default 1)
    	returns decimal(8, 0)
    	language sql
    	not deterministic
    	no external action
    	modifies sql data
    	return (select valeur from final table(update increments set valeur = valeur + pas where domaine = pdomaine))
    I tried that without any success :
    Code:
    create function incrscalar(in pdomaine varchar(10), in pas integer default 1)
    	returns decimal(8, 0)
    	language sql
    	not deterministic
    	no external action
    	modifies sql data
    	begin atomic
    		declare wvaleur integer default 0;
    		set wvaleur = (select valeur from final table (update increments set valeur = valeur + pas where domaine = pdomaine));
    		return wvaleur;
    	end!
    Nicolas

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    A scalar function defined as MODIFIES SQL DATA is subject to some constraints - check doc.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Nicolas,

    As stated in doc SQL-function-body has to be a compound SQL (compiled) statement.
    Try this:
    Code:
    create function incrscalar(in pdomaine varchar(10), in pas integer default 1)
    	returns decimal(8, 0)
    	language sql
    	not deterministic
    	no external action
    	modifies sql data
    begin
            declare wvaleur integer default 0;
    	select valeur into wvaleur
            from final table(update increments set valeur = valeur + pas where domaine = pdomaine);
            return wvaleur;
    end!
    Regards,
    Mark.

Posting Permissions

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