I have to delete some records that are considered to be useless.
There is a address file and an order history file. In the company which has a consumer products, they get many product inquiries or start of sale that never becomes a sale.
Each inquiry gets a record in the address file, Customer number. In the order history file is same customer numn and a Suffix field. which is started at 000 and increments when there is new order. the bulk of the business is in fact a recurring model.
A customer who has only '000' record (there could be multiple 000's), means they never bought anything we wish to purge them from these files.
I am thinking a simple RPG program but am also interested in just using SQL if that is possible or other methods.
At this stage, we would not actually be deleting but copying the proposed records for purging to an output file which will be reviewed and also would be stored in case a need to revert.
F Addressfile IF E
F OrderHistory IF E
**** create 2 output file clones but with altered name for now.
F Zaddressfile O E
F ZorderHistory O E
*inlr doweq *off
Read Addressfile lr
*inlr ifeq *off
move *off Flg000
exsr Chk000
Flg000 ifeq *on
iter
else exsr purge
endif
endif
enddo
Chk000 begsr
**basically setll to a different logical on the orderhistory and reade for a long as we have the matching customer number and if there is a suffix not = 000 then we turn on the flag and get out.
the purge subr will have to read thru again to get the records needed to purge from the orderhistory file by using the same customer number that would still be in the read of the address file. because i would not be sure what value has the subr, for customer and i dont want to store that.
then it would write to the new file incl the address file then we can iter read the next customer in address file.
also we cannot assume that if someone did buy, they have a 001 maybe it got deleted over the years.
if we did, i could simply chain on that.
All sorts of steps you have to do in RPG. This can be done in SQL a variety of simpler ways. SQL is adept at processing and analyzing groups of records in an entire file all at once.
CREATE TABLE zaddresses AS
( SELECT *
FROM addressFile
WHERE cust IN (SELECT cust
FROM orderHistory
GROUP BY cust
HAVING max(sufix)='000'
)
)
WITH DATA
NOT LOGGED INITIALLY;
CREATE TABLE zorderHst AS
( SELECT *
FROM orderHistory
WHERE cust IN (SELECT cust
FROM zaddresses
)
)
WITH DATA
NOT LOGGED INITIALLY;
There, you've defined your holding table and populated it in one single statement each. It does have some nested logic, but nonetheless only two statements.
To purge them
DELETE FROM addressfile
WHERE cust IN (SELECT cust FROM zaddresses);
DELETE FROM orderHistory
WHERE cust IN (SELECT cust FROM zaddresses);
A grand total of four SQL statements. (I wont even ask how many you'd have in your RPG program)
Once you understand SQL, you can think about processing entire files, not just record by record instructions. It's much simpler to get things done, and it's almost always faster when done well.
(You may hear arguments about performance under particular circumstances, but most often they simply aren't using SQL as well as they should. If you write poor RPG, it performs badly too. ;-)