Thanks Michael for taking a look into this -
I have Corrected the data now :
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=IL, Contract = C1,
Quarter=01/01/2000, prod = p14
id= 120, Clm_num=11, subtyp_cd = RESUM, state=IL, Contract = C1,
Quarter=01/01/2000, prod = p15
So it is matching on Contract, state, Quarter.
Thanks in advance!
On Thu, May 16, 2013 at 10:52 AM, Michael Moore <michaeljmo...@gmail.com>wrote:
> I could give you a query that can accomplish what you want, but there is
> one ambiguity that needs to be cleared up. Suppose you had two RESUBs that
> matched a given ORIG, what would you want the result to be?
> Add one more row of data to your table:
> 120,11,RESUB,CA,C1,1/1/2000,p15
>
> What should the result of the query look like?
>
>
>
> On Thu, May 16, 2013 at 10:43 AM, Michael Moore
> <michaeljmo...@gmail.com>wrote:
>
>> 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.
>
>
>
--
--
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.
