0

Below select statement causes ORA-00972: identifier is too long error. Suspect a syntax error. Running this select to generate a view in SQLPLUS on an Oracle db. Please advise. Thanks.

SELECT DISTINCT
    (CASE WHEN PROCEDURE_CODE IS NULL THEN REVENUE_CODE ELSE PROCEDURE_CODE END)
FROM HEALTH.DATA
peter.hrasko.sk
  • 4,043
  • 2
  • 19
  • 34
Beemer12
  • 1
  • 2
  • There is nothing wrong with the statement syntactically. Did u try running it without distinct? Also try to reproduce it with help of fiddle or dual statement to give us something to work with – Utsav Jun 08 '17 at 15:29
  • 1
    You lack the alias to that value. When you create a view, all columns must pass the rules of names definitions in Oracle. So basically, what you needed was to add a name to that column. – Renato Afonso Jun 08 '17 at 16:04
  • Unrelated, but: the parentheses around the `case` expression are useless. –  Jun 08 '17 at 16:10
  • 1
    Curious that you didn't get `ORA-00998: must name this expression with a column alias` when creating the view. Might have been helpful to show the entire statement you were running, and to have included the full Oracle version. – Alex Poole Jun 08 '17 at 16:21

1 Answers1

2

I can't reproduce the problem but maybe my database version is different.

My guess though is adding an alias to your select : 

SELECT DISTINCT (CASE
                   WHEN PROCEDURE_CODE IS NULL THEN
                    REVENUE_CODE
                   ELSE
                    PROCEDURE_CODE
                END) AS THING
  FROM HEALTH.DATA

If you don't specify any, it "builds" the column name using the full "CASE" and it might bust some limit there I suppose.

Dessma
  • 599
  • 3
  • 11