AS400 Date Query Frustration

[arniec]

I am an experienced IBM programmer starting work on the AS/400 Query/400 system, and I'm having a problem.

My company stores dates as six digit numbers in the AS400(not sure if this is normal or not), so if there is no date the field is 0. To query against dates the following is typed in the Define Result Fields:

Setup: Date field is called Mydate

@MYDATEMM substr(digits(mydate),1,2)
@MYDATEDD substr(digits(mydate),3,2)
@MYDATEYY substr(digits(mydate),5,2)
@MYDATE date(@mydatemm || '/' || @mydatedd || '/' || @mydateyy)

This works fine in other people's queries, but for some reason I am not able to emulate it. I have tried typing it verbatim many times, and I've even tried copying and pasting. When I try to use this method, however, I recieve the error message "Error retrieving record from a file".

Now I have worked around this by not using the Date() function and just querying against a string value, which has worked until now, when I need to query against Current(date).

Does anyone have any ideas why this may be happening? I know it's not the date format because in a query where this works for one field (a query I did not write) if I add this type of formatting for a second field (i.e. MyDate1) I get the same "Error retrieving record from a file" message.

Any help would be GREATLY appreciated!

AC

 

[flapeyre]

Numeric dates are generally not null (not even a zero value is null, per se), so converting it to a date type won't work. Your file has some zero or invalid dates in it.

Take off the date() function and just use them as character dates. Or you could change all the invalid dates to valid ones.


"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[arniec]

I don't have the access to the server to change all non-date values to dates, so that's not an option. Can I query as a string using the current(date) argument, or does that have to be date vs. date?

I'm trying something I've seen recently on other queries based on your advice, however. Using my above code I now use:

mydate GT 010000
@mydate eq current(date)

That SEEMS to be working so far, but I'm waiting to see if it works in the full query.

 

[flapeyre]

I think you have to compare dates only to dates.

For what it's worth, here is the IBM reference guide to Query/400:

http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/q400/rbaf9mst02.htm

"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[arniec]

Okay, I have this working to a degree.

This works:

T02.CMTCD EQ 'X007'
AND RVAPDT GT 010000
AND @RVAPDT EQ @today
AND RVSUDT EQ 0

and this works:

T02.CMTCD EQ 'X007'
AND RVSUDT GT 010000
AND @RVSUDT EQ @today

But this gives me "Error retrieving record from a file

T02.CMTCD EQ 'X007'
AND RVAPDT GT 010000
AND @RVAPDT EQ @today
AND RVSUDT EQ 0
OR T02.CMTCD EQ 'X007'
AND RVSUDT GT 010000
AND @RVSUDT EQ @today

Now @RVAPDT and @RVSUDT are formatted as shown above. @Today = current(date).

ANY clue why both parts would work alone but not when ORed?

AC

 

[flapeyre]

What kind of field is @TODAY?

Please post your work field definitions. You are using different names than you used before in your examples, so I'm a bit confused.


"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[arniec]

Sorry. I posted the definition for @Today in the line right below my query def.

@RVAPDT and @RVSUDT are both date fields (separate)and they are both created using the exact same syntax as for @MYDATE above, so replace @MYDATEMM with @RVAPDTMM and @RVSUDTMM and so forth.

The reason for this is I need records where @RVSUDT = today or RVSUDT is null and @RVAPDT = today. since the dates seem to be stored numerically here a field with no date has a value of 0. The specific syntax I use for these fields is:

@RVSUDTMM substr(digits(rvsudt),1,2)
@RVSUDTDD substr(digits(rvsudt),3,2)
@RVSUDTYY substr(digits(rvsudt),5,2)
@RVSUDT date(@rvsudtmm || '/' ||
@rvsudtdd || '/' ||
@rvsudtyy)

@RVAPDTMM substr(digits(rvapdt),1,2)
@RVAPDTDD substr(digits(rvapdt),3,2)
@RVAPDTYY substr(digits(rvapdt),5,2)
@RVAPDT date(@rvapdtmm || '/' ||
@rvapdtdd || '/' || @rvapdtyy)

@TODAY current(date)

 

[flapeyre]

You did post the def for @Today, sorry, I missed it.

The month and day have to be greater than zero or it's going to be an invalid date. But that won't catch everything, because you could still have month greater than 12, or a day of the month which is out of range for that month.


Use these conditions instead of RVAPDT GT 010000:

@RVSUDTMM RANGE '01' '12'
@RVSUDTDD RANGE '01' '31'

and hope that there's no February 30 or April 31 in the file.

Maybe you can write another query to search for these bad combinations in the dates and get the AS/400 folks to fix the data. Your options are limited because it looks like they are storing dates as numeric MMDDYY, which is the worst possible way for comparison (if there is bad or zero data, which appears to be the case).



"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci