What I want is to have one record each from the above duplicate entries - as you can see there are two entries for a single date - I want to have only the first entry for any date as my output.
I have done a search on this thinking this to be sort of a common query that I am asking, but what I got is only by doing it by sort. I need to do this by SQL in my stored proc.
I am looking for the best possible way to do this.
I am posting the cursor below. Only thing is that it is defined inside a Stored Proc.
Code:
This is how the cursor is defined :
---------------------------------------
SELECT ms.loc, ms.item, cs.res,
TRUNC(ms.start_dt, 'D') as sun_start_dt,
SUM(ms.quantity) as sum_quantity
FROM mstr_schd ms, prodmt c, prodmtstep cs
WHERE ms.item = c.item
AND c.item = cs.item
AND ((ms.loc = c.loc) OR (ms.loc||'M' = c.loc))
AND c.loc = cs.loc
AND c.cppprodmethod = cs.cppprodmethod
AND stepnum = 1
AND c.priority in (SELECT MIN(priority)
FROM prodmt cp
WHERE c.item = cp.item
AND c.loc = cp.loc)
GROUP BY ms.item, ms.loc, cs.res, TRUNC(start_dt, 'D')
ORDER BY ms.item, ms.loc, sun_start_dt;
Let me give you the example with the data now (I handpicked this example and have edited the values for obvious reasons) -
Output now returned by the cursor for the combo of a particular item and loc -
------------------------------------------------------------------------------------
SELECT ms.loc, ms.item, cs.res,
TRUNC(ms.start_dt, 'D') as sun_start_dt,
SUM(ms.quantity) as sum_quantity
FROM mstr_schd ms, prodmt c, prodmtstep cs
WHERE ms.item = c.item
AND c.item = cs.item
AND c.item = '11111111111111' AND c.loc = '222222M'
AND ((ms.loc = c.loc) OR (ms.loc||'M' = c.loc))
AND c.loc = cs.loc
AND c.cppprodmethod = cs.cppprodmethod
AND stepnum = 1
AND c.priority in (SELECT MIN(priority)
FROM prodmt cp
WHERE c.item = cp.item
AND c.loc = cp.loc)
GROUP BY ms.item, ms.loc, cs.res, TRUNC(start_dt, 'D')
ORDER BY ms.item, ms.loc, sun_start_dt;
LOC ITEM RES SUN_START_DT SUM_QUANTITY
222222 11111111111111 A 2006-08-20-00:00 19125
222222 11111111111111 B 2006-08-20-00:00 38250
222222 11111111111111 A 2006-08-27-00:00 14943
222222 11111111111111 B 2006-08-27-00:00 29886
Desired output I am looking for:
-------------------------------------
LOC ITEM RES SUN_START_DT SUM_QUANTITY
222222 11111111111111 A 2006-08-20-00:00 19125
222222 11111111111111 A 2006-08-27-00:00 14943
This has been fixed. We have used ROW_NUMBER() . Like as below -
Code:
select loc, item, res, sun_start_dt, sum_quantity from
(
select t.*,
row_number() over (partition by loc, item, sun_start_dt order by res) rn
from
(
SELECT ms.loc, ms.item, cs.res,TRUNC(ms.start_dt, 'D') as sun_start_dt,
SUM(ms.quantity) as sum_quantity
FROM speed_mstr_schd ms, prodmt c, prodmtstep cs
WHERE ms.item = c.item
AND c.item = cs.item
AND c.item = '11111111111111' AND c.loc = '222222M'
AND ((ms.loc = c.loc) OR (ms.loc||'M' = c.loc))
AND c.loc = cs.loc
AND c.cppprodmethod = cs.cppprodmethod
AND stepnum = 1
AND c.priority in
(
SELECT MIN(priority)
FROM prodmt cp
WHERE c.item = cp.item
AND c.loc = cp.loc
)
GROUP BY ms.item, ms.loc, cs.res, TRUNC(start_dt, 'D')
) t
)
where rn = 1
ORDER BY item, loc, sun_start_dt;
Thanks to all for sparing their time in going through this post and trying to help me out. _________________ Thanks
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
