case results not showing in editor

[Danny Lewkin]

Hi again,

another problem has occured with this stored procedure...
When added more than 1 record int the table tbl_comppool_modules, the  
stored procedure returns faulty records!
The first record added to tbl_comppool_modules is not shown anymore,  
which is correct. But the other records I insert into  
tbl_comppool_modules, the stored procedure still returns them!! Very  
strange, because when I run the query

> SELECT RecID, name, description FROM tbl_competence_pool
> 		WHERE RecID NOT IN (SELECT objptr_comppool FROM  
> tbl_comppool_modules WHERE objptr_module = 1) AND  
> objptr_organisation = intOrganisationID;

Outside the stored procedure, it runs perfectly...

Greetz,

Danny

Btw, should I put all of this into mantis?? Actually, we need a  
solution for this very badly...


Op 3-okt-07, om 10:33 heeft Danny Lewkin het volgende geschreven:

> Hi List, Ivan,
>
> when I call this procedure :
>
> create or replace procedure ct_select_competences_module(in  
> intModuleID long, IN intOrganisationID LONG, IN strSelectList VARCHAR)
> Begin
> 	CASE strSelectList
> 	WHEN 'Module' THEN
> 		SElect RecID, objptr_module, objptr_comppool->name As  
> competence_name,
> 		objptr_comppool->description As comp_description FROM  
> tbl_comppool_modules
> 		WHERE objptr_module = intModuleID;
> 	WHEN 'All' THEN
> 		SELECT RecID, name, description FROM tbl_competence_pool
> 		WHERE RecID NOT IN (SELECT objptr_comppool FROM  
> tbl_comppool_modules WHERE objptr_module = 1) AND  
> objptr_organisation = intOrganisationID;
> 	ELSE
> 		SELECT 1;
> 	END CASE
> END
>
> After calling this procedure like this :
> CALL ct_select_competences_module(1, 1, 'All'),
> I get 1 as Query Result, so the ELSE statement is executed.
>
>
>
> BUT if I rewrite this procedure :
>
> create or replace procedure ct_select_competences_module(in  
> intModuleID long, IN intOrganisationID LONG, IN strSelectList LONG)
> Begin
> 	CASE strSelectList
> 	WHEN 1 THEN
> 		SElect RecID, objptr_module, objptr_comppool->name As  
> competence_name,
> 		objptr_comppool->description As comp_description FROM  
> tbl_comppool_modules
> 		WHERE objptr_module = intModuleID;
> 	WHEN 2 THEN
> 		SELECT RecID, name, description FROM tbl_competence_pool
> 		WHERE RecID NOT IN (SELECT objptr_comppool FROM  
> tbl_comppool_modules WHERE objptr_module = 1) AND  
> objptr_organisation = intOrganisationID;
> 	ELSE
> 		SELECT 1;
> 	END CASE
> END
>
> After calling this procedure like this :
> CALL ct_select_competences_module(1, 1, 2),
>
> I DO get a list of records, and the ELSE statement is NOT executed...
>
> So, I guess, the case-statement can NOT compare VARCHARS ?????
> Same result with the IF-statement...
>
> Guess this is a bug..??
>
>
> Greetz,
>
> Danny
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina