Good morning,
I have a semi-functioning SQL query to pull data from the IBM AS/400 into Microsoft Excel. The data displays correctly inside Microsoft Query, but when I click on "Return Data" to return the data to Excel, I receive the following error message:
[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQ20448 - Expression not
valid using format string specified for TIMESTAMP_FORMAT.
Essentially, my code is joining a few files to provide me with item information and uses dates to remove duplicates so that I can get the most recent transactions.
I use practically an identical code for other files that functions correctly, so I suspect there may be an incorrectly formatted date in my original data, which would cause an error when converting the IBM date to the Microsoft-compatible date with the TO_DATE function. I know that my date conversion works correctly, given the IBM date is an actual date.
My question is, how could I code in an exception to either ignore incorrectly formatted data, or how could I return data that is formatted incorrectly so that I could write an exception in my code?
Here is my code (hopefully the comments are helpful):
SELECT xh.ITNBR, yh.VNDNR, zh.VN35VM, yh.BUYNO, xh.Create_Date -- Item #, Vendor #, Vendor Name, Buyer, Create_Date
FROM
(SELECT PO_IH.ITNBR, -- Item #
max(TO_DATE((CONCAT(
CONCAT(
(CONCAT(SUBSTRING(PO_MH.ACTDT,4,2), '/')),
(CONCAT(SUBSTRING(PO_MH.ACTDT,6,2), '/'))),
SUBSTRING(PO_MH.ACTDT,2,2))), 'MM/DD/YY')) as Create_Date -- Converts IBM date format to work with Microsoft Query
FROM POHISTI as PO_IH, POHSTM as PO_MH
WHERE PO_IH.ORDNO = PO_MH.ORDNO AND
(TO_DATE((CONCAT(
CONCAT(
(CONCAT(SUBSTRING(PO_MH.ACTDT,4,2), '/')),
(CONCAT(SUBSTRING(PO_MH.ACTDT,6,2), '/'))),
SUBSTRING(PO_MH.ACTDT,2,2))), 'MM/DD/YY')) =
(SELECT MIN((TO_DATE((CONCAT(
CONCAT(
(CONCAT(SUBSTRING(PO_MH.ACTDT,4,2), '/')),
(CONCAT(SUBSTRING(PO_MH.ACTDT,6,2), '/'))),
SUBSTRING(PO_MH.ACTDT,2,2))), 'MM/DD/YY'))) -- All of this chaos basically removes duplicate information and converts IBM date
FROM POHSTM as PO_MH2
WHERE PO_MH.ORDNO = PO_MH2.ORDNO AND
PO_MH.ACTDT NOT LIKE '0%' AND -- Removes dates that start with 0 (i.e. IBM's way of saying "no date")
PO_MH.ACTDT NOT LIKE '9%') -- Removes dates from 20th century
GROUP BY PO_IH.ITNBR) xh
LEFT JOIN
(SELECT PO_IH2.ITNBR, -- Item #
PO_IH2.BUYNO, -- Buyer
PO_IH2.VNDNR, -- Vendor
(TO_DATE((CONCAT(
CONCAT(
(CONCAT(SUBSTRING(PO_MH2.ACTDT,4,2), '/')),
(CONCAT(SUBSTRING(PO_MH2.ACTDT,6,2), '/'))),
SUBSTRING(PO_MH2.ACTDT,2,2))), 'MM/DD/YY')) as Create_Date
FROM POHISTI as PO_IH2, POHSTM as PO_MH2
WHERE PO_IH2.ORDNO = PO_MH2.ORDNO AND
PO_MH2.ACTDT NOT LIKE '0%' AND -- Removes dates that start with 0 (i.e. IBM's way of saying "no date")
PO_MH2.ACTDT NOT LIKE '9%') yh -- Removes dates from 20th century
ON xh.ITNBR = yh.ITNBR AND xh.Create_Date = yh.Create_Date
LEFT JOIN VENNAML0 zh -- Vendor Name
ON yh.VNDNR = zh.VNDRVM
Here is the output in Microsoft Query:
ITNBR VNDNR VN35VM BUYNO CREATE_DATE
A-FUL 76 HOLLAND COMP SUSY 2016-12-06 00:00:00.000000
A-MINI 76 HOLLAND COMP SUSY 2016-11-28 00:00:00.000000
A-SHIMBOX 76 HOLLAND COMP SUSY 2014-10-16 00:00:00.000000
A-001 76 HOLLAND COMP SUSY 2016-12-19 00:00:00.000000
A-002 76 HOLLAND COMP SUSY 2016-12-19 00:00:00.000000
....
Like I said, the information displays perfectly in Microsoft Query but when I return it to Excel I get the above error. I tried using the above "NOT LIKE" statements to deal with the two most common errors, but I'm at a loss as to how to find other errors.
I don't really care if I get bad data, as long as it dumps into Excel. At that point I can correct it. But I suspect that if Microsoft Query can't convert a date, it won't return the data to Excel.
Thanks.
You have a numeric field that is formatted like CYYMMDD. This is a common date format in the IBM i world where C is a century code (0=>19, 1=>20, 2=>21, ..., 9=>28). This came about because most decimal dates were stored in packed decimal format with 2 digit years before the Y2K remediation. Packed decimal always has room for an odd number of digits due to the configuration of the format, but most dates were defined as (6,0) (length,decimal places). This left room on disk for one extra digit to the left of the date, and folks could define the dates as (7,0) without changing the format of the data in the records. thus the 7 digit date was born as a result of Y2K. Synon was the first company I am aware of that did this in their 2E code generator. It was quite popular, and the format is everywhere. It even found its way into the IBM operating system.
So when SQL casts that to CHAR a date like 0951107 will look like 951107, and when SQL casts 1171107 to CHAR, it will look like 1171107. Unfortunately, NOT LIKE '9%' will be unreliable at some point, because that leading 9 could be the first non-zero digit of a 1990 era date, or it could be a 2800 era date. Even worse, 1900's dates chast to a CHAR could start with anything from 1-9. For example a date of 1985/12/05 would look like 0851205 in CYYMMDD digit format. That would be cast to 851205. So when dealing with dates you need to use DIGITS to cast the number to a CHAR so you don't loose the leading 0 character. And you need to test your date field for 0 which is literally 0000000 (not 00/00/00 even though that is what it looks like when it is formatted with EDITC(Y)).
Here is an example of what is happening:
create table datetest
(decdt decimal(7,0));
insert into datetest
values (0), (941107), (1170304), (1000101);
select substr(decdt,4,2) || '/' ||
substr(decdt,6,2) || '/' ||
substr(decdt,2,2),
decdt
from datetest;
results in:
/ / 0
10/7 /41 941,107
03/04/17 1,170,304
01/01/00 1,000,101
I bet your procedure TO_DATE() is not handling invalid dates properly as they are almost certainly being passed. If you use digits in the substr function, you will get something more sane.
select substr(digits(decdt),4,2) || '/' ||
substr(digits(decdt),6,2) || '/' ||
substr(digits(decdt),2,2),
decdt
from datetest
results in:
00/00/00 0
11/07/94 941,107
03/04/17 1,170,304
01/01/00 1,000,101
Notice that the month day and year parts are all in the correct place now, and all you have to code for is the 0 date which means no date. In any case the function to_date() needs to detect an invalid date and either ignore it, or set it to something usable like 0001-01-01 or null.