Link to home
Start Free TrialLog in
Avatar of bmsande

asked on

Invalid Identifier in Oracle Pivot query

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:

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,, COUNT(auditid) as NumReads FROM DAUDITNEW aa, KUAF bb WHERE auditdate > SYSDATE - 180 AND aa.performerid = 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, ORDER BY COUNT(auditid) DESC
where rownum < 4
  max(name) for ROWNUM in ('1','2','3')

Open in new window

Error:  ORA-00904: : invalid identifier

Any ideas?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bmsande


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