Subratct 90 days from the System Date

785 pts.
Tags:
RPG/400 program-defined disk output file


Hi friends, it´s me again.

I need to reorganize a file called payments to a file called paymentsH (H means Historical)

On the file payments I have 11 million records since 2005 and I need only the last 90 days, but I need thats information on the historical file.

The field PAYDATE was configurated below

PAYMENT A          R IPAYDATE   A            PAYDATE          8S         COLHDG('PAYMENT DATE')  A          K PAYBOX

PAYMENTH A          R IPAYDATEH   A            PAYDATE          8S         COLHDG('PAYMENT DATE')  A          K PAYBOX

How can I subtract 90 days from the current date and select only that record to record on PAYMENTH ?

*---------------------------* My first RPG program no ILE, I don´t know if it´s correct

FPAYMENT  UP  E                    DISK    FPAYMENTH O   E                    DISK     *                                        C           PAYDATE   IFLT DTSYS      (Where I configure only 90 days to record)       C                     WRITEIPAYDATEH         C                     DELETIPAYDATE         C                     ENDIF              


Answer Wiki

Thanks. We'll let you know when a new response is added.

<pre>
Sorry, the format of PAYDATE is yyyymmdd

Position to line .
Line ….+….1…
PAYMENT DATE
000001 20,081,020
000002 20,081,020
000003 20,081,020
000004 20,081,020
000005 20,081,020
000006 20,081,020
000007 20,081,020
000008 20,081,020
000009 20,081,020
000010 20,081,020
000011 20,081,020
000012 20,081,020
000013 20,081,020
000014 20,081,020
000015 20,081,020
000016 20,081,020
000017 20,081,020
000018 20,081,020
000019 20,081,020
</pre>
<pre>
I need only the records less than 90 days based on SystemDate and
</pre>.

