Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1309
  • Last Modified:

iSeries query to pull records by date

I have a query I am trying to get to run in JDE.  I am trying to get a listing of all work orders which have been closed in the past 5 days.

Everything works until I try to get a date qualifier in there (WASTRX is our 'date closed' field).

Here is my query.  I'm comfortable with SQL, just not iSeries....

SELECT WADOCO
     FROM E1PRODDTA/F4801
     LEFT OUTER JOIN E1PRODDTA/F4211 ON
          STRIP(F4801.WARORN, LEADING, '0' ) = STRIP( DIGITS( F4211.SDDOCO), LEADING, '0' )
          AND F4801.WALNID = FLOOR( F4211.SDLNID )
WHERE  
     WASRST > '93'
     AND WAUORG = WASOQS
     AND WARORN<> ' '
     AND WASTRX < CURDATE() - 5 DAYS

I WASTRX is stored as a 'normal' date (mm/dd/yyy).  I am assuming that CURDATE() is in Julian format.  I'm thinking I need to format one to match the other prior to comparison, but I'm not sure how to do that.

Thanks for any help you can provide!
0
dimmergeek
Asked:
dimmergeek
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:
You say WASTRX is stored as a "normal" date, but if that were the case, I believe your query would work.

Is the data-type of WASTRX an actual DATE format or is it stored as some other type? (possibly an integer or a character type)

A simple DspFFD on that file should show you the actual data-type.

-- DaveSlash
0
 
dimmergeekAuthor Commented:
You lost me at:
     'A simple DspFFD on that file should show you the actual data-type.'

However, the field WASTRX is stored as a six-digit numeric data type.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Excellent. Can you please show me a few examples of how the data looks for that column?

Thanks,
DaveSlash
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
dimmergeekAuthor Commented:
I managed to get a DspFFD:

              Data        Field     Buffer    Buffer           Field       Column  
Field      Type       Length  Length   Position        Usage    Heading
----------------------------------------------------------------------------------------
WASTRX     ZONED        6  0       6       427        Both     WASTRX


Data from that column in a query:

select distinct(WASTRX) from e1proddta/f4801
--------------------------------------------------------------------
  WASTRX
       0
 108,003
 108,010
 108,011
 108,016
 108,017
 108,022
 108,023
 108,026
 108,028
 108,030
 108,032
 108,036
 108,037
 108,038
 108,045
 108,052
 108,053
 108,059
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
I hate Julian dates. The (incredibly small) space-savings they offer is far outweighed by the extra complexity required to use them.

Somehow, you'll have to translate that into a real date in order to compare it with CURDATE() ... probably a convoluted mathematical formula.

Give me a few minutes, and I'll see what I can come up with.

-- DaveSlash
0
 
dimmergeekAuthor Commented:
Thanks, I'm out of the office now, and don't have remote access to the servers from home.  I'll have to try any solution you propose tomorrow from my desk.
0
 
theo kouwenhovenCommented:
Your WASTRX isn't a Normal date,  if I look to your example in answer ID: 37840032
It's not a Julian date but  a JDE date (or as they call is a long-julian).

So the selection WHERE WASTRX < CURDATE() - 5 DAYS will not work because of another format.
You have to change the CURDATE() -5 into JDE format (see code):
SELECT WADOCO
     FROM E1PRODDTA/F4801
     LEFT OUTER JOIN E1PRODDTA/F4211 ON
          STRIP(F4801.WARORN, LEADING, '0' ) = STRIP( DIGITS( F4211.SDDOCO), LEADING, '0' )
          AND F4801.WALNID = FLOOR( F4211.SDLNID )
WHERE   
     WASRST > '93'
     AND WAUORG = WASOQS
     AND WARORN<> ' '
     AND WASTRX <  to_char(to_date(char(CURDATE() -5 days, ISO), 'yyyy-mm-dd'), 'yyyyddd') - 1900000    

Open in new window

Regards,
Murph
0
 
dimmergeekAuthor Commented:
Thanks!  It works beautifully.
Can you please xplain what the conversion things are doing?  I'm confused on the:

WASTRX<to_char(to_date(char(CURDATE() -5 days,ISO),
'yyyy-mm-dd'),'yyyyddd')-1900000

