Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
How to use a function |
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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |