how to compare two different tables?

mainman666

Neuer Benutzer
Beiträge
1
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
 
Werbung:
Oben