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.
