Solved

Pivot table dynamic query problem.

Posted on 2010-09-02
18
3,847 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
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

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 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.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

772 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