Quoted names
I need to sort by an alias name given to a column name. In SQL version, it is working properly, but in Oracle the query below fails.
SELECT CASE WE.SubscriptionStatus WHEN '1' THEN '2' WHEN '2' THEN '1' WHEN '3' THEN '4' WHEN '4' THEN '3' END AS "DummyStatus", WE.SubscriptionStatus, WE.CreatedOperatorID AS "CreatedOperatorID" FROM WEBEnrollments WE, Division D WHERE D.RefID = WE.RefID AND WE.SubscriptionStatus LIKE '%' ORDER BY DummyStatus;
Be consistent about the double quotes around the alias name. Either omit them when you define the alias (AS DummyStatus) or include them in the ORDER BY clause (ORDER BY "DummyStatus").
Oracle names, including aliases, are case sensitive. You can use Oracle for years without realizing this (like I did), because SQL*Plus and other Oracle tools usually capitalize everything that's not inside single -- or double quotes.
You have a table called WEBENROLLMENTS (with 14 capital letters). You may have created that table by saying
CREATE TABLE webenrollments ...(i.e., zero capital letters), but Oracle capitalized the name before executing the command. You can refer to that table in your query as WEBEnrollments (four capital letters) for the same reason. However, you can not refer to it as "WEBEnrollments" (quoted, with four capitals) because Oracle will not capitalize quoted names.
In SQL*Plus, you can say SET SQLCASE UPPER to make Oracle capitalize quoted strings. SET SQLCASE MIXED is the default setting: quoted strings are left exactly as you entered them. You can also say SET SQLCASE LOWER. These commands refer only to quoted text: Oracle converts unquoted text to upper case, regardless of the SQLCASE setting.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments