Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Location
    Asutria
    Posts
    3

    Red face Unanswered: Compare two different tables

    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.

    i got this code and it works fine, especially when there is a ORDER BY in the select statement.
    any ideas how to fix this?

    pls help me ! (:

    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;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    i got this code and it works fine, especially when there is a ORDER BY in the select statement.
    any ideas how to fix this?
    How to fix what (if it works fine)?
    ORDER BY? I don't see any.

    I am using test environment of Scott Tiger.
    There's no TB_FRAGE table in Scott's schema.

    What I meant to say: you should provide code we can run. Or, if you use your own tables, provide test case (CREATE TABLE and INSERT INTO several sample records).

  3. #3
    Join Date
    Mar 2013
    Location
    Asutria
    Posts
    3
    I am sorry, this was my first post a Forum like this.
    so... i explain it more....

    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 storen in TB_FRAGE

    so in case 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 is the statement stored in TB_FRAGE
    I hope this helps

    And without the ORDER BY it works

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >And without the ORDER BY it works
    what "ORDER BY" clause?

    posted SQL below is not valid syntax.
    (SELECT ename FROM emp WHERE job = 'MANAGER' AND sal > 2000 SORT BY ename

    ORDER BY clause does not have any impact on COUNT(*) results!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2013
    Location
    Asutria
    Posts
    3
    well my english sucks ..
    the Problem is it just throws a excaption and me and my team dunno why...
    there should be an oder by, not a sort by (:

Tags for this Thread

Posting Permissions

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