Pivot table dynamic query problem.

I want to get list of users and their used programs in pivot table. And I able to get this list by the blow sql

SELECT * FROM
(select OSUSER,PROGRAM from gv$session where UPPER(PROGRAM) LIKE '%DAG%' ORDER BY OSUSER)
PIVOT (COUNT (OSUSER) FOR OSUSER IN ('MARK','JAMES'));


But I want to make dynamic OSUSER list by the blow query because of there is more users and that users can be change by LIKE '%DAG%' clouse. I wrote blow guery when I execute blow guery I get ORA-00936 error.

SELECT * FROM
(select OSUSER,PROGRAM from gv$session where UPPER(PROGRAM) LIKE '%DAG%' ORDER BY OSUSER)
PIVOT (COUNT (OSUSER) FOR OSUSER IN ( select DISTINCT OSUSER from gv$session where UPPER(PROGRAM) LIKE '%DAG%'   ));

Thanks for your help.
scientiaAsked:
Who is Participating?
 
sumit2906Connect With a Mentor Commented:
you can not use subquery in IN clause, following is what is written in Oracle Documents:
subquery: A subquery is used ONLY in conjunction with the XML keyword. When you
specify a subquery, all values found by the subquery are used for pivoting. The output
is not the same cross-tabular format returned by non-XML pivot queries.....


0
 
scientiaAuthor Commented:
But how I wrote this query dynamic ? I need an pivot query as result of this query  "sql select DISTINCT OSUSER from gv$session where UPPER(PROGRAM) LIKE '%DAG%'  "   this query returns to users to me that will be columns of the pivot table. Would you recomend any way for this. ?
0
 
ram_0218Commented:
check the blow:

SELECT * FROM
(select OSUSER,PROGRAM from gv$session where UPPER(PROGRAM) LIKE '%DAG%' ORDER BY OSUSER)
PIVOT XML (COUNT (OSUSER) FOR OSUSER IN ( select DISTINCT OSUSER from gv$session where UPPER(PROGRAM) LIKE '%DAG%'   ));
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ram_0218Commented:
huh.. didnt see the first response.. unfortunately i dont have 11i to test, sorry
0
 
sumit2906Commented:
check this link, at the end they have discussed about "pivoting an unknown domain of values", which similar to your issue:
http://www.oracle-developer.net/display.php?id=506
0
 
scientiaAuthor Commented:
ram_0218:  when I execute sql , I  face  ORA-00918 error  

------------------------

SELECT * FROM
(select OSUSER,PROGRAM from gv$session where UPPER(PROGRAM) LIKE '%DAG%' ORDER BY OSUSER)
PIVOT XML (COUNT (OSUSER) FOR OSUSER IN ( select DISTINCT OSUSER from gv$session where UPPER(PROGRAM) LIKE '%DAG%'   ));
0
 
ram_0218Commented:
can you alias your inner select like the blow:

SELECT * FROM
(select OSUSER,PROGRAM from gv$session where UPPER(PROGRAM) LIKE '%DAG%' ORDER BY OSUSER)
PIVOT XML (COUNT (OSUSER) FOR OSUSER IN ( select DISTINCT e1.OSUSER from gv$session e1 where UPPER(e1.PROGRAM) LIKE '%DAG%'   ));
0
 
scientiaAuthor Commented:
When I execute sql I get same ORA-00918 error.

0
 
ram_0218Connect With a Mentor Commented:
apologies i can't test i don't have 11. may be like blow:

SELECT * FROM
(select e2.OSUSER,e2.PROGRAM from gv$session e2 where UPPER(e2.PROGRAM) LIKE '%DAG%' ORDER BY e2.OSUSER)
PIVOT XML (COUNT (e2.OSUSER) FOR e2.OSUSER IN ( select DISTINCT e1.OSUSER from gv$session e1 where UPPER(e1.PROGRAM) LIKE '%DAG%'   ));

if not, may be COUNT(OSUSER)
0
 
scientiaAuthor Commented:
:) your help is very useful for me. Now I get ORA-01748 error.
0
 
ram_0218Commented:
simply can you bruteforce,
first replace 'FOR e2.OSUSER' with just OSUSER

if doesnt work, replace COUNT(e2.OSUSER) with just COUNT(OSUSER)
0
 
scientiaAuthor Commented:
Result is same, I am going to send combination of errors below.
       
   SELECT * FROM
(select e2.OSUSER,e2.PROGRAM from gv$session e2 where UPPER(e2.PROGRAM) LIKE '%DAG%' ORDER BY e2.OSUSER)
PIVOT XML (COUNT (e2.OSUSER) FOR OSUSER IN ( select DISTINCT e1.OSUSER from gv$session e1 where UPPER(e1.PROGRAM) LIKE '%DAG%'   ));

error ---> ORA-00918: sütun belirsiz bir tanimlandi

   SELECT * FROM
(select e2.OSUSER,e2.PROGRAM from gv$session e2 where UPPER(e2.PROGRAM) LIKE '%DAG%' ORDER BY e2.OSUSER)
PIVOT XML (COUNT (OSUSER) FOR e2.OSUSER IN ( select DISTINCT e1.OSUSER from gv$session e1 where UPPER(e1.PROGRAM) LIKE '%DAG%'   ));

error --> ORA-01748: burada sadece basit sütun adlarina izin verilir

   SELECT * FROM
(select e2.OSUSER,e2.PROGRAM from gv$session e2 where UPPER(e2.PROGRAM) LIKE '%DAG%' ORDER BY e2.OSUSER)
PIVOT XML (COUNT (OSUSER) FOR OSUSER IN ( select DISTINCT e1.OSUSER from gv$session e1 where UPPER(e1.PROGRAM) LIKE '%DAG%'   ));

error --> ORA-00918: sütun belirsiz bir tanimlandi

   SELECT * FROM
(select e2.OSUSER,e2.PROGRAM from gv$session e2 where UPPER(e2.PROGRAM) LIKE '%DAG%' ORDER BY e2.OSUSER)
PIVOT XML (COUNT (e2.OSUSER) FOR e2.OSUSER IN ( select DISTINCT e1.OSUSER from gv$session e1 where UPPER(e1.PROGRAM) LIKE '%DAG%'   ));

error --> ORA-01748: burada sadece basit sütun adlarina izin verilir

0
 
ram_0218Connect With a Mentor Commented:
SELECT * FROM
(select OSUSER,PROGRAM from gv$session where UPPER(PROGRAM) LIKE '%DAG%' ORDER BY OSUSER)
PIVOT XML (COUNT (OSUSER) FOR OSUSER IN (ANY ));

which is the samething what you're trying to do.. but pls ntoe the output type will be XML and you need to use XPath or any other parsing util to parse and print content..
0
 
sdstuberConnect With a Mentor Commented:
you can't create a query that will sometimes return 3 columns

MARK JAMES BILL

and other times 4 columns

MARK BILL SEAN THOM

and other times 1 column

FRED
etc

A SQL query must have a known and consistent result set at parse time.
The XML option works because it returns a single XMLTYPE column

But, if you parse the xml to create individual columns, ALL of those columns will need to be defined
They cannot be dynamic at run time. They must be defined at parse time.

0
 
sdstuberCommented:
I recommend split

http:#33585241 -- identifies the syntax error with subquery in non xml pivot
http:#33586152 -- noted by asker as being helpful
http:#33597485  -- summarizes problem of dynamic columns in sql
http:#33597409 -- provides alternate working syntax
0
 
sdstuberCommented:
small adjustment to code in http:#33586152  addresses syntax issues
ee.txt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.