you missed a lot in the query.you have to use inner-inner join 09003123631
On Fri, May 17, 2013 at 4:21 AM, Michael Moore <michaeljmo...@gmail.com>wrote:
> 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 <gd0...@gmail.com> 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
>> <michaeljmo...@gmail.com>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 <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.
>>>
>>>
>>>
>>
>> --
>> --
>> 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.
>
>
>
--
naveen
--
--
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.
