Hi,
I need to clear a table so that every key "Material_Period" exists just once.
Distinct does not work since other fields are different.
where not exists brought errors.
Any idea?
| Plant | % period | % Material | Cost |
| 1 | 1 | 300 | 100 |
| 2 | 1 | 300 | 120 |
| 1 | 1 | 301 | 80 |
| 1 | 2 | 300 | 110 |
| 2 | 2 | 303 | 90 |
Right now I'm doing a detour like that:
Kalk_Temp:
LOAD
PLANT
,
replace
(ltrim(replace(MATERIAL, '0', ' ')), ' ', 0)
as MATERIAL
,
makedate
(left(SPBUP,4),right(SPBUP,2))
as Datum
,
replace
(ltrim(replace(MATERIAL, '0', ' ')), ' ', 0)&'_'&SPBUP
as Key
,
// LOTSIZE,
// LOTSIZE_UNIT,
ZHK
,
ZMAT
FROM
U:\ZKALKMAT.qvd
(
qvd
)
where
SPBUP >
201003
and
VRSIO =
0
and
right(MATERIAL,6) >
315000
;
qualify
*;
unqualify
'Key';
Kalk0001:
LOAD
*
resident
Kalk_Temp
where
PLANT = 0001
;
Kalk0003:
LOAD
*
resident
Kalk_Temp
where
PLANT <>0001
;
drop
table Kalk_Temp;
unqualify
*;
Kalkfinal:
Load
*
resident
Kalk0001;
Load
*
resident
Kalk0003
where
not exists (Key)
;
Thanks for your help!
