Oracle PIVOT with COUNT() missing data

sutekh137 0
Viewed 22

I am counting events from a history table and want to pivot on the month the events occur. Base query is something like this:

SELECT TO_CHAR(Date_Entered, 'MONTH') AS Month, Userid FROM Customer_Order_History

The full query that implements the PIVOT then looks like:

SELECT * FROM
(SELECT TO_CHAR(Date_Entered, 'MONTH') AS Month, Userid FROM Customer_Order_History) 
PIVOT
(
    COUNT(*) AS Events
    FOR Month 
    IN ('JANUARY' AS Jan, 'FEBRUARY' AS Feb, 'MARCH' AS Mar, 'APRIL' AS Apr,
        'MAY' AS May, 'JUNE' AS Jun, 'JULY' AS Jul, 'AUGUST' AS Aug,
        'SEPTEMBER' AS Sep, 'OCTOBER' AS Oct, 'NOVEMBER' AS Nov, 'DECEMBER' AS Dec)
)

This query is valid and runs fine. Except in the results, where I should have had events to count for July, August, and September, everything is zeroes except September.

The problem was with the literal values in the IN() clause of the PIVOT. I come from a FoxPro background where the length of a string data column is not variable, so "JULY" is not the same as "JULY " (JULY plus five spaces). On a hunch, then, I changed the literals in the IN() to all be nine characters in length (the longest a month name can be), and July and August began reporting counts.

Of course, the better way to do this is to use a more consistent format for the month, such as just using the first three characters or using the month index. But the above-described behavior seems very odd to me. After all, if I compared a VARCHAR(100) with a VARCHAR(2000), and they were both "JULY", in a WHERE clause or a JOIN, they would be considered equal. I am used to padding strings to a certain width when comparing in FoxPro -- not in Oracle.

Has anyone else seen this in PIVOTs, and is there some larger concept I am missing in Oracle where the size of a VARCHAR column suddenly starts to matter (other scenarios I should be aware of)?

literals pivot oracle count
1 Answers

This isn't about pivoting, or about general string comparisons; it's about how month names are produced. From the documentation:

The character elements MONTH, MON, DAY, and DY are padded with trailing blanks to the width of the longest full month name, the longest abbreviated month name, the longest full date name, or the longest abbreviated day name, respectively, among valid names determined by the values of NLS_DATE_LANGUAGE and NLS_CALENDAR parameters. For example, when NLS_DATE_LANGUAGE is AMERICAN and NLS_CALENDAR is GREGORIAN (the default), the largest element for MONTH is SEPTEMBER, so all values of the MONTH format element are padded to nine display characters. ...

... which is exactly what you are seeing. The comparison with your literals in the pivot clause is still using nonpadded character semantics - but the strings produced by to_char() are padded to nine characters and your literals are not, so they don't match.

If you change your base query to add the FM format modifier then it won't pad the names:

TO_CHAR(Date_Entered, 'FMMONTH')

Since your pivot clause is relying on the names being in English, you might also want to force that to be used, overriding the session NLS settings of whoever runs the code:

TO_CHAR(Date_Entered, 'FMMONTH', 'NLS_DATE_LANGUAGE=ENGLISH')

which will always give you 'MAY', 'JUNE', etc. with no padding; or

TO_CHAR(Date_Entered, 'FMMonth', 'NLS_DATE_LANGUAGE=ENGLISH')

which will always give you 'May', 'June', etc. still with no padding, but will then look a bit less shouty in the pivot list.

Alex Poole0
Related