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

Dieter


What does it do that isn't correct?

Roy Lambert

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

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]

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]

Dieter


But did it work?

Roy Lambert

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

Dieter


I think you need to post your tables, catalog and query to Tim

Roy Lambert [Team Elevate]

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

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