This should run a bit faster:
SELECT b.id, b.prod_num
FROM ( SELECT clm_num,
subtyp_cd,
state,
contract,
quarter,
COLLECT (prod_num) AS prods
FROM t1
WHERE subtyp_cd = 'ORIG'
GROUP BY clm_num,
subtyp_cd,
state,
contract,
quarter) 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 "COLUMN_VALUE"
FROM TABLE (a.prods) f);
On Thu, May 16, 2013 at 2:57 PM, Gayathri <[email protected]> wrote:
> Thanks a lot Michael...
> Verified the data.. This worked prefect.
> But since this table is huge... it took a long time to execute.
>
>
>
> On Thu, May 16, 2013 at 1:12 PM, Michael Moore <[email protected]>wrote:
>
>> 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 <[email protected]> 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 <[email protected]> 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 <
>>>> [email protected]> 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 <
>>>>> [email protected]> 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 <[email protected]> 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 [email protected]
>>>>>>> To unsubscribe from this group, send email to
>>>>>>> [email protected]
>>>>>>> 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 [email protected].
>>>>>>> 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 [email protected]
>>>>> To unsubscribe from this group, send email to
>>>>> [email protected]
>>>>> 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 [email protected].
>>>>> 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 [email protected]
>>> To unsubscribe from this group, send email to
>>> [email protected]
>>> 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 [email protected].
>>> 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 [email protected]
>> To unsubscribe from this group, send email to
>> [email protected]
>> 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 [email protected].
>> 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 [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> 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 [email protected].
> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
