Home » SQL & PL/SQL » SQL & PL/SQL » query giving duplicate result
query giving duplicate result [message #196633] Fri, 06 October 2006 05:27 Go to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi,
I have written a query to fetch some data from multiple table. The data is coming with some duplications on the field "columnheader".
Please see my query :
SELECT i.planner_id,DECODE(i.purch_ind,1,'Y','N'),
       i.descr,w.item,ROUND(TO_NUMBER(i.avg_hist),2)
       w.columnheader,MAX(b.subord),MAX(TO_NUMBER(sku.oh)),
       MAX(TO_NUMBER(s.qty))       
  FROM stsc.item i,springs.si_skuproj_w w,
       stsc.bom b,stsc.sku sku,
       stsc.schedrcpts s
 WHERE (i.item=w.item)
   AND (((i.item=b.item (+)) AND (i.scen=b.scen (+))) 
   AND (i.scenbits=b.scenbits(+))) AND ((b.subord=sku.item (+)) 
   AND (b.loc=sku.loc (+))) 
   AND ((sku.item=s.item(+)) 
   AND (sku.loc=s.loc(+))) 
   AND ((w.columnheader='CBMPOHInventory' OR w.columnheader='CBMPProjOH' 
         OR w.columnheader='CBMPTotDmd' OR w.columnheader='RecArriv') 
   AND w.loc='DC' 
    OR (w.columnheader='PlanOrders' OR w.columnheader='SchedRcpts')
   AND (w.loc='175' OR w.loc='FIN')) 
   AND (i.inv_class='F335' OR i.inv_class='F344' 
        OR i.inv_class='L335' OR i.inv_class='L344') 
   AND i.packed_for='WMART'  
 GROUP BY w.item,w.columnheader,
          i.planner_id,i.purch_ind,i.descr,i.avg_hist
 ORDER BY w.item,decode(w.columnheader,'CBMPTotDmd','5','CBMPOHInventory','1',
      'SchedRcpts','2','PlanOrders','3','RecArriv','4','CBMPProjOH','6');

I am giving below a part of the data fetched for the ITEM R0085881.
PLAN_ID	Purch	DESCR	ITEM	AVG_HIST	COLUMNHEADER	SUBORD	OH	S/R QTY	
BNEW	N	PRTCL 	R0085881	102516.92	CBMPOHInventory	RS-0207	492924	100000	
BNEW	N	PRTCL 	R0085881	102516.92	SchedRcpts	RS-0208	492924	100000	
BNEW	N	PRTCL 	R0085881	102516.92	SchedRcpts	RS-0209	492924	100000	
BNEW	N	PRTCL 	R0085881	102516.92	PlanOrders	RS-0210	492924	100000	
BNEW	N	PRTCL 	R0085881	102516.92	PlanOrders	RS-0211	492924	100000	
BNEW	N	PRTCL 	R0085881	102516.92	RecArriv	RS-0212	492924	100000	
BNEW	N	PRTCL 	R0085881	102516.92	CBMPTotDmd	RS-0213	492924	100000	
BNEW	N	PRTCL 	R0085881	102516.92	CBMPProjOH	RS-0214	492924	100000	


You can see that the above data is duplicationg for the data PlanOrders and SchedRcpts. This should have come using DISTINCT clause.I tried using DISTINCT but this is not giving the required data. There should have been only 6 records but I am getting 8 records. Please advice as how to solve this so that it doesn't repeat for columnheader column.

Regards,
Sonali

[Updated on: Fri, 06 October 2006 05:28]

Report message to a moderator

Re: query giving duplicate result [message #196634 is a reply to message #196633] Fri, 06 October 2006 05:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Change the GROUP BY clause to
GROUP BY i.planner_id,DECODE(i.purch_ind,1,'Y','N'),
       i.descr,w.item,ROUND(TO_NUMBER(i.avg_hist),2)
       w.columnheader


The way you have it at the moment, if you have rows in ITEM with values or 2 and 3 in the column purch_ind, these will be groupbed as two seperate rows, but the Ddecode will show them both as 'N'.
Re: query giving duplicate result [message #196640 is a reply to message #196634] Fri, 06 October 2006 05:53 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi JRow,
I tried the Group BY clause but it didn't worked but gave the same result.

PLAN_ID	Purch	DESCR	ITEM	AVG_HIST	COLUMNHEADER	SUBORD	OH	S/R QTY
BNEW	N	PRTCL 	R0085881	102516.92	CBMPOHInventory	RS-0207	492924	100000
BNEW	N	PRTCL 	R0085881	102516.92	SchedRcpts	RS-0208	492924	100000
BNEW	N	PRTCL 	R0085881	102516.92	SchedRcpts	RS-0209	492924	100000
BNEW	N	PRTCL 	R0085881	102516.92	PlanOrders	RS-0210	492924	100000
BNEW	N	PRTCL 	R0085881	102516.92	PlanOrders	RS-0211	492924	100000
BNEW	N	PRTCL 	R0085881	102516.92	RecArriv	RS-0212	492924	100000
BNEW	N	PRTCL 	R0085881	102516.92	CBMPTotDmd	RS-0213	492924	100000
BNEW	N	PRTCL 	R0085881	102516.92	CBMPProjOH	RS-0214	492924	100000
BNEW	N	SHECL	R0126328	1487.08	CBMPOHInventory	RS129889	11208	5541
BNEW	N	SHECL	R0126328	1487.08	SchedRcpts	RS129889	11208	5541
BNEW	N	SHECL	R0126328	1487.08	SchedRcpts	RS129889	11208	5541
BNEW	N	SHECL	R0126328	1487.08	PlanOrders	RS129889	11208	5541
BNEW	N	SHECL	R0126328	1487.08	PlanOrders	RS129889	11208	5541
BNEW	N	SHECL	R0126328	1487.08	RecArriv	RS129889	11208	5541
BNEW	N	SHECL	R0126328	1487.08	CBMPTotDmd	RS129889	11208	5541
BNEW	N	SHECL	R0126328	1487.08	CBMPProjOH	RS129889	11208	5541



I think we need to use an inner query which will only allow DISTINCT columnheader with the outer condition.
The group by order is fine. But we need to use an inner query so that it filters the duplicate columnheader. There is a large record and I am showing just few here.
I tried using inner query but I am not very proficient in that and it didn't worked.

Thanks,
Sonali

[Updated on: Fri, 06 October 2006 05:55]

Report message to a moderator

Re: query giving duplicate result [message #196649 is a reply to message #196640] Fri, 06 October 2006 06:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The query you've got should do it - there's something about your data I think.

Have a look at the underlying data that produces (for example) these two rows here:
BNEW	N	SHECL	R0126328	1487.08	SchedRcpts	RS129889	11208	5541
BNEW	N	SHECL	R0126328	1487.08	SchedRcpts	RS129889	11208	5541
and see if you can spot what's going on. It might be that one of the fields has a trailing space in it or something.
It's definitely something to do with the GROUP BY, as it's returning different values for the aggregated columns.
Re: query giving duplicate result [message #196653 is a reply to message #196649] Fri, 06 October 2006 07:01 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi JRow,
Actually this query is little more bigger. I tried to give it leavig aside some details for making simple. But I think that was a wrong idea as it hid important details as why the duplicate is coming. I am attching the data I am getting with duplicates as there are more columns along with this. The reason seems to be because of the data in dates column that is w.week1 to w.week28. But still the data ihighlighted in yellow should ot come and we need only distinct columnheader.

SELECT i.planner_id,DECODE(i.purch_ind,1,'Y','N'),
       i.descr,w.item,ROUND(TO_NUMBER(i.avg_hist),2),
       w.columnheader,TO_NUMBER(w.week1),
       TO_NUMBER(w.week2),TO_NUMBER(w.week3),
       TO_NUMBER(w.week4),TO_NUMBER(w.week5),
       TO_NUMBER(w.week6),TO_NUMBER(w.week7),
       TO_NUMBER(w.week8),TO_NUMBER(w.week9),
       TO_NUMBER(w.week10),TO_NUMBER(w.week11),
       TO_NUMBER(w.week12),TO_NUMBER(w.week13),
       TO_NUMBER(w.week14),TO_NUMBER(w.week15),
       TO_NUMBER(w.week16),TO_NUMBER(w.week17),
       TO_NUMBER(w.week18),TO_NUMBER(w.week19),
       TO_NUMBER(w.week20),TO_NUMBER(w.week21),
       TO_NUMBER(w.week22),TO_NUMBER(w.week23),
       TO_NUMBER(w.week24),TO_NUMBER(w.week25),
       TO_NUMBER(w.week26),TO_NUMBER(w.week27),
       TO_NUMBER(w.week28),MAX(b.subord),
       MAX(TO_NUMBER(sku.oh)),MAX(TO_NUMBER(s.qty))
    
  FROM stsc.item i,springs.si_skuproj_w w,
       stsc.bom b,stsc.sku sku,
       stsc.schedrcpts s
 WHERE (i.item=w.item)
   AND (((i.item=b.item (+)) AND (i.scen=b.scen (+))) 
   AND (i.scenbits=b.scenbits(+))) AND ((b.subord=sku.item (+)) 
   AND (b.loc=sku.loc (+))) 
   AND ((sku.item=s.item(+)) 
   AND (sku.loc=s.loc(+))) 
   AND ((w.columnheader='CBMPOHInventory' OR w.columnheader='CBMPProjOH' 
         OR w.columnheader='CBMPTotDmd' OR w.columnheader='RecArriv') 
   AND w.loc='DC' 
    OR (w.columnheader='PlanOrders' OR w.columnheader='SchedRcpts')
   AND (w.loc='175' OR w.loc='FIN')) 
   AND (i.inv_class='F335' OR i.inv_class='F344' 
        OR i.inv_class='L335' OR i.inv_class='L344') 
   AND i.packed_for='WMART'  
 GROUP BY w.item,w.columnheader,
          i.planner_id,i.purch_ind,i.descr,i.avg_hist,w.week1,w.week2,
          w.week3,w.week4,w.week5,w.week6,w.week7,w.week8,w.week9,w.week10,
          w.week11,w.week12,w.week13,w.week14,w.week15,w.week16,w.week17,w.week18,
          w.week19,w.week20,w.week21,w.week22,w.week23,w.week24,w.week25,w.week26,
          w.week27,w.week28
 ORDER BY w.item,decode(w.columnheader,'CBMPTotDmd','5','CBMPOHInventory','1',
      'SchedRcpts','2','PlanOrders','3','RecArriv','4','CBMPProjOH','6');


Thanks again,
Sonali
  • Attachment: data.xls
    (Size: 26.50KB, Downloaded 144 times)
Re: query giving duplicate result [message #196658 is a reply to message #196653] Fri, 06 October 2006 07:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is a completely different problem, and the results that you're getting look correct to me.
I'm guessing that you've got two rows in si_skuproj_w for the invalid rows in the final results.
Try replacing the SELECT with
SELECT i.planner_id,DECODE(i.purch_ind,1,'Y','N'),
       i.descr,w.item,ROUND(TO_NUMBER(i.avg_hist),2),
       w.columnheader,max(TO_NUMBER(w.week1),
       max(TO_NUMBER(w.week2)),max(TO_NUMBER(w.week3)),
       max(TO_NUMBER(w.week4)),max(TO_NUMBER(w.week5)),
       max(TO_NUMBER(w.week6)),max(TO_NUMBER(w.week7)),
       max(TO_NUMBER(w.week8)),max(TO_NUMBER(w.week9)),
       max(TO_NUMBER(w.week10)),max(TO_NUMBER(w.week11)),
       max(TO_NUMBER(w.week12)),max(TO_NUMBER(w.week13)),
       max(TO_NUMBER(w.week14)),max(TO_NUMBER(w.week15)),
       max(TO_NUMBER(w.week16)),max(TO_NUMBER(w.week17)),
       max(TO_NUMBER(w.week18)),max(TO_NUMBER(w.week19)),
       max(TO_NUMBER(w.week20)),max(TO_NUMBER(w.week21)),
       max(TO_NUMBER(w.week22)),max(TO_NUMBER(w.week23)),
       max(TO_NUMBER(w.week24)),max(TO_NUMBER(w.week25)),
       max(TO_NUMBER(w.week26)),max(TO_NUMBER(w.week27)),
       max(TO_NUMBER(w.week28)),MAX(b.subord),
       MAX(TO_NUMBER(sku.oh)),MAX(TO_NUMBER(s.qty))

and change the GROUP BY to
GROUP BY w.item,w.columnheader,
          i.planner_id,i.purch_ind,i.descr,i.avg_hist
Re: query giving duplicate result [message #196911 is a reply to message #196658] Mon, 09 October 2006 02:14 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Thanks JRow,
I used min(TO_NUMBER(w.week1) and it helped me match the data from crystal report.
I am now stuck at the last point where I have one more last column as date column named "scheddate".
I have again to filter some data based on the MAX data from the
MAX(TO_NUMBER(s.qty)).
There are at times 2 or more dates coming due to more number of qty. I have to fetch the date where s.qty is maximum.
SELECT i.planner_id,DECODE(i.purch_ind,1,'Y','N'),
       i.descr,w.item,ROUND(TO_NUMBER(i.avg_hist),2),
       w.columnheader,MIN(TO_NUMBER(w.week1)),
       MIN(TO_NUMBER(w.week2)),MIN(TO_NUMBER(w.week3)),
       MAX(b.subord),MAX(TO_NUMBER(sku.oh)),
       MAX(TO_NUMBER(s.qty)),
       TO_CHAR(s.scheddate,'fmmm/dd/fmyy hh:mi:ssAM')
  FROM stsc.item i,springs.si_skuproj_w w,
       stsc.bom b,stsc.sku sku,
       stsc.schedrcpts s
 WHERE (i.item=w.item)
   AND (((i.item=b.item (+)) AND (i.scen=b.scen (+))) 
   AND (i.scenbits=b.scenbits(+))) AND ((b.subord=sku.item (+)) 
   AND (b.loc=sku.loc (+))) 
   AND ((sku.item=s.item(+)) 
   AND (sku.loc=s.loc(+))) 
   AND ((w.columnheader='CBMPOHInventory' 
         OR w.columnheader='CBMPProjOH' 
         OR w.columnheader='CBMPTotDmd' 
         OR w.columnheader='RecArriv') 
   AND w.loc='DC' 
    OR (w.columnheader='PlanOrders'
        OR w.columnheader='SchedRcpts')
   AND (w.loc='175' OR w.loc='FIN')) 
   AND (i.inv_class='F335' OR i.inv_class='F344' 
        OR i.inv_class='L335' OR i.inv_class='L344') 
   AND i.packed_for='WMART'  
 GROUP BY w.item,w.columnheader,i.planner_id,
       i.purch_ind,i.descr,i.avg_hist,
       TO_CHAR(s.scheddate,'fmmm/dd/fmyy hh:mi:ssAM')
 ORDER BY w.item


The above query is giving the output as the attached excel file.
According to the requirement I only need the rows highlighted in yellow. This can be achived by getting the date where qty is max(qty).
I tried using an inner query to select date where qty is the max qty.
But as I told earlier I have not used much of inner query. And it is not giving me the correct result.
Please help me how to go about this.

I am also pasting the data here though this is too big.
PLAN_ID	Purch	DESCR	ITEM	AVG_HIST	COLUMNHEADER	10/8/2006	10/15/2006	10/22/2006	SUBORD	OH	S/R QTY	SCHEDDATE
BNEW	N	ALSOUPTOSL	1480198	1206.46	CBMPOHInventory	5910	0	0	RS64992	4050	5000	10/27/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	CBMPOHInventory	5910	0	0	RS64992	4050	96	9/22/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	SchedRcpts	0	2000	0	RS64992	4050	5000	10/27/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	SchedRcpts	0	2000	0	RS64992	4050	96	9/22/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	PlanOrders	0	500	790	RS64992	4050	5000	10/27/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	PlanOrders	0	500	790	RS64992	4050	96	9/22/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	RecArriv	0	820	1200	RS64992	4050	5000	10/27/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	RecArriv	0	820	1200	RS64992	4050	96	9/22/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	CBMPTotDmd	2037.6923	1292.3077	1200	RS64992	4050	5000	10/27/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	CBMPTotDmd	2037.6923	1292.3077	1200	RS64992	4050	96	9/22/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	CBMPProjOH	3872.3077	3400	3400	RS64992	4050	5000	10/27/06 12:00:00AM
BNEW	N	ALSOUPTOSL	1480198	1206.46	CBMPProjOH	3872.3077	3400	3400	RS64992	4050	96	9/22/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	CBMPOHInventory	4380	0	0	RS127677	3756	4155	10/13/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	CBMPOHInventory	4380	0	0	RS127677	3756	6000	10/27/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	SchedRcpts	0	0	0	RS127677	3756	4155	10/13/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	SchedRcpts	0	0	0	RS127677	3756	6000	10/27/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	PlanOrders	0	0	0	RS127677	3756	4155	10/13/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	PlanOrders	0	0	0	RS127677	3756	6000	10/27/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	RecArriv	0	0	0	RS127677	3756	4155	10/13/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	RecArriv	0	0	0	RS127677	3756	6000	10/27/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	CBMPTotDmd	1357.6923	592.3077	550	RS127677	3756	4155	10/13/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	CBMPTotDmd	1357.6923	592.3077	550	RS127677	3756	6000	10/27/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	CBMPProjOH	3022.3077	2430	1880	RS127677	3756	4155	10/13/06 12:00:00AM
BNEW	N	AMAZGLYRIC	1480352	723.85	CBMPProjOH	3022.3077	2430	1880	RS127677	3756	6000	10/27/06 12:00:00AM


Thanks a lot,
Sonali
  • Attachment: data1.xls
    (Size: 17.00KB, Downloaded 152 times)

[Updated on: Mon, 09 October 2006 02:34]

Report message to a moderator

Re: query giving duplicate result [message #196916 is a reply to message #196911] Mon, 09 October 2006 02:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd try using an analytic function here. It may slow the query down slightly, but it's the easiest approach.
Change the Select clause to
SELECT DISTINCT i.planner_id,DECODE(i.purch_ind,1,'Y','N'),
       i.descr,w.item,ROUND(TO_NUMBER(i.avg_hist),2),
       w.columnheader,MIN(TO_NUMBER(w.week1)),
       MIN(TO_NUMBER(w.week2)),MIN(TO_NUMBER(w.week3)),
       MAX(b.subord),MAX(TO_NUMBER(sku.oh)),
       MAX(TO_NUMBER(s.qty)),
       TO_CHAR(max(s.scheddate) over (partition by i.descr,w.item,ROUND(TO_NUMBER(i.avg_hist),2),       w.columnheader order by qty),'fmmm/dd/fmyy hh:mi:ssAM')

and remove the Scheddate column from the Group By clause.
Re: query giving duplicate result [message #196917 is a reply to message #196916] Mon, 09 October 2006 02:40 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi Jrow,
I did that but it gave the error:
TO_CHAR(max(s.scheddate) over (partition by i.descr,w.item,ROUND
*
ERROR at line 6:
ORA-00979: not a GROUP BY expression

Regards,
Sonali

[Updated on: Mon, 09 October 2006 03:29]

Report message to a moderator

Re: query giving duplicate result [message #196928 is a reply to message #196917] Mon, 09 October 2006 03:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're right.
Not enough coffee, I think.

Try replacing the
TO_CHAR(s.scheddate,'fmmm/dd/fmyy hh:mi:ssAM')
in the SELECT with
SUBSTR(MAX(to_char(to_number(s.qty),'0000000000')||TO_CHAR(s.scheddate,'fmmm/dd/fmyy hh:mi:ssAM')),11)
and keep the scheddate out of the group by.
What we're doing here is making a string out of the quantity and the date in such a way that the greatest value of the string is the one with the highest Qty. Then we just take the part of that string with the date encoded in it and return that date.

Your date mask has FMs in it, so it's possible that you'll have to remove them when you're building this sorting string, in order to get a fixed length date string.
Re: query giving duplicate result [message #196933 is a reply to message #196928] Mon, 09 October 2006 04:13 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi Jrow,
Thanks a ton Smile
Your idea was great and the problem is solved.

Thanks a lot
Sonali
icon4.gif  Re: query giving duplicate result [message #197163 is a reply to message #196928] Tue, 10 October 2006 05:45 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi Jrow,
Sorry for bothering you again.
There is a large amount of data around 4000 records.
The SUBSTR is not working here because there are records where there is no qty and no date.
The qty and scheddate should be based on the MAX(SUBORD) value.
So in order to show the respective qty and scheddate column based on the MAX(subord), we might have to use inner query.

What do you suggest.
I am attaching the data and the one highlighted in yellow is the correct record per item.

ITEM AVG_HIST COLUMNHEADER 10/8/2006 10/15/2006 10/22/2006 SUBORD OH	QTY	SCHEDDATE
1495191  0	CBMPOHInventory	7070	0	0	1474828	0	3000	11/3/06 12:00:00AM
1495191     0	CBMPOHInventory	7070	0	0	1495123	0		
1495191     0	SchedRcpts	0	0	0	1474828	0	3000	11/3/06 12:00:00AM
1495191  0	SchedRcpts	0	0	0	1495123	0		
1495191  0	PlanOrders	0	0	0	1474828	0	3000	11/3/06 12:00:00AM
1495191  0	PlanOrders	0	0	0	1495123	0		
1495191  0	RecArriv	0	0	0	1474828	0	3000	11/3/06 12:00:00AM
1495191  0	RecArriv	0	0	0	1495123	0		
1495191  0	CBMPTotDmd	720	0	0	1474828	0	3000	11/3/06 12:00:00AM
1495191  0	CBMPTotDmd	720	0	0	1495123	0		
1495191  0	CBMPProjOH	6350	6350	6350	1474828	0	3000	11/3/06 12:00:00AM
1495191  0	CBMPProjOH	6350	6350	6350	1495123	0		



Regards,
Sonali
  • Attachment: data.xls
    (Size: 14.50KB, Downloaded 142 times)

[Updated on: Tue, 10 October 2006 06:47]

Report message to a moderator

Re: query giving duplicate result [message #197177 is a reply to message #197163] Tue, 10 October 2006 06:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
NVL the quantity to 0 and the date to some low value like '01-JAN-1900'
Re: query giving duplicate result [message #197181 is a reply to message #197177] Tue, 10 October 2006 06:46 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi JRow,
I didn't get you.
Actually I need the record highlighted in yellow and not the rest ones.
PLAN_ID	Purch DESCR ITEM AVG_HIST COLUMNHEADER 10/8/2006 10/15/2006 10/22/2006 SUBORD OH QTY	SCHEDDATE
BNEW	N	FLEEC	1495191	0	CBMPOHInventory	7070	0	0	1474828	0	3000	11/3/06 12:00:00AM
BNEW	N	FLEEC	1495191	0	CBMPOHInventory	7070	0	0	1495123	0		


In the above data, only the second row needs to be fetched as this is having the max(subord) value that is 1495123 > 1474828.
Out of the above two records, I need the record having max value of SUBORD.
So in this case, we need to find a way to get the correct value of qty and scheddate where the value of SUBORD is greatest.
Please suggest

Regards,
Sonali
  • Attachment: data.xls
    (Size: 15.50KB, Downloaded 104 times)

[Updated on: Tue, 10 October 2006 06:57]

Report message to a moderator

Re: query giving duplicate result [message #197186 is a reply to message #197181] Tue, 10 October 2006 07:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, I think this is what you're looking for:

Get the schedule date for the row from this set of rows which has the greatest value in SUBORD.
If all the rows have the same SUBORD, then get the schedule date for the row with the largest quantity.

If this is the case, then you can modify my previous solution and use
MAX(rpad(subord,20)||to_char(qty,'000000000')||schedule_date)
as the sorting string.

If that's not the answer you're looking for, you'll have to provide more explanation.
Re: query giving duplicate result [message #197408 is a reply to message #197186] Wed, 11 October 2006 03:04 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi JRow,
Thanks for your help.
I tried using :MAX(rpad(subord,20)||to_char(qty,'000000000')||schedule_date) but this is not exactly matching the requirement.

The requirement is :

MAX (Subord),->MAX(OH)->MAX(qty)->, max(scheddate)

I am getting correct values for the columns till Subord.
After that the logic is as :
I should get MAX(OH) out of MAX(Subord).
Respective to the MAX(OH), I should get MAX(Qty) and then respective to the MAX(Qty), I should get MAX(Scheddate).

The below data will clarify this :
PLAN_ID Purch   DESCR   ITEM   AVG_HIST COLUMNHEADER 10/8/2006 10/15/2006 10/22/2006 SUBORD      OH      QTY          SCHEDDATE
BNEW	N	PURPL	1480433	1497.69	CBMPOHInventory	6640	0	0	RS124510	9714	873	10/20/06 12:00:00AM
BNEW	N	PURPL	1480433	1497.69	CBMPOHInventory	6640	0	0	RS130223	8018	4000	11/17/06 12:00:00AM
BNEW	N	PURPL	1480433	1497.69	CBMPOHInventory	6640	0	0	RS130223	8018	5000	11/17/06 12:00:00AM
BNEW	N	PURPL	1480433	1497.69	CBMPOHInventory	6640	0	0	RS130223	8018	5000	11/19/06 12:00:00AM
BNEW	N	PURPL	1480433	1497.69	CBMPOHInventory	6640	0	0	RS130223	7896	5000	11/3/06 12:00:00AM

In the above data the correct row to be fetched is :
BNEW	N	PURPL	1480433	1497.69	CBMPOHInventory	6640	0	0	RS130223	8018	5000	11/19/06 12:00:00AM

I am using the below code now , please suggest the changes I need to make in the below code :
SELECT i.planner_id,DECODE(i.purch_ind,1,'Y','N'),
i.descr,w.item,ROUND(TO_NUMBER(i.avg_hist),2),
w.columnheader,MIN(ROUND(TO_NUMBER(w.week1))),
MIN(ROUND(TO_NUMBER(w.week2))),MIN(ROUND(TO_NUMBER(w.week3))),
MAX(b.subord),MAX(TO_NUMBER(sku.oh)),MAX(s.qty),
SUBSTR(MAX(to_char(to_number(s.qty),'0000000000')||
     TO_CHAR(s.scheddate,'mm/dd/yy hh:mi:ssAM')),12)
FROM stsc.item i,springs.si_skuproj_w w,
     stsc.bom b,stsc.sku sku,
     stsc.schedrcpts s
WHERE (i.item=w.item)
   AND (((i.item=b.item (+)) AND (i.scen=b.scen (+))) 
   AND (i.scenbits=b.scenbits(+))) AND ((b.subord=sku.item (+)) 
   AND (b.loc=sku.loc (+))) 
   AND ((sku.item=s.item(+)) 
   AND (sku.loc=s.loc(+))) 
   AND ((w.columnheader='CBMPOHInventory' OR  
         w.columnheader='CBMPProjOH' 
         OR w.columnheader='CBMPTotDmd' OR  
         w.columnheader='RecArriv') 
   AND w.loc='DC' 
    OR (w.columnheader='PlanOrders' OR 
        w.columnheader='SchedRcpts')
   AND (w.loc='175' OR w.loc='FIN')) 
   AND (i.inv_class='F335' OR i.inv_class='F344' 
        OR i.inv_class='L335' OR i.inv_class='L344') 
   AND i.packed_for='WMART'  
 GROUP BY w.item,decode(w.columnheader,'CBMPTotDmd','5','CBMPOHInventory','1',
  'SchedRcpts','2','PlanOrders','3','RecArriv','4',
   'CBMPProjOH','6'),
     w.columnheader,i.planner_id,i.purch_ind,
     i.descr,i.avg_hist
 ORDER BY w.item,
       decode(w.columnheader,'CBMPTotDmd','5',
                        'CBMPOHInventory','1',
                             'SchedRcpts','2',
                            'PlanOrders','3','
                               RecArriv','4','
                             CBMPProjOH','6');

Thanks for looking into this.

Regards,
Sonali
Re: query giving duplicate result [message #197411 is a reply to message #197408] Wed, 11 October 2006 03:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to add either a to_char(OH) or rpad(OH) (depending on whether OH is a number or char column) into the MAX after the SUBORD and before the QTY
Re: query giving duplicate result [message #197466 is a reply to message #197411] Wed, 11 October 2006 06:07 Go to previous message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Thanks JRow,
Finally my problem is solved and that also in a very easy way with your all help.
Thanks again,

Sonali
Previous Topic: about sQL error....
Next Topic: DDL in PL/SQL => insufficient privileges [SOLVED]
Goto Forum:
  


Current Time: Sat Dec 10 13:06:34 CST 2016

Total time taken to generate the page: 0.09707 seconds