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

It's a bit hard to tell from the pseudo code here what's going on without any data. It's also hard to tell intentional pseudo-code from typos/incorrect syntax. Are you saying it works if you replace every instance of TO_CHAR(last_day(add_months(now(), -3))+1, 'YYYYMMDD') with '20150401'? The WHERE clause shows a TO_CHAR function that has no format string, so that itself will fail. — ScottMcG, Jun 10 '15 at 15:47
Apologies, I must have cut off the last portion of that conditional statement in the WHERE clause. Either way, we figured out the issue wasn't with the date function, but rather with the table. Somehow, the flags were altered that caused the result set to be severely limited. @ScottMcG Thank you for your reply, and correction on my bad cut-and-paste job. — justin mcnamara, Jun 10 '15 at 16:56

score 0 · Accepted Answer · answered Jun 17 '15 at 20:55

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

Netezza TO_CHAR Function not evaluating appropriately?

1 Answers1