Hello, I am on 9i and have a question on pivot queries. This is my table data
and the output should be (based on the score):
Can I achieve this in a single query ?
I am currently using a function to return the data in the required format and calling that function in my SQL.
Will be good if it is possible to do within the query itself....
Dynamic SQL within PL/SQL procedure and ORA-00905
I tried to use dynamic SQL within a PL/SQL procedure to re-create materialized view. Because the SQL statement for the creation is long, I store these mv name, tablespace, refresh method, refresh key and SQL select statement in a local table called xxx_mv_information. Then I create procedure with dynamic SQL in. The procudure was compiled successfully. When I run the procedure, it always give me ORA-00905 missing key word error. I have carefully check the syntax and can not find the problems. Please help me to figure out where the problem is. Thanks a lot in advance. Here is the procedure I created.
CREATE OR REPLACE PROCEDURE sp_recreat_mvs
SELECT DISTINCT OWNER
SELECT TABSPACE||' '||REF_METHOD||' '||REF_KEY||' '||SQL_STRING
ORDER BY MV_NUM;
FOR x IN (SELECT MV_NAME FROM XXX_MV_INFORMATION ORDER BY MV_NUM)
v_sql_stmt := 'CREATE MATERIALIZED VIEW '||v_schema||'.'||x.mv_name||v_sql_string;
EXECUTE IMMEDIATE v_sql_stmt;
Click Here to Expand Forum to Full Width