Skip to Main Content
  • Questions
  • ORA-00904: invalid identifier when specifying column from Pivot result set

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Harry.

Asked: August 13, 2017 - 8:32 pm UTC

Last updated: August 14, 2017 - 10:05 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

I have had a Pivot result set with 40K entries, and I wanted to limit it further to see what rows from the result set with further criteria on the column values. To my surprise, I got invalid column error.

Below is a simplified test case demonstrating the issue.

create table test(interface varchar2(20), item varchar2(10))
/
insert into test values('interface1', '11111')
/
insert into test values('interface2', '22222')
/
insert into test values('interface3', '33333')
/

select * from
(
select interface, item, 1 as count from test 
) R 
pivot (
sum(count) for (interface) in ('interface1', 'interface2','interface3')
)
order by 1
/

Result:

ITEM   interface1  interface2  interface3
11111 1          null        null
22222   null           1       null 
33333   null       null        1

select * from
(
select interface, item, 1 as count from test 
) R 
pivot (
sum(count) for (interface) in ('interface1', 'interface2','interface3')
)
where interface1 > 0;

ORA-00904: "INTERFACE1": invalid identifier


Why did I got this error?
Is thetre a work around such that I could specify the column names in the WHERE clause?

Thanks

and Chris said...

The new column headings are the exact values you've got in the IN clause. So 'interface1' (including single quotes) and so on. These are case sensitive.

So to refer to them, use double quotes:

select * from
(
select interface, item, 1 as count from test 
) R 
pivot (
sum(count) for (interface) in ('interface1', 'interface2','interface3')
)
where "'interface1'" > 0;

ITEM   'interface1'  'interface2'  'interface3'  
11111  1


Or, better, alias the columns in the pivot clause and you can skip the quotes:

select * from
(
select interface, item, 1 as count from test 
) R 
pivot (
sum(count) for (interface) in (
  'interface1' INT1, 'interface2' INT2 ,'interface3' INT3
)
)
where int1 > 0;

ITEM   INT1  INT2  INT3  
11111  1   

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thanks. that works.

A reader, August 14, 2017 - 3:25 pm UTC

thanks. that works.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.