Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    19

    Unanswered: Challenging query

    Dear Query Gurus,

    Hope you are doing great.Its a simple query to retrieve data with the Order_ID.If the record is there its fine and can retrieve the data.

    But my query is to retrieve the list of order_id which has no records in database.

    Plz help me to write a query.


    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by wungexh View Post
    But my query is to retrieve the list of order_id which has no records in database.
    How do you expect to retrieve something from the database that is not there.

    I guess you are not telling the important parts.

    Please post:

    • your table definitions (as CREATE TABLE, not as screenshots) and please use [code] tags to make your code readable.
      See here for details: http://www.dbforums.com/misc.php?do=bbcode#code
    • sample data in those tables
    • the expected output of your query based on the sample dat.

  3. #3
    Join Date
    Nov 2011
    Location
    USA
    Posts
    1

    re

    Hey ...This is very nice post that you have posted here. I am impressed. Keep it up.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Maybe it is about missing IDs, and the solution is one of row generator techniques. For example, let's create a table and insert some records in there:
    Code:
    SQL> create table test (id number);
    
    Table created.
    
    SQL> insert all
      2    into test values (1)
      3    into test values (4)
      4    into test values (5)
      5    into test values (8)
      6    into test values (10)
      7  select * from dual;
    
    5 rows created.
    
    SQL>
    It is obvious that IDs that are missing are 2, 3, 6, 7 and 9. OK then, let's find them!

    First, using a CONNECT BY clause, create the whole ID set:
    Code:
    SQL> select level id
      2    from dual
      3    connect by level <= (select max(id) from test);
    
            ID
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
    
    10 rows selected.
    One more step to a solution - using a MINUS set operator, find missing IDs:
    Code:
    SQL> select level missing_id
      2    from dual
      3    connect by level <= (select max(id) from test)
      4  minus
      5  select id
      6    from test;
    
    MISSING_ID
    ----------
             2
             3
             6
             7
             9
    That would be all, I guess.

Posting Permissions

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