Reputation: 59
How to add compare select statement in if()
condition in Oracle pl-sql
IF( (v_c1.int_rate = '0')
AND (local_type_advances in (SELECT DISTINCT(REF_CODE)
FROM XYZ
WHERE REF_REC_TYPE = '39' AND DEL_FLG = 'N'
AND REF_CODE BETWEEN '1' AND '50' )))
where select query generate following output
'31','32','33'
now as below if I hardcode it then it works fine but in query doesn't works
IF( (v_c1.int_rate='0') AND (local_type_advances in ('31','32','33')))
Upvotes: 0
Views: 6417
Reputation: 5636
Even if you could put that big, honkin' select
in there, I would recommend keeping the if
statement simple for maintainability. This works and is a lot clearer, especially if you add sensible comments:
create table XYZ(
REF_REC_TYPE int,
DEL_FLG char( 1 ),
REF_CODE int
);
insert into XYZ
select 39, 'Y', 28 from dual union all
select 39, 'Y', 29 from dual union all
select 39, 'Y', 30 from dual union all
select 39, 'N', 31 from dual union all
select 39, 'N', 32 from dual union all
select 39, 'N', 33 from dual union all
select 39, 'Y', 34 from dual union all
select 39, 'Y', 35 from dual union all
select 39, 'Y', 36 from dual;
DECLARE
int_rate INT := 0;
local_type_advances INT := 32;
found INT;
BEGIN
-- See if we have any whatever
SELECT count(*)
INTO found
FROM XYZ
WHERE REF_REC_TYPE = 39
AND DEL_FLG = 'N'
AND REF_CODE = local_type_advances;
IF int_rate = 0
AND found > 0 THEN
dbms_output.put_line( 'True: ' || found );
ELSE
dbms_output.put_line( 'False: ' || found );
END IF;
END;
Btw, this doesn't execute in SqlFiddle for some reason. As it happens, I found a perfectly good Oracle DB sitting in a drawer under some old napkins. So I can verify this is good code.
Upvotes: 0
Reputation: 59
What I have done is, I have stored result of that select statment in localvariable as varchar2. And added this local varchar in that if condition's in clause as result instead of that select statment
select '''' || listagg(REF_CODE, ''',''' ) within group (order by REF_CODE) || '''' as type_advances into local_type_advances_list from RCT@ADF2FIN where REF_REC_TYPE = '39' AND DEL_FLG = 'N' AND REF_CODE BETWEEN '1' AND '50';
IF( (v_c1.int_rate='0') AND (local_type_advances in (local_type_advances_list)) )
Upvotes: 2
Reputation: 10360
Since you can't have a select statement in an IN clause, break it into two pieces. Select the range you need to search into a variable, then search that variable to see if the value is in there. Here is a working example using LISTAGG() to return a comma-separated list into a variable and REGEXP_LIKE to test the list in the variable to see if the value is in the list. X_TBL has one column, X_COL that contains three rows, '31', '32' and '33' respectively.
declare
local_type_advance_string varchar2(50); -- Holds the list returned from
-- the query.
local_type_advance_nbr varchar2(2) := '32'; -- Value to test the list for.
begin
-- Use listagg() to select a comma-separated list into a string variable.
-- X_TBL is the table, X_COL is the column.
SELECT LISTAGG(X_COL, ',') WITHIN GROUP( ORDER BY X_COL)
into local_type_advance_string
FROM X_TBL;
--dbms_output.put_line(local_type_advance_string);
IF regexp_like(local_type_advance_string, '(^|\W)'||
local_type_advance_nbr || '(\W|$)') then
dbms_output.put_line(local_type_advance_nbr || ' Is in there!');
else
dbms_output.put_line(local_type_advance_nbr || ' Is not found');
end if;
end;
The regular expression allows for the position of the search number no matter if it's at the beginning, middle or end of the list.
Run via Toad:
32 Is in there!
Upvotes: 0