Invalid Identifier in Oracle Pivot query

bmsande
bmsande used Ask the Experts™
on
I'm attempting to pivot a result set on the oracle "ROWNUM" column with no such luck thus far.  My current result set returns the following:

ROWNUM   NAME
1	user1
2	user2
3	user3

Open in new window


The rownum column represents the most active user in the result set (1 being most active), so I want to pivot on ROWNUM to show columns 1-3, with the value being NAME.  Here is the query that I came up with, which returns an 'invalid identifier' (ROWNUM) in the pivot section of the query...

select * from (
select ROWNUM, NAME from 
(SELECT BB.ID,bb.name, COUNT(auditid) as NumReads FROM DAUDITNEW aa, KUAF bb WHERE auditdate > SYSDATE - 180 AND aa.performerid = bb.id AND ( AA.AUDITSTR ='View' or AA.AUDITSTR='Fetch')
AND BB.ID <> 9471817 AND AA.DATAID in (select dataid from dtree start with dataid=19628279 connect by prior dataid=parentid) GROUP BY BB.ID,bb.name ORDER BY COUNT(auditid) DESC
)
where rownum < 4
)
pivot(
  max(name) for ROWNUM in ('1','2','3')
)

Open in new window


Error:  ORA-00904: : invalid identifier

Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
rownum is a reserved word.  Alias it in the inner query:

select * from (
select ROWNUM myrn, NAME from 
(SELECT BB.ID,bb.name, COUNT(auditid) as NumReads FROM DAUDITNEW aa, KUAF bb WHERE auditdate > SYSDATE - 180 AND aa.performerid = bb.id AND ( AA.AUDITSTR ='View' or AA.AUDITSTR='Fetch')
AND BB.ID <> 9471817 AND AA.DATAID in (select dataid from dtree start with dataid=19628279 connect by prior dataid=parentid) GROUP BY BB.ID,bb.name ORDER BY COUNT(auditid) DESC
)
where rownum < 4
)
pivot(
  max(name) for myrn in ('1','2','3')
)
/

Open in new window

Author

Commented:
I swear I tried that while troubleshooting, but I must have missed something.  Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial