sql ibm-midrange excel-2016 db2-400 microsoft-query

Microsoft Query displaying data correctly but won't return data to Excel- Error SQ20448

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.

Solution

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.