Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Posts
    78

    Angry Unanswered: SELECT INTO problem, need help!

    Hello,

    I have a SELECT INTO statement in user defined function, the number of selected fields is equal to the number of variables I declared in INTO statement. But when I build the function, it always gives me the following error:
    SQL0104N An unexpected token ":" was found following " into". Expected tokens may include: "FROM".

    And when I take the statement to execute in command center it give me the "CLI0100E Wrong number of parameters. " error.

    And it is absurd, that I try to change SELECT INTO statement replaced by equivalent DECLARE CURSOR statement, it still shows should replace the DECLARE CURSOR FOR to something else.

    I'd appreciate your replys on this issue.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: SELECT INTO problem, need help!

    That is because the SELECT INTO is not allowed in UDFs.
    Try doing it this way:

    DECLARE myvar integer;

    SET (myvar) = (SELECT myvalue from mytable where .... )

    HTH

    Andy

    Originally posted by christine2003
    Hello,

    I have a SELECT INTO statement in user defined function, the number of selected fields is equal to the number of variables I declared in INTO statement. But when I build the function, it always gives me the following error:
    SQL0104N An unexpected token ":" was found following " into". Expected tokens may include: "FROM".

    And when I take the statement to execute in command center it give me the "CLI0100E Wrong number of parameters. " error.

    And it is absurd, that I try to change SELECT INTO statement replaced by equivalent DECLARE CURSOR statement, it still shows should replace the DECLARE CURSOR FOR to something else.

    I'd appreciate your replys on this issue.

  3. #3
    Join Date
    Jul 2003
    Posts
    78

    Smile Re: SELECT INTO problem, need help!

    Thanks a lot, you are right! But how about using DECLARE CURSOR and then FETCH CURSOR to the host variable? Is this statement also not allowed in UDFs?

    I appreciate your help.


    Originally posted by ARWinner
    That is because the SELECT INTO is not allowed in UDFs.
    Try doing it this way:

    DECLARE myvar integer;

    SET (myvar) = (SELECT myvalue from mytable where .... )

    HTH

    Andy

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: SELECT INTO problem, need help!

    Nope, not allowed either. There is not much allowed in a compound statement ina UDF. You need to look in the SQL Refernece to see the complete list.

    Andy

    Originally posted by christine2003
    Thanks a lot, you are right! But how about using DECLARE CURSOR and then FETCH CURSOR to the host variable? Is this statement also not allowed in UDFs?

    I appreciate your help.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: SELECT INTO problem, need help!

    Probably NO ...

    SELECT INTO and FETCH CURSOR to host variable, I assume, are two different implementations of the same functionality ...

    HTH

    Sathyaram

    Originally posted by christine2003
    Thanks a lot, you are right! But how about using DECLARE CURSOR and then FETCH CURSOR to the host variable? Is this statement also not allowed in UDFs?

    I appreciate your help.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jul 2003
    Posts
    78

    Re: SELECT INTO problem, need help!

    Correct, now I'm confusing. I tried both implementation, both failed. So I guess as Andy said that this is the limitation of UDF?

    Originally posted by sathyaram_s
    Probably NO ...

    SELECT INTO and FETCH CURSOR to host variable, I assume, are two different implementations of the same functionality ...

    HTH

    Sathyaram

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: SELECT INTO problem, need help!

    As I stated earlier cursors are not allowed either.

    This is taken directly from the SQL manual for compound statement allowed in a UDF:

    Syntax

    dynamic-compound-statement

    >>-+-------------+--BEGIN ATOMIC-------------------------------->
    | (1) |
    '-label:------'

    >--+-----------------------------------------+------------------>
    | .-------------------------------------. |
    | V | |
    '---+-| SQL-variable-declaration |-+--;-+-'
    '-| condition-declaration |----'

    .-,--------------------------.
    V |
    >----SQL-procedure-statement--;-+--END--+-------+--------------><
    '-label-'

    SQL-variable-declaration

    .-,-----------------.
    V |
    |--DECLARE----SQL-variable-name-+--data-type-------------------->

    .-DEFAULT NULL------------.
    >--+-------------------------+----------------------------------|
    '-DEFAULT--default-values-'

    condition-declaration

    |--DECLARE--condition-name--CONDITION--FOR---------------------->

    .-VALUE-.
    .-SQLSTATE--+-------+-.
    >--+---------------------+--string-constant---------------------|



    Notes:

    1.A label can only be specified when the statement is in a function, method, or trigger definition.

    Description

    label
    Defines the label for the code block. If the beginning label is specified, it can be used to qualify SQL variables declared in the dynamic
    compound statement and can also be specified on a LEAVE statement. If the ending label is specified, it must be the same as the beginning label.

    ATOMIC
    ATOMIC indicates that, if an error occurs in the compound statement, all SQL statements in the compound statement will be rolled back and
    any remaining SQL statements in the compound statement are not processed.

    SQL-procedure-statement
    The following list of SQL-control-statements can be used within the dynamic compound statement:
    FOR Statement
    GET DIAGNOSTICS Statement
    IF Statement
    ITERATE Statement
    LEAVE Statement
    SIGNAL Statement
    WHILE Statement

    The SQL statements that can be issued are:
    fullselect 64
    Searched UPDATE
    Searched DELETE
    INSERT
    SET variable statement


    Andy



    Originally posted by christine2003
    Correct, now I'm confusing. I tried both implementation, both failed. So I guess as Andy said that this is the limitation of UDF?

  8. #8
    Join Date
    Jul 2003
    Posts
    78

    Thumbs up Re: SELECT INTO problem, need help!

    Thank you very much, you are absolutely right. Can I ask you one more question? What's the benifit to create a UDF, since SP has more flexibility?

    Thanks again.


    Originally posted by ARWinner
    As I stated earlier cursors are not allowed either.

    This is taken directly from the SQL manual for compound statement allowed in a UDF:

    Syntax

    dynamic-compound-statement

    >>-+-------------+--BEGIN ATOMIC-------------------------------->
    | (1) |
    '-label:------'

    >--+-----------------------------------------+------------------>
    | .-------------------------------------. |
    | V | |
    '---+-| SQL-variable-declaration |-+--;-+-'
    '-| condition-declaration |----'

    .-,--------------------------.
    V |
    >----SQL-procedure-statement--;-+--END--+-------+--------------><
    '-label-'

    SQL-variable-declaration

    .-,-----------------.
    V |
    |--DECLARE----SQL-variable-name-+--data-type-------------------->

    .-DEFAULT NULL------------.
    >--+-------------------------+----------------------------------|
    '-DEFAULT--default-values-'

    condition-declaration

    |--DECLARE--condition-name--CONDITION--FOR---------------------->

    .-VALUE-.
    .-SQLSTATE--+-------+-.
    >--+---------------------+--string-constant---------------------|



    Notes:

    1.A label can only be specified when the statement is in a function, method, or trigger definition.

    Description

    label
    Defines the label for the code block. If the beginning label is specified, it can be used to qualify SQL variables declared in the dynamic
    compound statement and can also be specified on a LEAVE statement. If the ending label is specified, it must be the same as the beginning label.

    ATOMIC
    ATOMIC indicates that, if an error occurs in the compound statement, all SQL statements in the compound statement will be rolled back and
    any remaining SQL statements in the compound statement are not processed.

    SQL-procedure-statement
    The following list of SQL-control-statements can be used within the dynamic compound statement:
    FOR Statement
    GET DIAGNOSTICS Statement
    IF Statement
    ITERATE Statement
    LEAVE Statement
    SIGNAL Statement
    WHILE Statement

    The SQL statements that can be issued are:
    fullselect 64
    Searched UPDATE
    Searched DELETE
    INSERT
    SET variable statement


    Andy

  9. #9
    Join Date
    Jul 2003
    Posts
    78

    Wink Re: SELECT INTO problem, need help!

    Sorry, not finished yet. If I want to select more column from the table, I have to repeate the same select statement except for the selected column name. Any genious idea?

    Originally posted by christine2003
    Thank you very much, you are absolutely right. Can I ask you one more question? What's the benifit to create a UDF, since SP has more flexibility?

    Thanks again.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: SELECT INTO problem, need help!

    SET (x,y) = (select a,b from ...

    Andy

    Originally posted by christine2003
    Sorry, not finished yet. If I want to select more column from the table, I have to repeate the same select statement except for the selected column name. Any genious idea?

  11. #11
    Join Date
    Jul 2003
    Posts
    78

    Thumbs up Re: SELECT INTO problem, need help!

    I asked such stupid question. Thanks a lot. I have to bother you about my previous post, what's the benifit to create function, instead of stored procedure?


    Originally posted by ARWinner
    SET (x,y) = (select a,b from ...

    Andy

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: SELECT INTO problem, need help!

    You cannot do this with a SP:

    Select myUDF(x,y,z),a,b from mytable.

    This is my rule of thumb for writing UDFs and SPs.

    If I need to transform data or lookup based on some value then I use a UDF.

    If I need to perform some process that either returns a result set or performs a specific task, then I use a SP.

    Andy


    Originally posted by christine2003
    I asked such stupid question. Thanks a lot. I have to bother you about my previous post, what's the benifit to create function, instead of stored procedure?

  13. #13
    Join Date
    Jul 2003
    Posts
    78

    Talking Re: SELECT INTO problem, need help!

    Thank you Andy. You help me a lot.


    Originally posted by ARWinner
    You cannot do this with a SP:

    Select myUDF(x,y,z),a,b from mytable.

    This is my rule of thumb for writing UDFs and SPs.

    If I need to transform data or lookup based on some value then I use a UDF.

    If I need to perform some process that either returns a result set or performs a specific task, then I use a SP.

    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
  •