create table card_hdr as
select 1 card_id, 'c1' card_name, 'Y' card_type from DUAL
union all
select 2 card_id, 'c2' card_name, 'Y' card_type from DUAL
union all
select 3 card_id, 'c3' card_name, 'N' card_type from DUAL
union all
select 4 card_id, 'c4' card_name, 'N' card_type from DUAL
union all
select 5 card_id, 'c5' card_name, 'Y' card_type from DUAL
union all
select 6 card_id, 'c6' card_name, 'N' card_type from DUAL;
create table card_det as
select 1 card_id, 100 loc_id, 'A' stat from DUAL
union all
select 1 card_id, 200 loc_id, 'A' stat from DUAL
union all
select 1 card_id, 300 loc_id, 'A' stat from DUAL
union all
select 2 card_id, 100 loc_id, 'A' stat from DUAL
union all
select 2 card_id, 200 loc_id, 'A' stat from DUAL
union all
select 2 card_id, 300 loc_id, 'A' stat from DUAL
union all
select 2 card_id, 400 loc_id, 'A' stat from DUAL
union all
select 2 card_id, 500 loc_id, 'A' stat from DUAL
union all
select 2 card_id, 600 loc_id, 'A' stat from DUAL
union all
select 5 card_id, 100 loc_id, 'A' stat from DUAL
union all
select 5 card_id, 200 loc_id, 'A' stat from DUAL;
var csv varchar2(65)
exec :csv := '100,200,300'
select *
from card_hdr
where card_id not in (select ch.card_id
from card_hdr ch
cross join
(select EXTRACTVALUE (value (d), '/g') as rslt
from table
(XMLSEQUENCE
(extract
(xmltype.createxml
(
'<row><g>'
|| replace
(:csv,
',',
'</g><g>')
||
'</g></row>'),
'/row/g'))) d) x
left outer join card_det cd
on ch.card_id = cd.card_id and cd.loc_id =
rslt
where ch.card_type != 'N' and loc_id is null);
--------This is one way.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
