asked on

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!

Member_2_2484401

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

dimmergeek

ASKER

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.

Member_2_2484401

Excellent. Can you please show me a few examples of how the data looks for that column?

Thanks,
DaveSlash

dimmergeek

ASKER

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

Member_2_2484401

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

dimmergeek

ASKER

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.

ASKER CERTIFIED SOLUTION

Theo Kouwenhoven

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dimmergeek

ASKER

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!

Theo Kouwenhoven

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

Theo Kouwenhoven

Oh and..... dimmergeek,

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

Member_2_276102

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