see if this gives what you want.
SELECT b.id,b.prod_num
FROM (SELECT DISTINCT clm_num,
subtyp_cd,
state,
contract,
quarter
FROM t1
WHERE subtyp_cd = 'ORIG') a
JOIN
t1 b
ON a.contract = b.contract
AND a.state = b.state
AND a.quarter = b.quarter
AND a.clm_num <> b.clm_num
AND b.subtyp_cd = 'RESUB'
WHERE b.prod_num NOT IN
(SELECT prod_num
FROM t1 x
WHERE x.contract = a.contract
AND x.state = a.state
AND x.quarter = a.quarter
AND subtyp_cd = 'ORIG');
On Thu, May 16, 2013 at 11:37 AM, Gayathri <gd0...@gmail.com> wrote:
> The result should pick:
>
> id:119 and 120 as it had the prods those are not in its original subtyp_cd.
>
>
> On Thu, May 16, 2013 at 11:35 AM, Gayathri <gd0...@gmail.com> wrote:
>
>> 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.
>
>
>
--
--
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.
