Problem solve Get help with specific problems with your technologies, process and projects.

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.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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.