Solved

Pivot table dynamic query problem.

Posted on 2010-09-02
18
3,718 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 73

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 73

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 73

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now