?
Solved

Pivot table dynamic query problem.

Posted on 2010-09-02
18
Medium Priority
?
4,316 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
16 Comments
 
LVL 7

Accepted Solution

by:
sumit2906 earned 252 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 500 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 500 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 248 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

864 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