how to select precisely column created on dual table

Question

i created a dummy table using dual in Oracle. If i execute this script using * to select all the lines, no problem it work. But when i try to select one named column, i have a problem who apparear : *

sql - ORA-00904: invalid identifier

Here it's my code :

    WITH
      DUMMY as 
        (   
            Select 1 "id", 'UBS' "bank" From Dual Union ALL
            Select 2, 'Credit Suisse' From Dual
        )
     -- SELECT * FROM DUMMY  it work
     SELECT id, bank FROM DUMMY -- it dont WORK, why?

Answer 1

Your CTE is using quoted identifiers. That makes the column names case-sensitive, and when you query using an unquoted equivalent that is treated as uppercase.

You either need to quote all references:

WITH
      DUMMY as 
        (   
            Select 1 "id", 'UBS' "bank" From Dual Union ALL
            Select 2, 'Credit Suisse' From Dual
        )
     -- SELECT * FROM DUMMY  it work
     SELECT "id", "bank" FROM DUMMY -- it dont WORK, why?

id	bank
1	UBS
2	Credit Suisse

Or - and this will make your life easier - do not use quoted identifiers (unless you really have to):

WITH
      DUMMY as 
        (   
            Select 1 id, 'UBS' bank From Dual Union ALL
            Select 2, 'Credit Suisse' From Dual
        )
     -- SELECT * FROM DUMMY  it work
     SELECT id, bank FROM DUMMY -- it dont WORK, why?

ID	BANK
1	UBS
2	Credit Suisse

fiddle

In the first version the column names are quoted as "id" and "bank" both in the CTE and in the query. Notice the column headings are also lowercase in the results, because that is exactly what you called the columns.

In the first version the column names are unquoted as id and bank both in the CTE and in the query. As they are unquoted, you could use id, ID, iD or Id and those would all be interpreted the same way. Here the column headings in the results are uppercase because unquoted identifiers are treated as uppercase, as described in the documentation.

It's still better to adopt a consistent style and naming/case standard, but at least without the quotes it's more forgiving if you get it wrong, plus you don't have remember to type the actual quote marks every time.