Link to home
Start Free TrialLog in
Avatar of bmsande
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:

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?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
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
bmsande

ASKER

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