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?
Oracle DatabaseSQL

Avatar of undefined
Last Comment
bmsande

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bmsande

ASKER
I swear I tried that while troubleshooting, but I must have missed something.  Thanks!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck