Forum Stats

  • 3,855,310 Users
  • 2,264,499 Discussions
  • 7,905,967 Comments

Discussions

Pivot function and Missing expression error

Parth Divekar
Parth Divekar Member Posts: 16
edited Jul 27, 2009 1:31PM in SQL & PL/SQL
select * from (
select T.PAT_MRN_ID, T.ABBREVIATION,CONCEPT_VALUE from TBL_WORKLFOw T
)
pivot
(
max(concept_value)
for ABBREVIATION in (select ABBREVIATION from TBL_WORKLFOW )
)

this query is throwing "missing expression"..error.

Can some one help figuring out the problem.
Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Jul 27, 2009 1:05PM Answer ✓
    Hi,

    Sorry, that won't work.
    You can only use a sub-query in the pivot IN clause if you are saying "PIVOT *XML* ..."
    You need to use dynamic SQL to get non-XML output for all values, if you cannot hard-code them into the query.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Jul 27, 2009 1:05PM Answer ✓
    Hi,

    Sorry, that won't work.
    You can only use a sub-query in the pivot IN clause if you are saying "PIVOT *XML* ..."
    You need to use dynamic SQL to get non-XML output for all values, if you cannot hard-code them into the query.
  • Thanks Frank

    I initially thought so after looking at various examples on OTN.

    Thanks for confirmation.

    So your suggestion is to have it in a procedure ...form a dynamic sql using values from query and then execute the sql..

    i will try that and post the updates soon...

    regards
    Parth
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Jul 27, 2009 1:31PM
    Hi, Parth,
    Parth Divekar wrote:
    ...
    So your suggestion is to have it in a procedure ...form a dynamic sql using values from query and then execute the sql..
    That's one thing you can do.
    You might also look into string aggregation, where you have one gargantuan column containing all the pivoted data from all the abbreviatons, no matter how many there are. That big column can be formatted so that it looks like several distinct columns.

    Assuming dynamic SQL is the best answer, I find it easier to do in SQL*Plus, using files or substitution variables, as shown below. That's just my preference; you may like PL/SQL better.
    /*
    How to Pivot a Table with a Dynamic Number of Columns
    
    This works in any version of Oracle
    The "SELECT ... PIVOT" feature introduced in Oracle 11
    is much better for producing XML output.
    
    Say you want to make a cross-tab output of
    the scott.emp table.
    Each row will represent a department.
    There will be a separate column for each job.
    Each cell will contain the number of employees in
    	a specific department having a specific job.
    The exact same solution must work with any number
    of departments and columns.
    (Within reason: there's no guarantee this will work if you 
    want 2000 columns.)
    
    Case 0 "Basic Pivot" shows how you might hard-code three
    job types, which is exactly what you DON'T want to do.
    Case 1 "Dynamic Pivot" shows how get the right results
    dynamically, using SQL*Plus.  
    (This can be easily adapted to PL/SQL or other tools.)
    */
    
    
    PROMPT	==========  0. Basic Pivot  ==========
    
    SELECT	deptno
    ,	COUNT (CASE WHEN job = 'ANALYST'  THEN 1 END)	AS analyst_cnt
    ,	COUNT (CASE WHEN job = 'CLERK'    THEN 1 END)	AS clerk_cnt
    ,	COUNT (CASE WHEN job = 'SALESMAN' THEN 1 END)	AS salesman_cnt
    FROM	scott.emp
    WHERE	job	IN ('ANALYST', 'CLERK', 'SALESMAN')
    GROUP BY	deptno
    ORDER BY	deptno
    ;
    
    
    PROMPT	==========  1. Dynamic Pivot using Script  ==========
    
    --	*****  Start of dynamic_pivot.sql  *****
    
    -- Suppress SQL*Plus features that interfere with raw output
    SET	FEEDBACK	OFF
    SET	PAGESIZE	0
    
    SPOOL	p:\sql\cookbook\dynamic_pivot_subscript.sql
    
    SELECT	DISTINCT
    	',	COUNT (CASE WHEN job = '''
    ||	job
    ||	''' '	AS txt1
    ,	'THEN 1 END)	AS '
    ||	job
    ||	'_CNT'	AS txt2
    FROM	scott.emp
    ORDER BY	txt1;
    
    SPOOL	OFF
    
    -- Restore SQL*Plus features suppressed earlier
    SET	FEEDBACK	ON
    SET	PAGESIZE	50
    
    SPOOL	p:\sql\cookbook\dynamic_pivot.lst
    
    SELECT	deptno
    @@dynamic_pivot_subscript
    FROM	scott.emp
    GROUP BY	deptno
    ORDER BY	deptno
    ;
    
    SPOOL	OFF
    
    --	*****  End of dynamic_pivot.sql  *****
    
    /*
    EXPLANATION:
    The basic pivot assumes you know the number of distinct jobs,
    and the name of each one.  If you do, then writing a pivot query
    is simply a matter of writing the correct number of ", COUNT ... AS ..."\
    lines, with the name entered in two places on each one.  That is easily
    done by a preliminary query, which uses SPOOL to write a sub-script 
    (called dynamic_pivot_subscript.sql in this example).
    
    The main script invokes this sub-script at the proper point.
    In practice, .SQL scripts usually contain one or more complete
    statements, but there's nothing that says they have to.
    This one contains just a fragment from the middle of a SELECT statement.
    
    Before creating the sub-script, turn off SQL*Plus features that are 
    designed to help humans read the output (such as headings and 
    feedback messages like "7 rows selected.", since we do not want these 
    to appear in the sub-script.
    Turn these features on again before running the main query.
    
    */
    
    PROMPT	==========  2. Dynamic Pivot using Substitution Variable  ==========
    
    --	*****  Preliminary Query:  *****
    
    COLUMN	sql_txt_col	NEW_VALUE	sql_txt
    
    WITH	all_jobs	AS
    (
    	SELECT DISTINCT
    		job
    	,	DENSE_RANK () OVER (ORDER BY job)	AS r_num
    	FROM	scott.emp
    )	
    SELECT	SYS_CONNECT_BY_PATH ( job || '''	THEN 1 END) AS '
    				  || job
    				  || '_CNT'
    				  || CHR (10)			-- Newline, for legibility only
    			    , ', COUNT (CASE WHEN job = '''	-- Delimiter, goes before each entry
    			    ) 	       	     	      	AS sql_txt_col
    FROM	all_jobs
    WHERE	CONNECT_BY_ISLEAF	= 1
    START WITH	r_num = 1
    CONNECT BY	r_num = PRIOR r_num + 1
    ;
    
    --	*****  Main Query  *****
    
    SELECT	deptno
    &sql_txt	-- Ends with newline, so occupy that line
    FROM	scott.emp
    GROUP BY	deptno
    ORDER BY	deptno
    ;
    
    /*
    EXPLANATION:
    
    Using a substitution variable is very similar to using a script.
    The main difference is that the output of the preliminary query has to
    go into one row.  This is done with SYS_CONNECT_BY_PATH, and getting
    that requires that the jobs be numbered with consecutive integers, 1, 2, 3, ...
    which we get from DENSE_RANK.
    
    The NEWLINE character was added just to make the output line easier
    to read during debugging.
    */
This discussion has been closed.