Discuss This Question: 27  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    RPG not RPGLE -- but the first issues would exist under any circumstances Ok, today is 20120117, unless you are somewhere where it isn't. 1. is that the format of PAYDATE CCYYMMDD ? 2. When you say 90 days do you really mean 3 months or precisely 90 days correcting for month length and leap year Could it just be the first of a month. (hint first of the month will be simple math). if we run the purge today would your accept 20111001 -- Nov 1 2011? Phil
    49,550 pointsBadges:
    report
  • CharlieBrowne
    Do you have to do this with RPG? SQL would be faster. Just so I understand Input file is Payments and has 11 million records You want to move all records from that file where the date is greater that 90 days from the current date to a file call PAYMENTSH. Once completed the combined record count will be 11 million records. Correct? The date in Payments is defined as 8S. Is it mmddyyyy? A source member with SQL statements 'INSERT into PAYWMENTS..... Then DELETE from PAYMENTS .... can be setup. Then you just need to do a single RUNSQLSTM. LMK if this is correct and the date format an some SQL statements can be provided to you.
    41,370 pointsBadges:
    report
  • TomLiotta
    All questions asked so far are valid. And I'd agree that this would probably be better done with pure SQL than with RPG, no matter if it's ILE or not. This is straightforward data manipulation with no program logic needed. The structure of the "date" field in PAYMENTS must be the first thing that is needed. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    So 1 PayDate is CCYYMMDD, great. 2 since the group is going to recommend SQL any date is easily computed, but when spec's say 90 days they rarely mean 90 days, they often mean 3 months or the end or beginning of a month. Your previous response on this appears to be cut off. Phil
    49,550 pointsBadges:
    report
  • PutzGrilla
    Phil, sorry the format of PAYDATE is yyyymmdd  (20081020).
    
    785 pointsBadges:
    report
  • philpl1jb
    Yes century year month day, you are right, I should have said YYYYMMDD 20120117 but what of the 90 days 2 since the group is going to recommend SQL any date is easily computed, but when spec’s say 90 days they rarely mean 90 days, they often mean 3 months or the end or beginning of a month. Your previous response on this appears to be cut off.
    49,550 pointsBadges:
    report
  • philpl1jb
    And the field structure of the two files are the same.
    49,550 pointsBadges:
    report
  • PutzGrilla
    Ok friends, today is 20120117 correct ?
    I need on file PAYMENT only these records maybe need convert this date to julian date first :
    
    785 pointsBadges:
    report
  • PutzGrilla
    Phil, Yes are the same structure.
    
    785 pointsBadges:
    report
  • philpl1jb
    There is probably an easier way SQL is Insert into PAYMENTH Select * from PAYMENT where date(substr(digits(paydate),1,4) concat '-' concat substr(digits(payment),5,2) concat '-' concat substr(digits(payment),7,2)) < (curdate() - 90 days)
    49,550 pointsBadges:
    report
  • PutzGrilla
    OK, but I need to clean on PAYMENT these records copied ON PAYMENTH
    
    785 pointsBadges:
    report
  • TomLiotta
    where date(substr(digits(paydate),1,4) concat ‘-’ concat substr(digits(payment),5,2) concat ‘-’ concat substr(digits(payment),7,2)) < (curdate() - 90 days) Since {(curdate() - 90 days)} will essentially be constant during the execution and since {paydate} is in a useful YYYYMMDD form, consider reversing the WHERE clause idea. That is, consider simply converting the digits from {(curdate() - 90 days)} to a numeric value. Then make a direct comparison to {paydate}. I suspect that the optimizer might than only execute a single conversion. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    I need to clean on PAYMENT these records copied ON PAYMENTH The thought from CharlieBrowne was that there will eventually be two SQL statements. The first one will be the INSERT statement that Phil is helping you with. Once that statement is decided for certain, it will have an embedded SELECT that has a WHERE clause. The statement that Phil has already shown will probably work. I suggest that some testing be done first in order to understand performance and other possible implications. The second SQL statement will be a DELETE statement. It will delete from PAYMENTS, and it will have the same WHERE clause as is used for the SELECT. The INSERT runs first. The selected rows get inserted into PAYMENTSH. Then the DELETE will run, and it will delete the same set of rows from PAYMENTS that was inserted into PAYMENTSH. For a test, I would try something like {(curdate() - 2191 days)} first. That should only select rows that are old enough to go almost all the way back into 2005 (if I multiplied correctly). With a smaller set of rows, you might verify that the right rows are being inserted before running over the full set. You would also get an idea of how long the process might run over the full set. You can clear those rows out of PAYMENTSH before running the final SQL. Once tests are finished, you can change {(curdate() - 2191 days)} to be {(curdate() - 90 days)}. At that time, you would add the DELETE statement with the final form of the WHERE clause. When the two statements run together, rows will be inserted into PAYMENTSH; and the same set of rows will be removed from PAYMENTS. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Excellent as always Tom. Reversing the conversion might speed up the process, although, the record insertion and deletion would be about 99% of the work. Make sure that there is a backup of the file before you begin!
    49,550 pointsBadges:
    report
  • PutzGrilla
    Friends, now it´s works.
    
    1º
    Select count(*) from PAYMENT (11.922.568 records).
    2º
    Insert into PAYMENTH
    Select * from PAYMENT
    where date(substr(digits(paydate),1,4) concat ‘-’ concat
                          substr(digits(paydate),5,2) concat ‘-’ concat
                          substr(digits(paydate),7,2)) < (curdate() - 90 days) (11.846.288 records).
    3º
    delete from PAYMENT
    where date(substr(digits(paydate),1,4) concat ‘-’ concat
                          substr(digits(paydate),5,2) concat ‘-’ concat
                          substr(digits(paydate),7,2)) < (curdate() - 90 days) (11.846.288 records).
    4º
    select * from PAYMENT (76.280 records).
    
    Now on my historical file have 11.846.288 records when I need to use.
    
    Thanks for all again.
    PutzGrilla
    
    785 pointsBadges:
    report
  • TomLiotta
    Friends, now it´s works. Does PAYMENTS need to be reorganized now? If it had 11 million records deleted, it might need it. Tom
    125,585 pointsBadges:
    report
  • PutzGrilla
    Yes Tom thanks, the command RGZPFM was executed on the file PAYMENT.
    
    PutzGrilla.
    785 pointsBadges:
    report
  • RBDSILVA
    Hi friends, I am using from an AS/400 the date values are stored as a 7 digit numeric. Here is the format: "CYYMMDD" C is a "century digit" where 0 = 1900 and 1 = 2000.
    How can I subtract 90 days from the System Date too ?
    
    Thanks,
    RBDSILVA
    
    550 pointsBadges:
    report
  • philpl1jb
    You didn't specify the environment that you needed the 7 digit date in This RPGLE code populates a 7 digit numeric with date + 90 days. MyDate7 = %dec(%char(%date() + %days(90) : *cymd0) : 7 : 0)
    49,550 pointsBadges:
    report
  • philpl1jb
    add - subtract what's a few days between friends? MyDate7 = %dec(%char(%date() - %days(90) : *cymd0) : 7 : 0)
    49,550 pointsBadges:
    report
  • PutzGrilla
    Hi Phil, sorry ...do You have a SQL/400 example ?
    
    I was thinking something like
    
    select substring(rcpdat,4,2) CONCAT '-' CONCAT        
               substring(rcpdat,6,2) CONCAT '-' CONCAT        
     (cast(substring(rcpdat,1,3) as integer) + 1900) as   
     date from lib/file < (CURDATE() - 90 DAYS)
    
    But... not works.
    
    RBDSILVA
    
    785 pointsBadges:
    report
  • RBDSILVA
    Thanks, Putzgrilla, no works to me too.
    
    550 pointsBadges:
    report
  • philpl1jb
    This code should be good till 2039 to convert current date - 90 days into a 7 digit number. Please post another message in 2039. Phil
    select dec('1' || substring( char(date(curdate() -90 days)),7,2)     
                             || substring( char(date(curdate() -90 days)),4,2)     
                             || substring( char(date(curdate() -90 days)),1,2) )   
    from sysibm/sysdummy1                 
    
    49,550 pointsBadges:
    report
  • RBDSILVA
    Hi friends, I have the solution...
    
    INSERT INTO BACKUPLIB/FILEX
    SELECT * FROM LIB/FILEX WHERE
    date((cast(substring(field,1,3) as integer) + 1900)
                                            CONCAT '-' CONCAT
         substring(field,4,2) CONCAT '-' CONCAT
         substring(field,6,2)) < (CURDATE() - 90 DAYS);
    delete from LIB/FILEX WHERE
    date((cast(substring(field,1,3) as integer) + 1900)
                                            CONCAT '-' CONCAT
         substring(field,4,2) CONCAT '-' CONCAT
         substring(field,6,2)) < (CURDATE() - 90 DAYS);
    
    Thanks for all.
    
    550 pointsBadges:
    report
  • philpl1jb
    Works but as Tom pointed out, I think this is going to be way more efficient. INSERT INTO BACKUPLIB/FILEX SELECT * FROM LIB/FILEX WHERE field < dec(’1′ || substring( char(date(curdate() -90 days)),7,2) || substring( char(date(curdate() -90 days)),4,2) || substring( char(date(curdate() -90 days)),1,2) );
    49,550 pointsBadges:
    report
  • RBDSILVA
    Yes Phil, You are correct, is more efficient and more fast too.
    
    I´m changing now.
    
    Thanks,
    Roberto
    
    550 pointsBadges:
    report
  • philpl1jb
    Cool, did you determine that it's actually running faster? We thought it would be a bit faster if the SQL engine recognized that it can use the first extract of the current date for all rows. Phil
    49,550 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following