Solved

Pivot table dynamic query problem.

Posted on 2010-09-02
18
3,974 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:scientia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +1
18 Comments
 
LVL 7

Accepted Solution

by:
sumit2906 earned 63 total points
ID: 33585241
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
 

Author Comment

by:scientia
ID: 33585281
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
 
LVL 17

Expert Comment

by:ram_0218
ID: 33585296
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:ram_0218
ID: 33585306
huh.. didnt see the first response.. unfortunately i dont have 11i to test, sorry
0
 
LVL 7

Expert Comment

by:sumit2906
ID: 33585323
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
 

Author Comment

by:scientia
ID: 33585344
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
 
LVL 17

Expert Comment

by:ram_0218
ID: 33585874
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
 

Author Comment

by:scientia
ID: 33585987
When I execute sql I get same ORA-00918 error.

0
 
LVL 17

Assisted Solution

by:ram_0218
ram_0218 earned 125 total points
ID: 33586152
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
 

Author Comment

by:scientia
ID: 33586237
:) your help is very useful for me. Now I get ORA-01748 error.
0
 
LVL 17

Expert Comment

by:ram_0218
ID: 33586285
simply can you bruteforce,
first replace 'FOR e2.OSUSER' with just OSUSER

if doesnt work, replace COUNT(e2.OSUSER) with just COUNT(OSUSER)
0
 

Author Comment

by:scientia
ID: 33586735
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
 
LVL 17

Assisted Solution

by:ram_0218
ram_0218 earned 125 total points
ID: 33597409
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 62 total points
ID: 33597485
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 33929808
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 33929828
small adjustment to code in http:#33586152  addresses syntax issues
ee.txt
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question