Where’d the Data Go?

The Reported Problem

A few weeks ago I was contacted by a former employer because an SSRS report I had written several years ago was no longer returning the data that was expected.  The report returned Manufacturing Instructions that were attached to an Order or Order Item and was used to ensure that everything was done as requested by the customer.  There are 2 groups on the report:

1. Runs – a Run consists of orders that contain the same product.
2. Orders

The detail section consists of the specific Manufacturing Instructions for each order in the run.  Well, the report was showing the Runs and the Orders but no Instructions.  Obviously this is bad since the whole point of the report is to show the instructions.

Troubleshooting the Problem

I vpn’ed into the network and opened up the report to find the SQL that was generating the report.  I knew I had authored the report, but could not remember what the data source was.  There were 2 possible data sources:  SQL Server or DB2 on the iSeries.  I was hoping it would be SQL Server.  It wasn’t.  I had a query similar to this (underlining added) in the report that was connecting to DB2 using the IBM DA400 OLE DB Driver:

SELECT
    S.run_no,
    O.order_no,
    OI.instruction AS order_instruction,
    S.start_date,
    S.end_date
FROM
    @orders O JOIN 
    @order_instructions OI
        ON O.order_no = OI.order_no JOIN
    @schedule S
        ON O.order_no = S.order_no
WHERE
    (S.start_date >= '12/17/2009' AND
     S.end_date < '12/21/2009') OR
    (S.start_date < '12/21/2009' AND
     S.end_date > '12/14/2009')
UNION ALL
SELECT
    S.run_no,
    O.order_no,
    OII.instruction AS order_item_instruction,
    S.start_date,
    S.end_date
FROM
    @orders O JOIN 
    @order_items OI
        ON O.order_no = OI.order_no JOIN 
    @order_item_instructions OII
        ON OI.order_item_no = OII.order_item_no JOIN
    @schedule S
        ON O.order_no = S.order_no
WHERE
    (S.start_date >= '12/17/2009' AND
     S.end_date < '12/21/2009') OR
    (S.start_date < '12/21/2009' AND
     S.end_date > '12/14/2009')
ORDER BY
    run_no,
    O.order_no 

I ran the query in the dataset designer in Visual Studio 2003 (yup, SSRS 2000) and got data back including instructions.  I couldn’t figure out why I wasn’t seeing instructions on the report, especially since this report had been running without problem.  Then I ran the query in a custom query tool and that’s when I noticed the instruction column was not being returned as instruction but as column 00003!  What! I don’t have that anywhere?  Well, look at the underlined text in query, notice how I aliased the instruction columns with different aliases.  Turns out THIS was the problem.  Apparently when an update (ptf) was applied to the iSeries the behavior of column aliases in a UNION changed.  If you run a query like that in SQL Server the ALIAS used in the FIRST query is returned as the column name.  This was also how it USED to work in DB2 on the iSeries, but apparently that changed and instead of getting EITHER of the ALIASES it returns the column number, in the case you’d get 00003 for the ALIASED column.

The Solution

Well, simple solution, I removed the ALIAS in the second part of the UNION query and, VIOLA!, the report now worked again.

Why did I have the separate ALIASes in the first place?  I think it was because when I was testing the queries for correct data I wanted to know which instructions were at the order level and which were at the order item level and I ran the queries separately.  Then when I was convinced the data was correct I UNION’ed the queries and stuck them in the report without removing the ALIASes. 

So in reality the query was returning the correct data, but the SSRS report was looking for a column named instructions and it didn’t exist, so it didn’t show it!

Next time, I’ll know better!