Home » SQL & PL/SQL » SQL & PL/SQL » GROUP BY
GROUP BY [message #197933] Fri, 13 October 2006 05:59 Go to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi,
I am working on a script where I am stuck at a grouping point.

The data to work on is
ITEM DESCR SKU_STAT PACKED_FOR SOP_APP DFULOC LOC ALLOCFACTOR FCST
1435416	GTFIT 	A	LNTC	1	43687	261	0	WAMA16
1435416	GTFIT 	A	LNTC	1	43687	261	0	WAMA16
1435416	GTFIT 	A	LNTC	1	43687	262	0	WAMA16
1435416	GTFIT 	A	LNTC	1	43687	262	0	WAMA16
1435416	GTFIT 	A	LNTC	1	43687	262	.1	WAMA16
1435416	GTFIT 	A	LNTC	1	43687	262	.1	WAMA16
1435416	GTFIT 	A	LNTC	1	43687	262	1	WAMA16
1435416	GTFIT 	A	LNTC	1	43687	262	1	WAMA16
1433097	BDFIB	A	BBB	0	4081	261	0.15	


And the required data is :
ITEM  DESCR SKU_STAT PACKED_FOR SOP_APP DFULOC LOC ALLOCFACTOR FCST
1435416	GTFIT 	A	LNTC	1	43687	261	0	 1
1435416	GTFIT 	A	LNTC	1	43687	262	1        2
1433097	BDFIB	A	BBB	0	4081	261	0.15	 0


The data which I am getting is :
ITEM  DESCR SKU_STAT PACKED_FOR SOP_APP DFULOC LOC ALLOCFACTOR FCST
1435416	GTFIT 	A	LNTC	1	43687	261	0	 2
1435416	GTFIT 	A	LNTC	1	43687	262    .1        2
1435416	GTFIT 	A	LNTC	1	43687	262	1        2
1433097	BDFIB	A	BBB	0	4081	261	0.15	 0


I am getting wrong data in the last column. The count is coming more than the correct value.
In the data, in the last two columns, I have to take the MAX value of ALLOCFACTOR which is '1' in case of '1435416' and the value of FCST is count of the FCST for MAX(ALLOCFACTOR) which is 2 for '1435416'.

Below is my script :
SELECT s.item,i.descr,
       i.sku_stat,i.packed_for,
       dfu.sop_app,d.dfuloc,s.loc,
       MAX(ROUND(TO_NUMBER(d.allocfactor))),
       COUNT(f.fcst)
  FROM   stsc.item i,stsc.sku s,
        stsc.supplymethod sm,
        stsc.dfutosku d,stsc.dmdunit du,
        stsc.fcst f,stsc.dfu dfu
WHERE  (i.item=s.item)
  AND ((s.item=sm.item) AND (s.loc=sm.loc))
  AND ((s.item=d.item (+)) AND (s.loc=d.skuloc (+)))
  AND (s.item=du.sku(+)) AND (du.fcst=f.fcst(+))
  AND (((d.fcst=dfu.fcst(+)) AND (d.dmdgroup=dfu.dmdgroup(+)))
  AND (d.dfuloc=dfu.loc(+)))
  AND (i.inv_class='F305' OR i.inv_class='L305' OR i.inv_class='L342')
  AND (s.loc='260' OR s.loc='261' OR s.loc='262')
GROUP BY s.item,d.dfuloc,
    s.loc,i.descr,i.sku_stat,
  i.packed_for,dfu.sop_app,f.fcst
ORDER BY s.item,s.loc


Please suggest me as how can I get the FCST value correct.

Regards,
Sonali

[Updated on: Fri, 13 October 2006 07:52]

Report message to a moderator

Re: GROUP BY [message #197970 is a reply to message #197933] Fri, 13 October 2006 09:27 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

here is one way....

The data to work on is

ITEM  	   DESCR   SKU_STAT 	 PACKED_FOR    SOP_APP 	  DFULOC 	LOC 	ALLOCFACTOR 	FCST
1435416	   GTFIT 	A	  LNTC		   1	   43687	261		0	  WAMA16
1435416	   GTFIT 	A	  LNTC		   1	   43687	261		0	  WAMA16
1435416	   GTFIT 	A	  LNTC		   1	   43687	262		0	  WAMA16
1435416	   GTFIT 	A	  LNTC		   1	   43687	262		0	  WAMA16
1435416	   GTFIT 	A	  LNTC		   1	   43687	262		.1	  WAMA16
1435416	   GTFIT 	A	  LNTC		   1	   43687	262		.1	  WAMA16
1435416	   GTFIT 	A	  LNTC		   1	   43687	262		1	  WAMA16
1435416	   GTFIT 	A	  LNTC		   1	   43687	262		1	  WAMA16
1433097	   BDFIB	A	  BBB		   0	   4081		261		0.15	



And the required data is :
ITEM  	  DESCR  SKU_STAT 	PACKED_FOR    SOP_APP 	  DFULOC        LOC 	ALLOCFACTOR 	FCST
1435416	  GTFIT 	A	LNTC		   1	  43687		261		0	 	2
1435416	  GTFIT 	A	LNTC		   1	  43687		262		1        	2
1433097	  BDFIB	        A	BBB                0	  4081		261		0.15	 	1




SELECT a.*
  FROM (SELECT   item, descr, sku_stat, packed_for, sop_app, dfuloc, loc,
                 allocfactor, COUNT (*)
            FROM table_a
        GROUP BY item, descr, sku_stat, packed_for, sop_app, dfuloc, loc, allocfactor) a,
       (SELECT   item, descr, sku_stat, packed_for, sop_app,
                 dfuloc, loc, MAX (allocfactor) allocfactor
            FROM table_a 
        GROUP BY item, descr, sku_stat, packed_for, sop_app, dfuloc, loc) b
 WHERE b.item = a.item
   AND b.descr = a.descr
   AND b.sku_stat = a.sku_stat
   AND b.packed_for = a.packed_for
   AND b.sop_app = a.sop_app
   AND b.dfuloc = a.dfuloc
   AND b.loc = a.loc
   AND b.allocfactor = a.allocfactor





Naveen

[Updated on: Fri, 13 October 2006 09:38]

Report message to a moderator

Re: GROUP BY [message #197971 is a reply to message #197933] Fri, 13 October 2006 09:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This time, I think analytics are the only way.

select item
      ,descr
      ,sku_stat
      ,packed_for
      ,sop_app
      ,dfuloc
      ,loc
      ,max_allocfactor
      ,fcst
from (SELECT distinct
             item
            ,descr
            ,sku_stat
            ,packed_for
            ,sop_app
            ,dfuloc
            ,loc
            ,allocfactor
            ,max(allocfactor) over (partition by item,descr,sku_stat,packed_for,sop_app,dfuloc,loc) max_allocfactor
            ,count(fcst) over (partition by item,descr,sku_stat,packed_for,sop_app,dfuloc,loc,allocfactor) fcst
      FROM   <your FROM and WHERE clause goes here, but without the group by and order by section>
      )
where allocfactor = max_allocfactor      
ORDER BY item,loc
Re: GROUP BY [message #198436 is a reply to message #197971] Tue, 17 October 2006 03:18 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Thanks JRow,
Below is the code according to what I understood from your instaructions. I feel there is something wrong ...in my understanding.
Please see the below code and let me know if I am on the correct path as this is giving errors.


SELECT s.item,i.descr,i.sku_stat,i.packed_for,
       dfu.sop_app,d.dfuloc,s.loc,
       max_allocfactor,fcst
FROM (SELECT DISTINCT s.item,i.descr,i.sku_stat,
             i.packed_for,dfu.sop_app,d.dfuloc,
             s.loc,d.allocfactor,
              MAX(d.allocfactor) OVER (PARTITION BY  
              s.item,i.descr,i.sku_stat,i.packed_for,
             dfu.sop_app,d.dfuloc,s.loc) max_allocfactor,
             COUNT(f.fcst) OVER (PARTITION BY  
             s.item,i.descr,i.sku_stat,i.packed_for,
             dfu.sop_app,d.dfuloc,s.loc,d.allocfactor) fcst
        FROM stsc.item i,stsc.sku s,
             stsc.supplymethod sm,
             stsc.dfutosku d,stsc.dmdunit du,
             stsc.fcst f,stsc.dfu dfu
       WHERE  (i.item=s.item)
         AND ((s.item=sm.item) AND (s.loc=sm.loc))
         AND ((s.item=d.item (+)) AND (s.loc=d.skuloc (+)))
         AND (s.item=du.sku(+)) AND (du.fcst=f.fcst(+))
         AND (((d.fcst=dfu.fcst(+)) AND (d.dmdgroup=dfu.dmdgroup(+)))
         AND (d.dfuloc=dfu.loc(+)))
         AND (i.inv_class='F305' OR i.inv_class='L305' OR i.inv_class='L342')
         AND (s.loc='260' OR s.loc='261' OR s.loc='262')
     )
WHERE d.allocfactor = max_allocfactor
ORDER BY s.item,s.loc


Regards,
Sonali

[Updated on: Tue, 17 October 2006 03:20]

Report message to a moderator

Re: GROUP BY [message #198439 is a reply to message #198436] Tue, 17 October 2006 03:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you could post the error that you're getting, it would help.
Re: GROUP BY [message #198441 is a reply to message #198436] Tue, 17 October 2006 03:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, the first problem I've spottedis that you've got confused about table aliases again.
You've got table aliases on all the columns in the outermost SELECT, and you shouldn't.
Remove all of the 's.', 'i.', 'dfu.' etc in the outermost SELECT and WHERE clauses

A table alias in a SELECT is there to tell the optimiser which table it needs to select the column from. The table aliases can't be used outside the query they are defined in.
eg

Here, the table aliases (a & b) are used to identify the origin of the two columns COL_1 and COL_2:
SELECT a.col_1
      ,b.col_2
FROM   table_1 a
      ,table_1 b;


This doesn't work, because the aliases a and b are only valid inside the inner SELECT (the SELECT in the brackets):
SELECT a.col_1
      ,b.col_2
      ,c.col_1
FROM (SELECT a.col_1
            ,b.col_2
       FROM  table_1 a
            ,table_1 b)
     ,table_1 c;


What you can do is provide an alias for the entire inner select, to allow you to refer to columns that come from there:
SELECT d.col_1
      ,d.col_2
      ,c.col_1
FROM (SELECT a.col_1
            ,b.col_2
       FROM  table_1 a
            ,table_1 b) d
     ,table_1 c;



Re: GROUP BY [message #198472 is a reply to message #198441] Tue, 17 October 2006 05:06 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Thanks JRow,
I ran the following code :
SELECT item,descr,sku_stat,packed_for,
       sop_app,dfuloc,loc,
       max_allocfactor,fcst
FROM (SELECT DISTINCT s.item,i.descr,i.sku_stat,
             i.packed_for,dfu.sop_app,d.dfuloc,
             s.loc,d.allocfactor,
              MAX(d.allocfactor) OVER (PARTITION BY  
              s.item,i.descr,i.sku_stat,i.packed_for,
             dfu.sop_app,d.dfuloc,s.loc) max_allocfactor,
             COUNT(f.fcst) OVER (PARTITION BY  
             s.item,i.descr,i.sku_stat,i.packed_for,
             dfu.sop_app,d.dfuloc,s.loc,d.allocfactor) fcst
        FROM stsc.item i,stsc.sku s,
             stsc.supplymethod sm,
             stsc.dfutosku d,stsc.dmdunit du,
             stsc.fcst f,stsc.dfu dfu
       WHERE  (i.item=s.item)
         AND ((s.item=sm.item) AND (s.loc=sm.loc))
         AND ((s.item=d.item (+)) AND (s.loc=d.skuloc (+)))
         AND (s.item=du.sku(+)) AND (du.fcst=f.fcst(+))
         AND (((d.fcst=dfu.fcst(+)) AND (d.dmdgroup=dfu.dmdgroup(+)))
         AND (d.dfuloc=dfu.loc(+)))
         AND (i.inv_class='F305' OR i.inv_class='L305' OR i.inv_class='L342')
         AND (s.loc='260' OR s.loc='261' OR s.loc='262')
     ) 
WHERE allocfactor = max_allocfactor
ORDER BY item,loc


The output in the last column is coming different than required.
ITEM   DESCR  SKU_STAT  PACKED_FOR SOP_APP DFULOC LOC ALLOCFACTOR FCST
1432374	PLPOL	20X36     A	 LNT	 1  262	 0.85	 62
1432388	PLPOL	20X28	  A	 LNTC	 1  262	 1	 1
1432389	PLPOL	20X36	  A	 LNTC	 1  262	 1	 1

In the last column I should have got 1 for 1432374 and 2 for 1432388 and 1432389. There are no values more than 2 in the whole data. But here I am getting a large number like 62 for 1432374 and many others too. It seems like this is making some wrong groupings.

Regards,
Sonali

[Updated on: Tue, 17 October 2006 05:58]

Report message to a moderator

Re: GROUP BY [message #198492 is a reply to message #198472] Tue, 17 October 2006 07:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Run this and post the results:
SELECT DISTINCT s.item,i.descr,i.sku_stat,
             i.packed_for,dfu.sop_app,d.dfuloc,
             s.loc,d.allocfactor,
              MAX(d.allocfactor) OVER (PARTITION BY  
              s.item,i.descr,i.sku_stat,i.packed_for,
             dfu.sop_app,d.dfuloc,s.loc) max_allocfactor,
             COUNT(f.fcst) OVER (PARTITION BY  
             s.item,i.descr,i.sku_stat,i.packed_for,
             dfu.sop_app,d.dfuloc,s.loc,d.allocfactor) fcst
        FROM stsc.item i,stsc.sku s,
             stsc.supplymethod sm,
             stsc.dfutosku d,stsc.dmdunit du,
             stsc.fcst f,stsc.dfu dfu
       WHERE  (i.item=s.item)
         AND ((s.item=sm.item) AND (s.loc=sm.loc))
         AND ((s.item=d.item (+)) AND (s.loc=d.skuloc (+)))
         AND (s.item=du.sku(+)) AND (du.fcst=f.fcst(+))
         AND (((d.fcst=dfu.fcst(+)) AND (d.dmdgroup=dfu.dmdgroup(+)))
         AND (d.dfuloc=dfu.loc(+)))
         AND (i.inv_class='F305' OR i.inv_class='L305' OR i.inv_class='L342')
         AND (s.loc='260' OR s.loc='261' OR s.loc='262'
Re: GROUP BY [message #198544 is a reply to message #197933] Tue, 17 October 2006 09:53 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Try this

create table sku (
item number,
description varchar2(10),
sku_stat varchar2(1),
packed_for varchar2(10),
sop_app number,
default_loc number,
location number,
allocfactor number(5,2),
fsct varchar2(10))
/
with group_sku
as
(select location, max(allocfactor) alloc from sku group by location, default_loc)
select item, description, sku_stat, packed_for, sop_app, default_loc, a.location, allocfactor, count(*) from
sku a, group_sku where a.allocfactor = group_sku.alloc
group by item, description, sku_stat, packed_for, sop_app, default_loc, a.location, allocfactor


ITEM DESCRIPTIO S PACKED_FOR SOP_APP DEFAULT_LOC LOCATION ALLOCFACTOR
---------- ---------- - ---------- ---------- ----------- ---------- -----------
COUNT(*)
----------
1435416 GTFIT A LNTC 1 43687 261 0
2

1433097 BDFIB A BBB 0 4081 261 .15
1

1435416 GTFIT A LNTC 1 43687 262 1
2


Having said that there is a fundamental problem in your query when you are doing your outer join.

FROM stsc.item i,stsc.sku s,
stsc.supplymethod sm,
stsc.dfutosku d,stsc.dmdunit du,
stsc.fcst f,stsc.dfu dfu
WHERE (i.item=s.item)
AND ((s.item=sm.item) AND (s.loc=sm.loc))
AND ((s.item=d.item (+)) AND (s.loc=d.skuloc (+)))
AND (s.item=du.sku(+)) AND (du.fcst=f.fcst(+))
AND (((d.fcst=dfu.fcst(+)) AND (d.dmdgroup=dfu.dmdgroup(+)))
AND (d.dfuloc=dfu.loc(+)))



You are doing a outer join from sku to dfutosku, sku to dmdunit and later on you are referring the same table (dfutosku) to dfu, dmdunit to fsct (which will not be successful). Because once the right outerjoin is evaluated all the columns will be null in dfutosku and dmdunit. So for those records it doesn't make any sense to do a right outer join with other table.

In other words the results what you get will be incomplete.

Re: GROUP BY [message #198613 is a reply to message #198492] Wed, 18 October 2006 00:20 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Thanks JRow for looking into this. But I am still getting the previous result. That is in the last row I am getting large values like 62 whereas the data in last column is only 0,1 and 2. and I am getting 1 where I should have got 2. It similar to the last result.

Regards,
Sonali



[Updated on: Wed, 18 October 2006 00:57]

Report message to a moderator

Re: GROUP BY [message #198615 is a reply to message #198544] Wed, 18 October 2006 00:24 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Thanks Ram,
I do not have permissions to create any objects that is any table here. Actually I am converting a report into sql script. So just have to write a query to get the result without making any objects.

Regards,
Sonali
Re: GROUP BY [message #198642 is a reply to message #198613] Wed, 18 October 2006 01:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, if you'll post that data that I asked you to, then I'll take a look.
Re: GROUP BY [message #198649 is a reply to message #198642] Wed, 18 October 2006 02:15 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Sonali,

I think you are not understanding my point. Read my entire post. Forget about creating objects. I gave you the step by step process i followed. Have a look at the query which i ran. Ofcourse you have to tweak it becauase it is not a complete one.

ALSO THERE IS A FUNDAMENTAL PROBLEM IN YOUR QUERY. You have to change it otherwise using an outer join is literally waste of time.

Cheers

Re: GROUP BY [message #198650 is a reply to message #198642] Wed, 18 October 2006 02:19 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi JRow,
Below is the data I am getting :
ITEM   DESCR  SKU_STAT  PACKED_FOR SOP_APP DFULOC LOC ALLOCFACTOR FCST
1432374	PLPOL	20X36     A	 LNT	 1  262	 0.85	 62
1432388	PLPOL	20X28	  A	 LNTC	 1  262	 1	 1
1432389	PLPOL	20X36	  A	 LNTC	 1  262	 1	 1


I should have got 1,2,2 in the last column but I am getting 62,1,1.


Regards,
Sonali
Re: GROUP BY [message #198658 is a reply to message #198650] Wed, 18 October 2006 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is going to be one of those days, I can tell.

A few messages up, I asked you to post the results of this query:
SELECT DISTINCT s.item,i.descr,i.sku_stat,
             i.packed_for,dfu.sop_app,d.dfuloc,
             s.loc,d.allocfactor,
              MAX(d.allocfactor) OVER (PARTITION BY  
              s.item,i.descr,i.sku_stat,i.packed_for,
             dfu.sop_app,d.dfuloc,s.loc) max_allocfactor,
             COUNT(f.fcst) OVER (PARTITION BY  
             s.item,i.descr,i.sku_stat,i.packed_for,
             dfu.sop_app,d.dfuloc,s.loc,d.allocfactor) fcst
        FROM stsc.item i,stsc.sku s,
             stsc.supplymethod sm,
             stsc.dfutosku d,stsc.dmdunit du,
             stsc.fcst f,stsc.dfu dfu
       WHERE  (i.item=s.item)
         AND ((s.item=sm.item) AND (s.loc=sm.loc))
         AND ((s.item=d.item (+)) AND (s.loc=d.skuloc (+)))
         AND (s.item=du.sku(+)) AND (du.fcst=f.fcst(+))
         AND (((d.fcst=dfu.fcst(+)) AND (d.dmdgroup=dfu.dmdgroup(+)))
         AND (d.dfuloc=dfu.loc(+)))
         AND (i.inv_class='F305' OR i.inv_class='L305' OR i.inv_class='L342')
         AND (s.loc='260' OR s.loc='261' OR s.loc='262'


This will show what the data is before the top level Select processes it.
Re: GROUP BY [message #198906 is a reply to message #198658] Thu, 19 October 2006 01:53 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
HI JRow,
I am getting the below data :
ITEM   DESCR  SKU_STAT  PACKED_FOR SOP_APP DFULOC LOC ALLOCFACTOR FCST
1432374	PLPOL	20X36     A	    LNT	    1      262	 0.85	  62
1432388	PLPOL	20X28	  A	    LNTC    1      262	 1	  1
1432389	PLPOL	20X36	  A	    LNTC    1      262	 1	  1


from the above code only.

I have run the Distinct select statement and the above data came.

Thanks,
Sonali
Re: GROUP BY [message #198911 is a reply to message #198906] Thu, 19 October 2006 02:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That is NOT the output from the query I gave you to run.
The query I gave you to run would have had a column MAX_ALLOCFACTOR in the output.

Go and run the query I just gave you.
Re: GROUP BY [message #198935 is a reply to message #198911] Thu, 19 October 2006 04:04 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi JRow,
I run the Distinct clause and below is the data :
ITEM	DESCR SKU_STAT PACKED_FOR SOP_APP DFULOC LOC ALLOCFACTOR MAX_ALLOCFACTOR FCST
1432374	PLPOL	A	LNT      1	   43687  261 	     0.15	0.15	62
1432374	PLPOL	A	LNT      1	   43687  262	     0.85	0.85	62
1432388	ROWPL  	A	LNTC     1	   43687  262	     0  	1	1
1432388	ROWPL  	A	LNTC     1	   43687  262        1	1       1
1432389	PLXPR 	A	LNTC     1	   43687  262	     0		1       1
1432389	PLXPR 	A	LNTC     1	   43687  262	     1  	1	1


The data at the column FCST can not be more than 2 and here it is coming 62 and at other rows 51,55....

Regards,
Sonali

[Updated on: Thu, 19 October 2006 04:04]

Report message to a moderator

Re: GROUP BY [message #198962 is a reply to message #198935] Thu, 19 October 2006 06:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, going by the results you've just posted, I'd say that the error is in your Joins and /or where clauses.

I also notice that your bracketting convention is very odd - there is no need to bracket AND conditions together.

As @S.Rajaram pointed out - your outer joins look VERY suspicious. Do all those tables actually need to be outer joined together?

Run this, and it should show you that there are, in fact, 62 FCSTs for the data.
SELECT s.item,i.descr,i.sku_stat,
             i.packed_for,dfu.sop_app,d.dfuloc,
             s.loc,d.allocfactor,
             f.fcst
        FROM stsc.item i,stsc.sku s,
             stsc.supplymethod sm,
             stsc.dfutosku d,stsc.dmdunit du,
             stsc.fcst f,stsc.dfu dfu
       WHERE  (i.item=s.item)
         AND ((s.item=sm.item) AND (s.loc=sm.loc))
         AND ((s.item=d.item (+)) AND (s.loc=d.skuloc (+)))
         AND (s.item=du.sku(+)) AND (du.fcst=f.fcst(+))
         AND (((d.fcst=dfu.fcst(+)) AND (d.dmdgroup=dfu.dmdgroup(+)))
         AND (d.dfuloc=dfu.loc(+)))
         AND (i.inv_class='F305' OR i.inv_class='L305' OR i.inv_class='L342')
         AND (s.loc='260' OR s.loc='261' OR s.loc='262'


Previous Topic: Transactional Migration Scripts?
Next Topic: Passing or getting parameter to SQL script
Goto Forum:
  


Current Time: Tue Dec 06 02:52:57 CST 2016

Total time taken to generate the page: 0.09921 seconds