0
SELECT
 ASTRO_VIEW_CNT_O08.pickdate, -- picking day;
 ASTRO_VIEW_CNT_O08.linestat, -- picking line status;
 ASTRO_VIEW_CNT_O08.partno, -- product name
 ASTRO_VIEW_CNT_O08.reqquant, -- orderd quantity
 ASTRO_VIEW_CNT_O08.delquant,-- delivered quantity
 ASTRO_VIEW_CNT_O08.ordno,--- order number
 L16T3.fmha -- from material handling area

FROM ASTRO_VIEW_CNT_O08  - VIEW TABLE FROM ORDERS

LEFT JOIN L16T3 ON ASTRO_VIEW_CNT_O08.shorto08=L16T3.shorto08  --- L16T3 is Logg table with history data
 
WHERE linestat IN (0,7,25) AND delquant=0  
ORDER BY reqquant DESC

I get double results. For example:

<07.03.2022,    25, 31012 ,                     640 0,  SH1, 777011;>
<07.03.2022,    25, 31012 ,                     640 0,      , 777011;>
`07.03.2022,    25, 31012 ,                     640 0,  DP14, 777011;`
`07.03.2022,    25, 31012 ,                     640 0,  SH1OT,777011;`
`07.03.2022,    25, 31012,                      640 0,  UT121,777011;`

I want to take only one row from order number (777011).

Andrijana K
  • 1
  • 2
  • Why do you have a `GROUP BY` when you have no aggregation? – Thom A Apr 05 '22 at 11:17
  • I'm trying to modify my question. It sholud be without GROUP BY part. I get those posted results. Tried to put DISTICNT ASTRO_VIEW_CNT_O08.ordno but I still get several rows for one order. – Andrijana K Apr 05 '22 at 11:26

1 Answers1

0

For a same material handling area there are different ASTRO_VIEW_CNT_O08.ordno due to which you are getting duplicate/more number of records. Try removing the ASTRO_VIEW_CNT_O08.ordno in the select.

Or you can try as below.

;with FinalOutput as 
(SELECT
 ASTRO_VIEW_CNT_O08.pickdate, -- picking day;
 ASTRO_VIEW_CNT_O08.linestat, -- picking line;
 ASTRO_VIEW_CNT_O08.partno, -- product name
 ASTRO_VIEW_CNT_O08.reqquant, -- orderd quantity
 ASTRO_VIEW_CNT_O08.delquant,-- delivered quantity
 ASTRO_VIEW_CNT_O08.ordno,--- order number
 L16T3.fmha, -- from material handling area,
 row_number() over (partition by L16T3.fmha order by L16T3.fmha)   as rownum

FROM ASTRO_VIEW_CNT_O08  - VIEW TABLE FROM ORDERS

LEFT JOIN L16T3 ON ASTRO_VIEW_CNT_O08.shorto08=L16T3.shorto08  --- L16T3 is Logg table with history data
 
WHERE linestat IN (0,7,25) AND delquant=0 
) 
select * from FinalOutput
where rownum=1
ORDER BY reqquant DESC
Srishuk Kumar Bhagwat
  • 130
  • 4
  • Then I don't get good results. I need to have Order number because I want to see where mistake was maden (mha) and on witch order. Status line 9 tells me it not picked. – Andrijana K Apr 05 '22 at 11:44