on 07-25-2014 1:36 PM
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;
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.