Custom views and Oracle: The tyranny of capitalization
Oracle has a funny way of dealing with capital and lowercase letters in things like tables and views.p But then, Oracle is funny about a lot of things.
For example, did you know Oracle still has a 30-character limit for all object names?p No table with 31 characters.p Or that Oracle doesn't have a function for getting the difference between two dates in, for example, seconds, but it does have a function returning the difference as a floating-point number of months.
But a "month" isn't a single time unit, you cry.p Yes, well, it's a 30-day month.
Oh well, their buildings look like the database flowchart symbol and they have pretty, expensive lakes, so that's something I guess.
Anyway, back to case-sensitivity.
Our tables and views are (automatically) created using all-lowercase letters.p That means you can use any letter-casing you want when you access them.p Some report-writing tools automatically upper-case things, but that's OK.
With our views, however, there's a little snag.p The columns on the custom-field views (reviewcustom and defectcustom) are reproduced exactly as you typed the field title in the custom-field admin screen.p This was supposed to be a good thing because it's very easy to read.
Well if you're like 100% of our customers, you probably used mixed-case titles, because forms should say "Severity:" and not "severity:".p But that means mixed-case column names.
When you use mixed-case names with Oracle, you lose the ability to reference those names with any old letter-casing you want; now you must match the names exactly.
So that means this query works:
SELECT Severity, COUNT(*) FROM reviewcustom GROUP BY Severity
But this one doesn't:
SELECT severity, COUNT(*) FROM reviewcustom GROUP BY severity
And this one doesn't:
SELECT SEVERITY, COUNT(*) FROM reviewcustom GROUP BY SEVERITY
We're going to fix this in a future release.p For now, you'll just have to make sure you get the column names exactly right.