DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Pivot query

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    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.

  2. #2
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

  3. #3
    Join Date
    Jan 2006
    Posts
    39

    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;

  4. #4
    Join Date
    Mar 2008
    Posts
    3
    Search for the keyword stragg in google.

    some sample urls are

    http://www.williamrobertson.net/documents/one-row.html
    http://laurentschneider.blogspot.com...-in-10gr2.html

    Regards

    Raj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width