Retrieving Last 7 days Details in AS/400

150 pts.
Tags:
AS/400 Function Logic
AS/400 Query
MAX
I've a senario, In that i need to extract last 7 days details using query in AS/400. I tried with MAX function. In that it takes MAX(&CURNTDATE) as whole number and subtract. For example i'm subtracting 7 from today's date (1101027 - 7), i got 1101020. If i subtract 40 means i got 1100987. Can anybody advice me to how to solve this.

Answer Wiki

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

Discuss This Question: 13  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
  • TomLiotta
    ...i'm subtracting 7 from today's date (1101027 - 7) Can you clarify what query product you are using? I'd guess Query Manager from the MAX() function and the &CURNTDATE variable. But I'm not clear how the MAX() function would have a relationship to a variable value. Normally, MAX() would be over column named CURNTDATE; but that seems to be an unlikely name for a column and the ampersand ("&") needs to be gone. So, can you describe what process you're trying to create in addition to what products or features you're trying to use? Tom
    125,585 pointsBadges:
    report
  • Dsaran2007
    Select T1.EDEJDT From disemdtd/coedrep T1 Where T1.EDEJDT Between (Select (MAX(T2.EDEJDT) - 7) From disemdtd/coedrep T2) and (Select MAX(T2.EDEJDT) From disemdtd/coedrep T2) This is what i'm using in my query. I'm using V5R4M0. Is there anything you required Tom?
    150 pointsBadges:
    report
  • TomLiotta
    Okay, so this is purely a SQL query over a numeric column that you want to access as a date, though the numeric value is not in a valid SQL DATE format. If this is the type of data manipulation that you will be doing regularly, perhaps because you have no control over the data formats that you have to query, you probably need to create a UDF (user function) that returns a DATE equivalent of that type of numeric value. You could create a VIEW that does a similar conversion, but you'd have create VIEW columns for every such table and column. Are you at all comfortable with UDFs? (Are you comfortable with various conversions to DATE formats in SQL?) This can get ugly in terms of how it looks to the eye, but dates are often ugly. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Here is a rough shot at the one subquery that you're having a problem with 1. Change input data to sql data type 2. subtract 7 days 3. Return data to numeric CYYMMDD you would replace myfield with yourfield and myfile with your file.
    SELECT  
    decimal ('1' || substr(                                           
                     char (DATE('20' || SUBSTRING( max(myfield),2,2) ||  '-' ||               
                  SUBSTRING( max(myfield),4,2) ||  '-' ||                     
                  SUBSTRING( max(myfield),6,2)     )  - 7 DAYS ,iso),3,2)     
                              ||   Substr(                                                    
                    char (DATE( '20' || SUBSTRING( max(myfield),2,2) ||  '-' ||             
                   SUBSTRING( max(myfield),4,2) ||  '-' ||                    
                   SUBSTRING( max(myfield),6,2)     )  - 7 DAYS ,iso),6,2)    
                             ||   Substr(                                                    
                     char (DATE( '20' || SUBSTRING( max(myfield),2,2) ||  '-' ||             
                   SUBSTRING( max(myfield),4,2) ||  '-' ||                    
                   SUBSTRING( max(myfield),6,2)     )  - 7 DAYS ,iso),9,2)    
                           ,7,0       ) 
     from MYFILE                           
    I think Tom's got a better idea. but hay this is impressive. Phil
    49,840 pointsBadges:
    report
  • TomLiotta
    Phil's got a good start on it. You'd want to be sure that you'll never have dates before Jan 1, 2000, since that would result in a century digit of '0' instead of '1'. And means that everything that is done to put '20' at the front of your date would need to be duplicated to handle '19'. And it would need to be in a CASE structure in order to make the IF/THEN/ELSE decision. The complexities of date character manipulation is why it can be best to put it into a function that you can simply reference by name wherever you need it. If you're interested, you might start by browsing through Using user-defined functions. Tom
    125,585 pointsBadges:
    report
  • BigKat
    can you use CHAR(19 + decimal(SUBSTRING( max(myfield),1,1),1,0)) in place of the '20' in Phil's suggestion
    8,100 pointsBadges:
    report
  • BigKat
    also, since the range is max date - 7 to max date, change the where clause to increase performance.
    Select T1.EDEJDT
    From disemdtd/coedrep T1
    Where T1.EDEJDT >= (Select (MAX(T2.EDEJDT) - 7) From disemdtd/coedrep T2)
     
    8,100 pointsBadges:
    report
  • Dsaran2007
    Thanks for your advice I will try that.
    150 pointsBadges:
    report
  • TomLiotta
    ...in place of the ‘20′ in Phil’s suggestion Generally, yes. The point, though, was that both of them, '20' and '19', would need to be accounted for in every field reference if dates in the previous century were allowed. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Where T1.EDEJDT >= (Select (MAX(T2.EDEJDT) - 7) Unfortunately, that isn't guaranteed to give accurate results. The fields are simple numeric fields, not dates. When MAX(T2.EDEJDT) is 1100101, then (MAX(T2.EDEJDT) - 7) is 1100094. The latest date in 2009 is 12/31/2009 which would be 1091231. Even though that date is within the intended range because it's only one day earlier, it wouldn't satisfy the condition. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    to handle the century the hardcoded '1' at the beginning of the expression should be changed to substring(Max(Myfield),1,1). Big Kats point was that this query needs the subquery to produce the lower boundry, the monster that we're working on that computes the date -7days but it doesn't need the upper boundry because there can be no records above it. And to use only the lower boundry use >= not between. Phil
    49,840 pointsBadges:
    report
  • TomLiotta
    Max(Myfield) Ah. That makes more sense. I'm posting some code below. In case my last second indentation and cleanup caused bugs, I'll look it over after posting it. If corrections are needed, I'll try to get them in quickly. BTW, for handling dates like this, I tend to prefer DIGITS(CYYMMDD) over CHAR(CYYMMDD). The length of the result field is more predictable. Leading zeros are preserved. Century digit always appears in the same position whether it's zero or one. With CHAR(), leading zeros are trimmed. That helps make the SQL coding a little more 'portable' for different queries. Combining all of the thoughts --
    • Because MAX() is already used, the BETWEEN isn't needed. Basic ">=" is sufficient.
    • And previous century dates won't be involved mostly for the same MAX() reason.
    • There is only one file involved, so the MAX(EDEJDT) date will never be less than than a selected EDEJDT value.
    The storage of dates in numeric CYYMMDD format has been a problem ever since real DATE data types have been available even in DDS files. It's particularly a mess for SQL which isn't exactly a "programming" language outside of a stored procedure. (And even then, it's not intended to do programming work.) If a 'date' calculation needs to be done, a DATE data type must be made available. The value must be converted from NUMERIC, of whatever sub-type, to DATE in every place the value is referenced in the entire statement. (There are some exceptions.) The conversion is messy, which makes for a messy statement, especially if there are a lot of references. The multiple conversions can all be executed for every row in the result set. This can have a significant impact on performance, so limiting the number of conversions can be important for performance as well as readability. Without a UDF() to simplify the statement and to speed processing by having an optimized HLL module doing the work, I would try a CTE (Common Table Expression) to provide the comparison values. The MAX() function should let us create a single-row CTE. The CTE result set can be directly joined to the base table without serious slowdowns or complexities. The original attempt was for a BETWEEN test. It might look this way:
    with cteDT (BeginDt, EndDt) as (
       select
           ( date('20' concat substr(digits(max(EDEJDT)),2,2) concat '-'
                  concat substr(digits(max(EDEJDT)),4,2) concat '-'
                  concat substr(digits(max(EDEJDT)),6,2)) - 7 days )
           as BeginDt,
           ( date('20' concat substr(digits(max(EDEJDT)),2,2) concat '-'
                concat substr(digits(max(EDEJDT)),4,2) concat '-'
                concat substr(digits(max(EDEJDT)),6,2)) )
           as EndDate
       from disemdtd/coedrep )
    select EDEJDT from disemdtd/coedrep, cteDT
       where date('20' concat substr(digits(EDEJDT),2,2) concat '-'
                  concat substr(digits(EDEJDT),4,2) concat '-'
                  concat substr(digits(EDEJDT),6,2)) between BeginDt and
                                                             EndDt
    The proposed ">=" version is simpler. It could look this way:
    with cteDT (BeginDt) as (
       select
           ( date('20' concat substr(digits(max(EDEJDT)),2,2) concat '-'
                concat substr(digits(max(EDEJDT)),4,2) concat '-'
                concat substr(digits(max(EDEJDT)),6,2)) - 7 days )
           as BeginDt,
       from disemdtd/coedrep )
    select EDEJDT from disemdtd/coedrep, cteDT
       where date('20' concat substr(digits(EDEJDT),2,2) concat '-'
                  concat substr(digits(EDEJDT),4,2) concat '-'
                  concat substr(digits(EDEJDT),6,2)) >= BeginDt
    The SQL should run under OPTION DATFMT=*ISO unless the conversion code is changed to match a different date format. The above works on V5R3 for a table with a CYYMMDD numeric column containing valid 'date' values. If "special" values such as all zeros or actual null values have been used, additional code will need to be added. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    The biggest problem with the posted code is that the editor changed most apostrophes to leading- and trailing-quote marks. It'd be nice if that didn't happen in a code block where the result should be exactly what was put in. With all apostrophes back in place, the code pastes into and runs in STRSQL on V5R3. Tom
    125,585 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