[SOLVED] Using field alias in MSQuery does not work with DB2

Using field alias in MSQuery does not work with DB2

This query works in data studio, but fails to show alias in MS Query! I have tried different types such as "",'',[] and even https://support.microsoft.com/en-us/kb/298955

SELECT 'TRANIN'AS NAME, SUM(CASE WHEN ALT3.TRANINDT BETWEEN 20150603 AND 20150601 THEN 1 else 0 END) AS CurrentMonth, SUM(CASE WHEN ALT3.TRANINDT BETWEEN 20150501 AND 20150531 THEN 1 else 0 END) AS LastMonth FROM ALT3

Solution

MS broke MS query a long time ago...

I've tried to get it to work right, but nothing worked. I've pretty much given up.

Normally I just rename the column once the data is back in Excel.

But if you really want the name returned from MS query, this works:

WITH tbl AS (SELECT 'TRANIN'AS NAME
             , SUM(CASE WHEN ALT3.TRANINDT BETWEEN 20150603 AND 20150601 
                     THEN 1 else 0 END) AS CurrentMonth
             , SUM(CASE WHEN ALT3.TRANINDT BETWEEN 20150501 AND 20150531 
                     THEN 1 else 0 END) AS LastMonth 
             FROM ALT3)
SELECT * FROM TBL