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 identifierWhy did I got this error?
Is thetre a work around such that I could specify the column names in the WHERE clause?
Thanks
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 1Or, 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