Hi All, Thanks to every one. It is working fine as expected.
Deen On Thu, Sep 11, 2008 at 2:32 AM, Michael Moore <[EMAIL PROTECTED]>wrote: > > 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 Oracle-PLSQL@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
