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 <[email protected]>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 <[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. > > > -- 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 [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.
