1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to compare two tables?

Discussion in 'SQL PL/SQL' started by mainman, Mar 21, 2013.

  1. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    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, it just throws a exception
    both statetements use the same order by parameter
    any ideas how to fix this?

    Code:
    Code (Text):
    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:
    Code (Text):
    SELECT ename
    FROM emp
    WHERE job = 'MANAGER' AND sal > 2000
    ORDER BY ename;
    this statement ist stored in TB_FRAGE

    And without the ORDER BY it works


    thanks
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Why is the ORDER BY important for the query? Given that you're doing this in APEX the presumption is this is for a report (Classic or Interactive) and the user can sort the results as they please.


    Just wondering...

    CJ
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're using ONE input to the procedure and generating ONE statement from the internal query so I can't understand why you're trying to use an ORDER BY.

    Please explain; if there is a valid reason we will do our best to assist you.
     
  4. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    because when the students have to train sql statements where an ORDER BY should be used, thats the main reason :)
    thank you
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That makes no sense, to be honest. Usng an essentially useless ORDER BY, especually when you receive an error (an error, by the way , you failed to post) has no purpose.

    I think you need a better reason.
     
  6. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    If i could decide I wouldnt use an useless ORDER BY there, but the problem i have is, that it must work for my school thesis, so the teacher said to me and me project team, that the ORDER BY must work, because there are over 100 of questions to train students in ORACLE sql..., as you may remeber, there are such questions in the scott tiger tasks, and my teacher doestn know the reason why there is an exception thrown...
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You NEED to post the exception that is thrown; we cannot assist you in any way if you don't.
     
  8. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    EXECUTE IMMEDIATE('SELECT COUNT(*) FROM ('||v_frage||' MINUS '||v_statement||')') INTO v_erg;

    thats the main part of the function,
    so the idea behind it is... i compare the table with the statement of the student and the right statement, and if it is 0..its right

    Thats the error message
    ORA-00907 missing right parenthesis
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to check v_frage and v_statement for correct syntax -- I suspect that's the source of your problem. An order by in a MINUS query works just fine when the syntax is correct:

    Code (SQL):
    SQL> CREATE TABLE emp(
      2          smort NUMBER,
      3          poloy NUMBER,
      4          yeepa NUMBER
      5  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE emp2(
      2          smort NUMBER,
      3          jerple varchar2(20),
      4          yeepa NUMBER
      5  );
     
    TABLE created.
     
    SQL>
    SQL> SELECT column_name
      2  FROM user_tab_columns
      3  WHERE TABLE_NAME = 'EMP'
      4  minus
      5  SELECT column_name
      6  FROM user_tab_columns
      7  WHERE TABLE_NAME = 'EMP2'
      8  ORDER BY 1;
     
    COLUMN_NAME
    ------------------------------
    POLOY
     
    SQL>
     
  10. mainman

    mainman Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    thanks, i will implement it so far i get time for that :)
    and might it be..that there are two "order by" it might fail two?

    its implemenetd like this
    Code (Text):
    SELECT column_name
    FROM user_tab_columns
    WHERE TABLE_NAME = 'EMP'
    ORDER BY 1;
    minus
    SELECT column_name
    FROM user_tab_columns
    WHERE TABLE_NAME = 'EMP2'
    ORDER BY 1;
    und you did i just with one order by.

    might this also be a mistake?
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes. You have terminated the first part of the MINUS query so you never get to the second part of it. The semicolon after the first order by terminates that query. Order by comes after the second query in the MINUS construct -- look at my example again for proper syntax. Your query should look like this:

    Code (SQL):

    SELECT column_name
    FROM user_tab_columns
    WHERE TABLE_NAME = 'EMP'
    minus
    SELECT column_name
    FROM user_tab_columns
    WHERE TABLE_NAME = 'EMP2'
    ORDER BY 1;
     
     
  12. krishnaiah

    krishnaiah Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    By using Joins concept in SQL
     
  13. krishnaiah

    krishnaiah Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    By Using Joins Topic in SQL.
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Thank you for such an informative post, including relevant examples.

    Using joins is no easier than using MINUS as two separate queries need to be written to provide the join data:

    Code (SQL):
    SQL> CREATE TABLE emp(
      2          smort NUMBER,
      3          poloy NUMBER,
      4          yeepa NUMBER
      5  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE emp2(
      2          smort NUMBER,
      3          jerple varchar2(20),
      4          yeepa NUMBER
      5  );
     
    TABLE created.
     
    SQL>
    SQL> SELECT column_name
      2  FROM user_tab_columns
      3  WHERE TABLE_NAME = 'EMP'
      4  minus
      5  SELECT column_name
      6  FROM user_tab_columns
      7  WHERE TABLE_NAME = 'EMP2'
      8  ORDER BY 1;
     
    COLUMN_NAME
    ------------------------------
    POLOY
     
    SQL>
    SQL> SELECT s.column_name, d.column_name
      2  FROM
      3  ((SELECT column_name
      4  FROM user_tab_columns
      5  WHERE TABLE_NAME = 'EMP') s
      6  FULL OUTER JOIN
      7  (SELECT column_name
      8  FROM user_tab_columns
      9  WHERE TABLE_NAME = 'EMP2') d
     10  ON (d.column_name = s.column_name))
     11  WHERE d.column_name IS NULL
     12  ORDER BY 1;
     
    COLUMN_NAME                    COLUMN_NAME
    ------------------------------ ------------------------------
    POLOY
     
    SQL>