Home » SQL & PL/SQL » SQL & PL/SQL » Query with multiple inner rows
Query with multiple inner rows [message #217904] Tue, 06 February 2007 01:06
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I am stuck on a query. I have done the first part, now I have to make some changes.
I have attached the part of data for better understanding.
My code gives me result as in the upper part of excel sheet.
But the requirement is changed to as shown in the below part.
I have to display the data for item "R0935714" differently as the column 'K' is not null for this item. For the items where "ORD TYP" is not NULL , I have to display next 13 weeks
where first date will be the SUNDAY of this week. And then I have to accomodate the data against OI
with matching week date like 3/1/2007 falls under the week head "2/25/2007".
There are some other changes too, but I do not know how to start on this change. Please guide me so that I can
start on this.
I have created a table:

create table wk as select
 to_char(TRUNC((SYSDATE-7),'DAY') + p.pivot, 'MM-DD-YY') sdate, (p.pivot/7) serial
 from dual d,
 (select rownum pivot from all_objects) p
 where
 (select trunc((sysdate-7),'day') from dual) + p.pivot <= (select trunc((sysdate-7),'day') from dual) + 91
 and
 to_number(to_char((select trunc((sysdate-7),'day') from dual) + p.pivot, 'D')) in (1)

I thought this might help me in producing 13 week dates which starts from current week's sunday.

My current code which is producing the result in the upper part of the excel sheet is as under. I have to export it to csv file so there are pipes in between columns. :
SELECT style_name||','||color_name||','||iitem||','||
       packed_for||','||iinv||','||minincrorderqty||','||invqty||','||
       si_avg_fcst||','||''||','||ROUND(avg_hist,0)||','||si_jde_wo_type||','||
       sorder||','||TO_CHAR(sdate,'fmmm/dd/"0"yy')||','||
       sqty||','||i1item||','||minqty||','||
       s1qty||','||TO_CHAR(s1date,'fmmm/dd/yy')||','||
       s1order||','||tot||','||ROUND(main,1)||','
  FROM (SELECT DISTINCT 1 AS ordercolumn, i.style_name, i.color_name,
               i.item iitem, i.packed_for, i.inv_class iinv,
               MIN(si.incrorderqty) minincrorderqty ,inv.qty invqty,
               i.si_avg_fcst, NULL, 
               i.avg_hist, NULL si_jde_wo_type, NULL sorder,
               NULL sdate, NULL sqty, i1.item i1item, MIN(inv1.qty) minqty,
               NULL s1qty, NULL s1date, NULL s1order,
               MIN(DECODE(inv.qty, NULL, 0,
                                  (inv.qty + DECODE(inv1.qty, NULL, 0,
                                                             inv1.qty))))tot,
               MIN(DECODE(i.si_avg_fcst,
                           0, DECODE(inv.qty, NULL, 0,
                             (inv.qty + DECODE(inv1.qty,NULL, 0, inv1.qty))),
                                    (DECODE(inv.qty, NULL, 0,
                                        (inv.qty + DECODE(inv1.qty, NULL, 0,
                                            inv1.qty)))/i.si_avg_fcst))) main
          FROM stsc.item i, springs.si_jde_sku_work si, stsc.item i1,
               stsc.schedrcpts s, stsc.bom b, stsc.inventory inv,
               stsc.inventory inv1, stsc.schedrcpts s1
         WHERE i.item = si.item (+)
           AND i.item=s.item (+)
           AND i.item=b.item(+)
           AND i.item= inv.item (+)
           AND b.subord=i1.item (+)
           AND i1.item=inv1.item (+)
           AND i1.item = s1.item (+)
           AND (i.inv_class='F335' OR i.inv_class='F336'
                OR i.inv_class='F344' OR i.inv_class='L335'
                OR i.inv_class='L344')
           AND i.packed_for = 'WMART'
           AND (s.scheddate <= (SYSDATE + 51) OR s.scheddate IS NULL)
         GROUP BY 1, i.style_name, i.color_name,  i.item,
               inv.loc,  s.scheddate, i.packed_for, i1.item, i.inv_class,
               inv.qty, i.si_avg_fcst,i.avg_hist
         UNION ALL
        SELECT 2 AS ordercolumn, i.style_name, i.color_name, i.item,
               i.packed_for, NULL, NULL, NULL, NULL, :g_val,
               NULL,
               s.si_jde_wo_type, s.si_jde_order_num sorder,
               s.scheddate sdate, s.qty sqty, NULL, NULL, s1.qty s1qty,
               s1.scheddate s1date, s1.si_jde_order_num s1order, NULL, NULL
          FROM stsc.item i, springs.si_jde_sku_work si, stsc.schedrcpts s,
               stsc.bom b, stsc.inventory inv, stsc.item i1,
               stsc.inventory inv1, stsc.schedrcpts s1
         WHERE i.item = si.item (+)
           AND i.item=s.item (+)
           AND i.item=b.item(+)
           AND i.item= inv.item (+)
           AND b.subord=i1.item (+)
           AND i1.item=inv1.item (+)
           AND i1.item = s1.item (+)
           AND (i.inv_class='F335' OR i.inv_class='F336'
                OR i.inv_class='F344' OR i.inv_class='L335'
                OR i.inv_class='L344')
           AND i.packed_for = 'WMART'     
           AND (s.scheddate <= (SYSDATE + 51) OR s.scheddate IS NULL)
         GROUP BY 1, i.style_name, i.color_name, i.item, 
               inv.loc, s.scheddate, i.packed_for, s.si_jde_wo_type, i1.item, 
               s.si_jde_order_num, s.scheddate, s.qty,s1.qty, inv1.qty,
               si.incrorderqty, s1.scheddate, s1.si_jde_order_num
       ) qry1
    ORDER BY style_name, color_name, iitem, i1item,  sdate, ordercolumn;

I understand that this code is big.... but can you advice me on how to work with multi columns as in this case with help of new created table WK.

Thanks in advance.
Mona
Previous Topic: Help required in using IN clause
Next Topic: How to use LIKE inside Where & When clauses?
Goto Forum:
  


Current Time: Mon Dec 05 19:26:35 CST 2016

Total time taken to generate the page: 0.05661 seconds