![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
![]() |
Sun, Feb 5 2012 4:18 AM | Permanent Link |
Dieter Nagy | Hi all,
when I try the following update in the EDB-Manager it works as expected; UPDATE ANZAHL SET _2ER = 1 WHERE (IF(Z1 IN(27,29), 1, 0) + IF(Z2 IN(27,29), 1, 0) + IF(Z3 IN(27,29), 1, 0) + IF(Z4 IN(27,29), 1, 0) + IF(Z5 IN(27,29), 1, 0) + IF(Z6 IN(27,29), 1, 0))= 2 but when I try this in my procedure BEGIN DECLARE STATCURSOR CURSOR WITH RETURN FOR STMT; EXECUTE IMMEDIATE'UPDATE ANZAHL SET _2ER =0'; PREPARE STMT FROM'UPDATE ANZAHL SET _2ER = 1 WHERE (IF(Z1 IN(?,?), 1, 0) + IF(Z2 IN(?,?), 1, 0) + IF(Z3 IN(?,?), 1, 0) + IF(Z4 IN(?,?), 1, 0) + IF(Z5 IN(?,?), 1, 0) + IF(Z6 IN(?,?), 1, 0))= 2'; EXECUTE STMT USING A1,A2; update failed. Please help me, what do I wrong? TIA Dieter |
Sun, Feb 5 2012 5:08 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Dieter
The only thing I can think of is the parameterized IN statements - the parser may have trouble figuring out what they are. Try building the statement yourself. PREPARE STMT FROM'UPDATE ANZAHL SET _2ER = 1 WHERE (IF(Z1 IN(' +CAST(param1 AS VARCHAR)+ ','+CAST(param2 AS VARCHAR)+'), 1, 0) + .... .... = 2'; and see if that works Roy Lambert [Team Elevate] |
Sun, Feb 5 2012 6:07 AM | Permanent Link |
Fernando Dias ![]() | Dieter,
The update is not failing, what is happening is you are executing the statement passing 2 parameter values only when the statement expects 12 values. Instead of: EXECUTE STMT USING A1,A2; Your should be calling it as: EXECUTE STMT USING A1,A2 , A1,A2 , A1,A2 , A1,A2 , A1,A2 , A1,A2 ; or build it yourself as Roy suggested instead of using parameters , if you find this calling statement weird ![]() -- Fernando Dias [Team Elevate] |
Sun, Feb 5 2012 7:02 AM | Permanent Link |
Dieter Nagy | Thanks Roy,
I tried it with Fernandos tip and it works. Roy Lambert wrote: Dieter The only thing I can think of is the parameterized IN statements - the parser may have trouble figuring out what they are. Try building the statement yourself. PREPARE STMT FROM'UPDATE ANZAHL SET _2ER = 1 WHERE (IF(Z1 IN(' +CAST(param1 AS VARCHAR)+ ','+CAST(param2 AS VARCHAR)+'), 1, 0) + .... .... = 2'; and see if that works Roy Lambert [Team Elevate] |
Sun, Feb 5 2012 7:03 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Fernando
>The update is not failing, what is happening is you are executing the statement passing 2 parameter values only when the statement expects 12 values. Arrrgh - another one of the "minor" differences between TEDBQuery and the SQL/PSM language Roy Lambert |
Sun, Feb 5 2012 7:03 AM | Permanent Link |
Dieter Nagy | Fernando, thanks for your help.
It works perfect. Dieter Fernando Dias wrote: Dieter, The update is not failing, what is happening is you are executing the statement passing 2 parameter values only when the statement expects 12 values. Instead of: EXECUTE STMT USING A1,A2; Your should be calling it as: EXECUTE STMT USING A1,A2 , A1,A2 , A1,A2 , A1,A2 , A1,A2 , A1,A2 ; or build it yourself as Roy suggested instead of using parameters , if you find this calling statement weird ![]() -- Fernando Dias [Team Elevate] |
Tue, Feb 7 2012 10:49 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< Arrrgh - another one of the "minor" differences between TEDBQuery and the SQL/PSM language >> Actually, the two are the same in this respect, but the TParams makes it appear as if there are only two parameters, in terms of their unique names. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Feb 7 2012 11:47 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
>Actually, the two are the same in this respect, but the TParams makes it >appear as if there are only two parameters, in terms of their unique names. And in this world, as Microsoft spotted, appearance is much more important than reality ![]() Roy Lambert |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |