-
Pivot query
Hello, I am on 9i and have a question on pivot queries. This is my table data
Code:
QUESTION SCORE
------------------
1 0
2 1
3 0
4 1
5 0
and the output should be (based on the score):
Code:
CORRECT_ANSWER WRONG_ANSWER
-----------------------------------------
2,4 1,3,5
Can I achieve this in a single query ?
Thanks.
-
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....
Thanks.
-
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
IS
v_schema user_mviews.owner%type;
v_sql_string varchar2(1000);
v_sql_stmt varchar2(2000);
BEGIN
SELECT DISTINCT OWNER
INTO v_schema
FROM USER_MVIEWS;
SELECT TABSPACE||' '||REF_METHOD||' '||REF_KEY||' '||SQL_STRING
INTO v_sql_string
FROM XXX_MV_INFORMATION
ORDER BY MV_NUM;
FOR x IN (SELECT MV_NAME FROM XXX_MV_INFORMATION ORDER BY MV_NUM)
LOOP
v_sql_stmt := 'CREATE MATERIALIZED VIEW '||v_schema||'.'||x.mv_name||v_sql_string;
EXECUTE IMMEDIATE v_sql_stmt;
END LOOP;
COMMIT;
END;
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|