Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Lightbulb Unanswered: Retreive dynamically a field of a table within a UDF

    I'm quite new to DB2, so i'm not sure if this is possible to do this with a UDF:

    I want to write a function that takes two parameters : the first is the key value of record i want to retreive. The second one is the name of the field i want to retreive the value from.

    So i'd like to have a function like :

    CREATE FUNCTION GET_VALUE(key INTEGER, field VARCHAR(255) )
    RETURNS VARCHAR(255)

    F1: BEGIN ATOMIC

    DECLARE value VARCHAR(255);
    SET value = 'Default value';

    FOR V1 AS
    SELECT * FROM TEXTEASSIETTES WHERE NUMTXTASSIETTES=key
    DO
    value = {the value of the field named by the field parameter of my function};
    END FOR;

    RETURN truc;

    END F1

    Is there any way to construct dynamically the select statement or to retreive a field by its name ? Hope anyone has a clue !

    Christophe

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not believe that you can do it in a UDF, but you can do it is a stored procedure. Look at the PREPARE statement.

    Andy

  3. #3
    Join Date
    Apr 2003
    Posts
    3
    Ok, let's suppose I can do it with the prepare statement in a stored proc. I have another problem : how can I call my stored proc from the function ? It seems also impossible... ?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is correct, you canot call a stored procedure from a UDF.

    There is an alternative. You could do it the brute force way in your UDF.

    CREATE FUNCTION GET_VALUE(key INTEGER, field VARCHAR(255) )
    RETURNS VARCHAR(255)

    F1: BEGIN ATOMIC

    DECLARE value VARCHAR(255);
    DECLARE var1, var2, var3, ...

    select * into var1, var1, var3, ... from mytab where key = key_col;

    if field = 'Col1'
    THEN value = var1;
    end if;

    if field = 'Col2'
    THEN value = var2;
    end if;

    if field = 'Col3'
    THEN value = var3;
    end if;

    ...

    return value;

    END F1

    Originally posted by cvigouroux
    Ok, let's suppose I can do it with the prepare statement in a stored proc. I have another problem : how can I call my stored proc from the function ? It seems also impossible... ?

  5. #5
    Join Date
    Apr 2003
    Posts
    3
    Thanks a lot for your help !

Posting Permissions

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