DB2 SQL delete to purge Header/Detail records
I have a Header file with Detail records that belong to it. I need to copy the detail data off to a backup file, delete the detail records where the Header record entered date is greater than xx days from the current date, copy the header data off to a backup file and finally delete the header record.
I came up with this:
BEGIN
SET @days = 30;
SET nDate = integer(
substring(char(date(now()),ISO),1,4) ||
substring(char(date(now()),ISO),6,2) ||
substring(char(date(now()),ISO),9,2) );
SET NewDate = (nDate - @days);
INSERT INTO EKDBK -- new backup file
(
BDDATE
, BDWHS
, BDDEPT
, BDVNDR
, BDJOB
, BDDTS
)
SELECT
KDDATE
, KDWHS
, KDDEPT
, KDVNDR
, KDITEM
FROM EKDL01 d2 -- detail file
WHERE EXISTS
(
SELECT
h2.KHWHS
, h2.KHDEPT
, h2.KHVNDR
, h2.KHDATE
FROM
EKH h2 -- Header file
WHERE h2.KHDATE <= NewDate
AND h2.KHWHS = d2.KDWHS
AND h2.KHDEPT = d2.KDDEPT
AND h2.KHVNDR = d2.KDVNDR
)
;
Software/Hardware used:
Software/Hardware used:
ASKED:
November 1, 2004 1:16 PM
UPDATED:
November 15, 2004 3:30 PM
Answer Wiki:
Recommend you use TYPE date instead of TYPE integer for nDate. In your SQL, a current date of 15 Nov 2004 gives:
20041115 - 30 = 20041085 (not a valid date)
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
