Solved

iSeries query to pull records by date

Posted on 2012-04-12
11
1,247 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
ID: 37839968
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
ID: 37839990
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
ID: 37840002
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
ID: 37840032
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
ID: 37840225
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 7

Author Comment

by:dimmergeek
ID: 37840485
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
ID: 37841962
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
ID: 37842292
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
ID: 37844394
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
ID: 37844404
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
ID: 37844586
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

28 Experts available now in Live!

Get 1:1 Help Now