CODEWITHSUNDEEP
sqloracle-database
alexherm
alexherm

Reputation: 1362

Oracle - Invalid Identifier for column name

I ran into an interesting situation with a NUMBER field.

Here is a simplified example.

When I select a particular field have happens to be 29 characters long, it works just fine.

select FIELD_NAME_THAT_IS_29_CHAR_XX
from table;

In the report where this query is being used, the query does not return headers (this is an eText type XML publisher report). But when I run the query with a UNION, selecting the header names, I get an invalid identifier error.

SELECT "FIELD_NAME_THAT_IS_29_CHAR_XX" FROM dual
UNION
SELECT FIELD_NAME_THAT_IS_29_CHAR_XX FROM table1;

Returns:

ORA-00904: "LINE_RECEIPT_AMNT_AT_COST_USD": invalid identifier

The max length of a field name in Oracle DB is 30 chars. Am I hitting this limitation? I think not as this, for example:

SELECT "FIELD_NAME_THAT_IS_29_CHAR_XXxxxxx" FROM dual;

..gives:

ORA-00972: identifier is too long

What is wrong with the UNION?

Single quotes do not work either. This is a NUMBER field.

SELECT 'FIELD_NAME_THAT_IS_29_CHAR_XX' FROM dual
UNION
SELECT FIELD_NAME_THAT_IS_29_CHAR_XX FROM table1;

..gives:

ORA-01790: expression must have same datatype as corresponding expression

Upvotes: 1

Views: 1910

Answers (2)

alexherm
alexherm

Reputation: 1362

@Standin answer lead me to solution.

Because the selected field is a number, just need to cast is as a char - along with the single quotes.

SELECT 'FIELD_NAME_THAT_IS_29_CHAR_XX' FROM dual
UNION
SELECT to_char(FIELD_NAME_THAT_IS_29_CHAR_XX) FROM table1;

Upvotes: 1

Standin.Wolf
Standin.Wolf

Reputation: 1234

The problem is with the double quotes

Oracle SQL allows us to ignore the case of database object names provided we either create them with names all in upper case, or without using double quotes. If we use mixed case or lower case in the script and wrapped the identifiers in double quotes we are condemned to using double quotes and the precise case whenever we refer to the object or its attributes:

Instead use single quotes which essentially works and will give you the result you want

http://sqlfiddle.com/#!4/dce84/4

Upvotes: 3

Related Questions