Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Prcedure |
Sun, Feb 21 2010 3:40 AM | Permanent Link |
Dieter Nagy | Hi all,
I tried this SP EXECUTE IMMEDIATE 'CREATE PROCEDURE "PROC_ERGEBNIS" (IN "E1" SMALLINT, IN "E2" SMALLINT, IN "E3" SMALLINT, IN "E4" SMALLINT, IN "E5" SMALLINT, IN "E6" SMALLINT, IN "E7" SMALLINT) BEGIN DECLARE STATCURSOR CURSOR WITH RETURN FOR STMT; Unprepare stmt; Prepare Stmt FROM ''SELECT * FROM ZAHLEN WHERE ( IF(Z1 IN (''+CAST(E1 AS VARCHAR)+'',''+CAST(E2 AS VARCHAR)+'',''+CAST(E3 AS VARCHAR)+'',''+CAST(E4 AS VARCHAR)+'',''+CAST(E5 AS VARCHAR) +'',''+CAST(E6 AS VARCHAR)+''), 1, 0) + IF(Z2 IN (''+CAST(E1 AS VARCHAR)+'',''+CAST(E2 AS VARCHAR)+'',''+CAST(E3 AS VARCHAR)+'',''+CAST(E4 AS VARCHAR)+'',''+CAST(E5 AS VARCHAR) +'',''+CAST(E6 AS VARCHAR)+''), 1, 0) + IF(Z3 IN (''+CAST(E1 AS VARCHAR)+'',''+CAST(E2 AS VARCHAR)+'',''+CAST(E3 AS VARCHAR)+'',''+CAST(E4 AS VARCHAR)+'',''+CAST(E5 AS VARCHAR) +'',''+CAST(E6 AS VARCHAR)+''), 1, 0) + IF(Z4 IN (''+CAST(E1 AS VARCHAR)+'',''+CAST(E2 AS VARCHAR)+'',''+CAST(E3 AS VARCHAR)+'',''+CAST(E4 AS VARCHAR)+'',''+CAST(E5 AS VARCHAR) +'',''+CAST(E6 AS VARCHAR)+''), 1, 0) + IF(Z5 IN (''+CAST(E1 AS VARCHAR)+'',''+CAST(E2 AS VARCHAR)+'',''+CAST(E3 AS VARCHAR)+'',''+CAST(E4 AS VARCHAR)+'',''+CAST(E5 AS VARCHAR) +'',''+CAST(E6 AS VARCHAR)+''), 1, 0) + IF(Z6 IN (''+CAST(E1 AS VARCHAR)+'',''+CAST(E2 AS VARCHAR)+'',''+CAST(E3 AS VARCHAR)+'',''+CAST(E4 AS VARCHAR)+'',''+CAST(E5 AS VARCHAR) +'',''+CAST(E6 AS VARCHAR)+''), 1, 0)) = ''+CAST(E7 AS VARCHAR); open statcursor using E1,E2,E3,E4,E5,E6,E7; END This works not as expected. While E7 <4 it seems to be ok, but if E7 > 4 then it works not correct. What do I wrong? Please help. Thanks Dieter Nagy |
Sun, Feb 21 2010 5:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Dieter
What does it do that isn't correct? Roy Lambert |
Sun, Feb 21 2010 7:05 AM | Permanent Link |
Dieter Nagy | Roy,
I see the rows in the Table zahlen but the result of the SP is NULL. For example: Z1=1,Z2=10,Z3=11,Z4=36,Z5=40,Z6=41 --->Table zahlen In the SP E1=1,E2=10........E6=41, E7 = 6 then the result = NULL(No Record found).... Thanks Dieter Roy Lambert wrote: Dieter What does it do that isn't correct? Roy Lambert |
Sun, Feb 21 2010 8:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Dieter
Looking at it I can't spot anything wrong, but with a horrible piece of SQL like that its not easy to spot a missing comma Just in case its not handling the parameters correctly can you try this EXECUTE IMMEDIATE 'CREATE PROCEDURE "PROC_ERGEBNIS" (IN "E1" SMALLINT, IN "E2" SMALLINT, IN "E3" SMALLINT, IN "E4" SMALLINT, IN "E5", IN E7 SMALLINT, IN "E6" SMALLINT, IN "E7" SMALLINT) BEGIN DECLARE STATCURSOR CURSOR WITH RETURN FOR STMT; DECLARE Prog VARCHAR; DECLARE CA VARCHAR; Unprepare stmt; SET CA = CAST(E1 AS VARCHAR)+'',''+CAST(E2 AS VARCHAR)+'',''+CAST(E3 AS VARCHAR)+'',''+CAST(E4 AS VARCHAR)+'',''+CAST(E5 AS VARCHAR)+'',''+CAST(E6 AS VARCHAR); SET Prog = 'SELECT * FROM ZAHLEN WHERE (IF(Z1 IN ('+CA+',1,0)+IF(Z2 IN ('+CA+',1,0)+IF(Z3 IN ('+CA+',1,0)+IF(Z4 IN ('+CA+',1,0)+IF(Z5 IN ('+CA+',1,0)+IF(Z6 IN ('+CA+',1,0))='+CAST(E7 AS VARCHAR); Prepare Stmt FROM Prog; open statcursor; END Naturally, lacking your data,I haven't Roy Lambert [Team Elevate] |
Sun, Feb 21 2010 9:21 AM | Permanent Link |
Dieter Nagy | Roy
Thanks for your help. It's realy horrible I have to change... Dieter Nagy Roy Lambert wrote: Dieter Looking at it I can't spot anything wrong, but with a horrible piece of SQL like that its not easy to spot a missing comma Just in case its not handling the parameters correctly can you try this EXECUTE IMMEDIATE 'CREATE PROCEDURE "PROC_ERGEBNIS" (IN "E1" SMALLINT, IN "E2" SMALLINT, IN "E3" SMALLINT, IN "E4" SMALLINT, IN "E5", IN E7 SMALLINT, IN "E6" SMALLINT, IN "E7" SMALLINT) BEGIN DECLARE STATCURSOR CURSOR WITH RETURN FOR STMT; DECLARE Prog VARCHAR; DECLARE CA VARCHAR; Unprepare stmt; SET CA = CAST(E1 AS VARCHAR)+'',''+CAST(E2 AS VARCHAR)+'',''+CAST(E3 AS VARCHAR)+'',''+CAST(E4 AS VARCHAR)+'',''+CAST(E5 AS VARCHAR) +'',''+CAST(E6 AS VARCHAR); SET Prog = 'SELECT * FROM ZAHLEN WHERE (IF(Z1 IN ('+CA+',1,0)+IF(Z2 IN ('+CA+',1,0)+IF(Z3 IN ('+CA+',1,0)+IF(Z4 IN ('+CA+',1,0)+IF(Z5 IN ('+CA+',1,0)+IF(Z6 IN ('+CA+',1,0))='+CAST(E7 AS VARCHAR); Prepare Stmt FROM Prog; open statcursor; END Naturally, lacking your data,I haven't Roy Lambert [Team Elevate] |
Sun, Feb 21 2010 10:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Dieter
But did it work? Roy Lambert |
Sun, Feb 21 2010 1:41 PM | Permanent Link |
Dieter Nagy | Roy,
sorry, no. First I get the error ....missing THEN or, found +. IF(Z1 IN ('+CA+') solved this problem. It's crazy, when E7 = 6 then it works, when E7 = 5 then no..... Dieter Nagy Roy Lambert wrote: Dieter But did it work? Roy Lambert |
Mon, Feb 22 2010 2:32 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Dieter
I think you need to post your tables, catalog and query to Tim Roy Lambert [Team Elevate] |
Mon, Feb 22 2010 6:29 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dieter,
<< This works not as expected. While E7 <4 it seems to be ok, but if E7 > 4 then it works not correct. What do I wrong? >> I have no idea just by looking at the SQL. Please send me a sample project that reproduces this, and I'll see what the issue is. Please also indicate the expected result in your email to me. Thanks, -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Feb 22 2010 11:46 AM | Permanent Link |
Dieter Nagy | Tim, Roy
after testing I found the error! It was so simple. I changed .....= '+CAST(E7 AS VARCHAR) to >= '+CAST(E7 AS VARCHAR); Sorry about that! It was my mistake. Thanks Dieter Nagy "Tim Young [Elevate Software]" wrote: Dieter, << This works not as expected. While E7 <4 it seems to be ok, but if E7 > 4 then it works not correct. What do I wrong? >> I have no idea just by looking at the SQL. Please send me a sample project that reproduces this, and I'll see what the issue is. Please also indicate the expected result in your email to me. Thanks, -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |