Solved

iSeries query to pull records by date

Posted on 2012-04-12
11
1,235 Views
Last Modified: 2012-04-13
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
Comment
Question by:dimmergeek
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 18

Expert Comment

by:daveslash
Comment Utility
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
 
LVL 7

Author Comment

by:dimmergeek
Comment Utility
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
 
LVL 18

Expert Comment

by:daveslash
Comment Utility
Excellent. Can you please show me a few examples of how the data looks for that column?

Thanks,
DaveSlash
0
 
LVL 7

Author Comment

by:dimmergeek
Comment Utility
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
 
LVL 18

Expert Comment

by:daveslash
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Author Comment

by:dimmergeek
Comment Utility
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
 
LVL 16

Accepted Solution

by:
theo kouwenhoven earned 200 total points
Comment Utility
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
 
LVL 7

Author Comment

by:dimmergeek
Comment Utility
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
 
LVL 16

Expert Comment

by:theo kouwenhoven
Comment Utility
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
 
LVL 16

Expert Comment

by:theo kouwenhoven
Comment Utility
Oh and..... dimmergeek,

I really like it when people ask why and how and not just copy...   GREAT!! :-)
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now