Netezza TO_CHAR Function not evaluating appropriately?

Question

I am having issues with a query that, if ran with hard-coded dates, will insert the correct number of rows into a table (170K+). The issue is, when I try to automate it, by replacing the hard-coded dates with date functions, the query will then only insert a single row into a newly truncated table.

Example hard-coded date: '20150401'

Sample of that same date, using the date function:

TO_CHAR(last_day(add_months(now(), -3))+1, 'YYYYMMDD') 

The above TO_CHAR function returns the desired result, when ran separately.

Here is a cleaned-up version of the query that results in a single row being inserted:

INSERT INTO SCHEMA.INSERT_TABLE(

SELECT TO_CHAR(now(), 'YYYYMM') TRAN_MONTH,

    SUM(CASE WHEN B.DATE = TO_CHAR(last_day(add_months(now(), -3))+1, 'YYYYMMDD')
        THEN 'Do stuff'
        END) AS Stuff1,
    SUM(CASE WHEN B.DATE = TO_CHAR(last_day(add_months(now(), -3))+1, 'YYYYMMDD')
        THEN 'Do other stuff'
        END) AS Stuff2,
    SUM(CASE WHEN B.DATE = TO_CHAR(last_day(add_months(now(), -3))+1, 'YYYYMMDD')
        THEN 'Do really weird stuff'
        END) AS Stuff3,
    SUM(CASE WHEN B.DATE = TO_CHAR(last_day(add_months(now(), -3))+1, 'YYYYMMDD')
        THEN 'Do really really weird stuff'
        END) AS Stuff4,
    SUM(CASE WHEN A.CODE= 1
        THEN 'Do ... '
        END) AS Stuff5,

FROM

(SELECT Col1, Col2...  FROM Table_A) A,
(SELECT Col1, Col2...  FROM Table_B) B,
(SELECT Col1, Col2...  FROM Table_C) C,
(SELECT Col1, Col2...  FROM Table_D) D,
(SELECT Col1, Col2...  FROM Table_E) E,

WHERE 'Conditions for A, B, C, D, and E are met'
    AND B.DATE = TO_CHAR(last_day(add_months(now(), -3))+1,'YYYYMMDD')
GROUP BY All of the things
ORDER BY Something
);

I have done quite a bit of testing, and research, but I haven't found a possible cause as to why the amount of records returned would be so drastically different.

Thank you,

Justin

Answer 1

I think it's because you added a 1 to the character string resulting from your last_day function. Check your parentheses:

WHERE 'Conditions for A, B, C, D, and E are met'
    AND B.DATE = TO_CHAR(last_day(add_months(now(), -3)+1)

If it isn't that (or you really do want to add 1 to a character string), then I'm going to go out on a limb and assume that B.DATE is a column of type date. If so, the reason it isn't comparing correctly is because you're relying on implicit conversion. Change your date filter to explicitly convert both sides.

WHERE 'Conditions for A, B, C, D, and E are met'
    AND B.DATE::date = (last_day(add_months(now(), -3)+1)::date