Behaviour of 'NOT IN' in Stored Procedure

[Former Member]

Hello,

I am encountering an abnormal behavior of 'NOT IN' in my .hdbprocedure. It was working fine a couple of days ago but today it doesn't yield any result.

If I execute the same query via SQL Console ( replacing sub query for col2 with hardcoded values), it works fine. If I replace NOT IN with <> , it works fine.

I am on SP7 rev 73.

CREATE PROCEDURE "TEST".TEST_PROC" (in RECORD_TYPE varchar(2))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
DEFAULT SCHEMA "TEST"     
AS  
BEGIN  
/* CHECKING FOR THE RECORD TYPE FROM THE INPUT PARAMETER */
  If :RECORD_TYPE = 'R' THEN   
         items[1] := 'R';  
items[2] := 'D' ; 
  ELSEIF :RECORD_TYPE = 'MR' THEN      
     items[1] := :RECORD_TYPE; 
END IF; 


/* Unnesting the array to a
table variable */
  v_table = UNNEST(:items) AS ("REC_TYPE");

Insert  into
new_table(col1,col2,col3,col4)

-- the following select doesnt
return any record in proc; whereas it returns records when run on SQL console

Select distinct  col1,col2,col3,col4
from TABLE_INPUT
where 

COALESCE(col1,0) NOT in (Select Parameter_Value from T_CONFIG where Parameter_name = 'check' 
                                                and pack ='GENERAL' and Active='1') 

 and col2 IN (SELECT REC_TYPE FROM :v_table);
END;     

[patrickbachmann]

Hey Tarun,

I'm curious but what if you try WHERE not COALESCE(col1,0) in (Select....etc

ie: Simply moving the NOT to before the coalesce.

-Patrick

[Former Member]

Thanks for your response Patrick.

Your suggestion should work too but I am curious as to why 'NOT IN' is not working now.

As I mentioned earlier that '<>' or NOT EXIST can work, but if I have to make this change then I

would have to do it in the entire codebase; just to be sure that I do not encounter this issue again.

Tarun