What is going on here?  Points will certainly be awarded, but I don't want to close the issue until I understand what is happening.

Thank you!
0
 
theo kouwenhovenCommented:
Oke Here it is

WASTRX is in JDE format  CYYDDD  (Century 0/1, Year en dau number), in SQL you have CURDATE() or CURRENT DATE that is in a the (unknown) format of your session, so we can't compare that. in the WHERE part

This are the steps

First we have to get the right date result1 = CURDATE() -5 days

Then we convert that to a known format result2=char(result1, ISO)

The format is yyyy-mm-dd, but character and we need a date to convert to daynumber result3= to_date(result2, 'yyyy-mm-dd')
Here we tell result3 that the character-input is a date in format yyyy-mm-dd

Now we have a date field that we can convert to a day number result4=to_char(result3, 'yyyyddd')
Result is a Long-julianday but we need the JDE version of a Short-julianday

For a normal short-julian date you can substring and use the last 5 characters, but JDE like to see 0 for dates in the previous century end a 1 for 2000 and later,
so we have to subtract 1900 from the year, and because our long-jul date has also day numbers 3 pos we subtract 1900000.

You can check is step by step by running a SQL on a small file (you only need one record to get output), try the following to see the results:

Select CURDATE() - 5 days from myfile
Select char(CURDATE() -5 days,ISO) from myfile
Select to_date(char(CURDATE() -5 days,ISO), 'yyyy-mm-dd') from myfile
Select to_char(to_date(char(CURDATE() -5 days,ISO), 'yyyy-mm-dd'),'yyyyddd') from myfile
Select to_char(to_date(char(CURDATE() -5 days,ISO), 'yyyy-mm-dd'),'yyyyddd')-1900000 from myfile

By running this (and play with it) you can see how it is working, it's also possible to go the other way, from JDE to USA format (the one that you called Normal date)

Have fun,
Murph
0
 
theo kouwenhovenCommented:
Oh and..... dimmergeek,

I really like it when people ask why and how and not just copy...   GREAT!! :-)
0
 
tliottaCommented:
The deal is that you don't have "dates" in WASTRX. The field is simply a numeric value that you want SQL to process as if it was a DATE. ("DATE" is upper-cased to emphasize that it's an actual database data type.) SQL knows what to do with DATE data types, but numeric data types don't have the same capabilities.

Murph's functions take a DATE value, i.e., the value returned from the CURDATE() function, and manipulate it into a form that can be compared to WASTRX.

to_char(to_date(char(CURDATE() -5 days, ISO), 'yyyy-mm-dd'), 'yyyyddd') - 1900000

Subtract 5 days from the current date and put it into a date format known as ISO. Store that result as a character value. Let's call that value "x".

to_char(to_date( x , 'yyyy-mm-dd'), 'yyyyddd') - 1900000

Take our character value, x, and turn it into a DATE with a known format. The to_date() function doesn't quite actually exist; it's an alternative syntax element for the TIMESTAMP_FORMAT() function that was recently added to SQL/400. If you had an older system, there are other functions that can be combined to give the same result. But the function really returns a TIMESTAMP value, so the next step is needed. We'll call this result "y".

to_char( y , 'yyyyddd') - 1900000

Take our TIMESTAMP, y, and turn it into a string of characters consisting of the year and day values extracted from the TIMESTAMP. Since we use three characters for the "ddd" part, the function understands the request to mean it should return the day of the year as a julian date requires. The full 4-digit year is part of the result. And since our format string doesn't include anything for the TIME portion, drop hours, minutes and seconds from the TIMESTAMP. The to_char() is like to_date() in that it's actually an alternative for the VARCHAR_FORMAT() function that's been available a little longer than the TIMESTAMP_FORMAT() function. We'll call this string of characters "z".

WASTRX <  z - 1900000

Now that we're down to the very basic string of characters for a 4-digit julian date, use subtraction to turn the first two characters (e.g., '20') into a "century digit". We're pretty sure that our string , z, has nothing but numeric digits; so the subtraction is more or less safe. Check the result to see if it's greater than WASTRX.

As noted by DaveSlash, any potential (not certain at all) space savings is very likely offset by the extra effort always needed to handle this for DATE operations. Further, there are extra precautions needed to ensure that values inserted into fields like this are valid dates.

Tom
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now