Support Forums - View Thread

View Thread

The following is the text of the current message along with any replies.

Messages 1 to 6 of 6 total

Sat, Sep 13 2008 11:32 AM	Permanent Link
Dieter Nagy	Hello, I use a FUNCTION "Korrektur()" in a PROC. .......... EXECUTE IMMEDIATE 'UPDATE CROSSTABLE SET '+Korrektur(ZAHL)+' = 1; Works perfect. When I try .......... FETCH FIRST FROM CROSS (Korrektur(Zahl)) INTO TOTAL ---> ERROR 700 ....(Expected) but instead found(). I know I have to use CALL, but how can I do this? Please help... TIA Dieter
Sat, Sep 13 2008 11:38 AM	Permanent Link
Fernando Dias Team Elevate	Dieter, Can you post the Korrektur() function definition ? -- Fernando Dias [Team Elevate]
Sat, Sep 13 2008 11:53 AM	Permanent Link
Dieter Nagy	Fernando, this is the code: EXECUTE IMMEDIATE 'CREATE FUNCTION "KORREKTUR" (IN "EINGABE" SMALLINT) RETURNS VARCHAR(3) COLLATE ANSI BEGIN DECLARE RESULT VARCHAR; SET RESULT = ''T''+CAST(EINGABE AS VARCHAR); RETURN RESULT; END Dieter Fernando Dias <fernandodias.removthis@easygate.com.pt> wrote: Dieter, Can you post the Korrektur() function definition ? -- Fernando Dias [Team Elevate]
Sat, Sep 13 2008 12:36 PM	Permanent Link
Fernando Dias Team Elevate	Dieter, You are using the Korrektur() function to return a column name as a string, and that's ok if you use it in a dynamic sql statement construction as you did in <<EXECUTE IMMEDIATE 'UPDATE ... >> . However, you can't use a string value (the function result) as a column name in FETCH. Also, you can't use FETCH in a dynamic sql statement so maybe a possible solution would be to use a CASE structure with multiple FETCH statements, depending on the value returned by your function. -- Fernando Dias [Team Elevate]
Sat, Sep 13 2008 12:39 PM	Permanent Link
Dieter Nagy	Fernando, Thanks for your answer. Dieter Fernando Dias <fernandodias.removthis@easygate.com.pt> wrote: Dieter, You are using the Korrektur() function to return a column name as a string, and that's ok if you use it in a dynamic sql statement construction as you did in <<EXECUTE IMMEDIATE 'UPDATE ... >> . However, you can't use a string value (the function result) as a column name in FETCH. Also, you can't use FETCH in a dynamic sql statement so maybe a possible solution would be to use a CASE structure with multiple FETCH statements, depending on the value returned by your function. -- Fernando Dias [Team Elevate]
Mon, Sep 15 2008 10:12 AM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	Fernando, << However, you can't use a string value (the function result) as a column name in FETCH. >> Just as a side note - I intend to add the ability to do this shortly via an SQL/PSM function. -- Tim Young Elevate Software www.elevatesoft.com