यहां छवि विवरण दर्ज करें[प्रश्न विवरण] मेरे पास बिलों की तालिका है, इसमें मैं उन अभिलेखों को नहीं दिखाना चाहता जिनमें Doctyp='BIL' है।

BILNO        DOCTYP

1812B00001      BIL 
1812B00001      RCR 
ADVN            CN 
ADVN            DA 
ADVN            RCD 
ADVN            RCR 
ADVN            TF 
AL1707B00006    BIL 
AL1707B00006    RCR

मुझे अपना आउटपुट चाहिए

BILNO   DOCTYP

ADVN    CN 
ADVN    DA 
ADVN    RCD 
ADVN    RCR 
ADVN    TF
-1
sharmishtha 30 पद 2019, 15:54

1 उत्तर

सबसे बढ़िया उत्तर

NOT IN एक विकल्प है:

SQL> with test (bilno, doctyp) as
  2    (select 182, 'bil' from dual union all
  3     select 182, 'xxy' from dual union all
  4     select 111, 'abc' from dual union all
  5     select 111, 'zdv' from dual union all
  6     select 223, 'bil' from dual union all
  7     select 555, 'xzy' from dual
  8    )
  9  select *
 10  from test t
 11  where bilno not in (select bilno
 12                      from test
 13                      where doctyp = 'bil');

     BILNO DOC
---------- ---
       111 zdv
       111 abc
       555 xzy

दूसरा है NOT EXISTS:

SQL> with test (bilno, doctyp) as
  2    (select 182, 'bil' from dual union all
  3     select 182, 'xxy' from dual union all
  4     select 111, 'abc' from dual union all
  5     select 111, 'zdv' from dual union all
  6     select 223, 'bil' from dual union all
  7     select 555, 'xzy' from dual
  8    )
  9  select *
 10  from test t
 11  where not exists (select null
 12                    from test t1
 13                    where t1.bilno = t.bilno
 14                      and t1.doctyp = 'bil'
 15                   );

     BILNO DOC
---------- ---
       111 zdv
       111 abc
       555 xzy
2
Littlefoot 30 पद 2019, 13:06