Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

how to compare two different tables?

Dieses Thema im Forum "Oracle" wurde erstellt von mainman666, 21 März 2013.

  1. mainman666

    mainman666 Neuer Benutzer

    Hello
    I am working on a Application in Oracle Apex 4.2

    I want to compare two tables with the same collums but with different Data and count the differences.
    I am using test environment of Scott Tiger.

    There are students who can train their sql skills on this application.
    Like "Who has the highest sal?" etc
    and this function has to compare the sql statement from the student, which he types in in our GUI and the sqlstatement which is stored in TB_FRAGE

    I got this code and it works fine, but when there is a ORDER BY in the select statement it doesnt work anymore
    any ideas how to fix this?

    Code:
    CREATE OR REPLACE FUNCTION checkSQL (fragenID IN NUMBER, v_statement IN VARCHAR)

    RETURN NUMBER IS

    v_frage VARCHAR(1000);

    v_ID NUMBER(4);
    v_Statement VARCHAR(1000);
    v_erg NUMBER(2);


    BEGIN
    v_ID := fragenID;

    SELECT FRAGE into v_frage
    FROM tb_frage
    WHERE v_id = v_ID;


    EXECUTE IMMEDIATE('SELECT COUNT(*) FROM ('||v_frage||' MINUS '||v_statement||')') INTO v_erg;


    RETURN v_erg;
    END;



    so in case there a question like

    "Get all Managers who earn more than 2000$ and sort by name"

    Code:
    SELECT ename
    FROM emp
    WHERE job = 'MANAGER' AND sal > 2000
    SORT BY ename;

    this statement ist stored in TB_FRAGE

    And without the ORDER BY it works

    thanks
     

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden