ID 111 matches id 119
ID 112 matches id 119
ID 113 matches id 119
On Wed, May 15, 2013 at 10:57 PM, Gayathri <gd0...@gmail.com> wrote:
> Hi All,
>
> I have a table as below
>
> table name = table
> columns are as below :
> id(Unique num)
> subtyp_cd ('ORIG', 'RESUM')
> state
> quarter
> contract
> prod_num
> clm_num
>
>
> And here are the records -
>
> id= 111, Clm_num=12, subtyp_cd = ORIG, state=CA, Contract = C1,
> Quarter=01/01/2000, prod = p1
> id= 112, Clm_num=12, subtyp_cd = ORIG, state=CA, Contract = C1,
> Quarter=01/01/2000, prod = p2
> id= 113, Clm_num=12, subtyp_cd = ORIG, state=CA, Contract = C1,
> Quarter=01/01/2000, prod = p3
>
> id= 114, Clm_num=13, subtyp_cd = RESUM, state=CA, Contract = C1,
> Quarter=01/01/2000, prods = p2
> id= 115, Clm_num=13, subtyp_cd = RESUM, state=CA, Contract = C1,
> Quarter=01/01/2000, prod = p2
>
> id= 116, Clm_num=10, subtyp_cd = ORIG, state=IL, Contract = C1,
> Quarter=01/01/2000, prod = p11
> id= 117, Clm_num=10, subtyp_cd = ORIG, state=IL, Contract = C1,
> Quarter=01/01/2000, prod = p12
> id= 118, Clm_num=10, subtyp_cd = ORIG, state=IL, Contract = C1,
> Quarter=01/01/2000, prod = p13
>
> id= 119, Clm_num=11, subtyp_cd = RESUM, state=CA, Contract = C1,
> Quarter=01/01/2000, prod = p14
>
> I need to pull the prod_num that is in subtyp_cd = 'RESUM' and not in
> subtyp_cd = 'ORIG'
> from the example above, need to pull clm_num = 11 and prod_num p14
>
> its a self joined query -
>
> this is how it is joined:
>
> select b.id, b.prod_num
> from table a, table b
> where a.contract = b.contract
> and a.state = b.state
> and a.quarter = b.quarter
> and a.clm_num <> b.clm_num
> and a.subtyp_cd = 'ORIG'
> and b.subtyp_cd = 'RESUB'
> and b.prod_num not in (select prod_num from table where clm_num =
> a.clm_num)
>
> this query is not returning the expected results. I get multiple records,
> please let me know what am i missing?
>
> Thanks in advance!
> G
>
> --
> --
> 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
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
> ---
> You received this message because you are subscribed to the Google Groups
> "Oracle PL/SQL" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to oracle-plsql+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
--
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
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
---
You received this message because you are subscribed to the Google Groups
"Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to oracle-plsql+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
