1

Could you help me on this one?

See I have this order_table where I have to find the order numbers that don't exist in the table.

Let's say I have columns id, order_number in the table named order_table.

What I want is to show in my result all the values that don't exist in order_table.

Like for example:

I have order_number values 43454235423, 785686847856, 1243215421, 6437647, 978423454. I then do

SELECT *
FROM order_table
WHERE order_number = ('43454235423', '45423534252','785686847856', '1243215421',
                      '6437647', '978423454','5634636','4354235235')
--return order_numbers that don't exist in the table as 'status'

This is my sample query result:

order_number  status
------------  ---------
43454235423   not found
785686847856  not found
1243215421    not found
6437647       not found
978423454     not found
2
create table order_table(order_number number);
insert into order_table values (43454235423);
commit;

with input_values as
(
  select
    to_number(column_value) as order_number
  from
    xmltable('43454235423, 785686847856, 1243215421, 6437647, 978423454')
)
select
  v.order_number, 'not found' as status
from
  input_values v
left join
  order_table o on (v.order_number = o.order_number)
where
  o.order_number is null
;

        ORDER_NUMBER STATUS
-------------------- ---------
        785686847856 not found
          1243215421 not found
             6437647 not found
           978423454 not found
  • order_table is already an existing table. I'm only querying in a single table alone. – WashichawbachaW Oct 10 '17 at 2:51
1

As you want the results to be returned as rows, you need the input to be in the form of a row set as well.

One way is to create such a row set dynamically, right in the query, using a series of SELECTs FROM DUAL combined with UNION ALL:

SELECT '43454235423' AS order_number FROM DUAL
UNION ALL
SELECT '45423534252' FROM DUAL
UNION ALL
SELECT '785686847856' FROM DUAL
UNION ALL
...

Another way would be to insert the input values into an actual table and use that table in the query.

Whatever way you represent the input, the next step will be to anti-join the order_table to the input table. There are various ways of implementing an anti-join. One very common method is using NOT EXISTS:

SELECT
  input.order_number,
  'not found' AS status
FROM
  (
    SELECT '43454235423' AS order_number FROM DUAL
    UNION ALL
    SELECT '45423534252' FROM DUAL
    UNION ALL
    SELECT '785686847856' FROM DUAL
    UNION ALL
    ...
  ) input
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      order_table o
    WHERE
      o.order_number = input.order_number
  )
;

You can also use NOT IN:

SELECT
  order_number,
  'not found' AS status
FROM
  (
    SELECT '43454235423' AS order_number FROM DUAL
    UNION ALL
    SELECT '45423534252' FROM DUAL
    UNION ALL
    SELECT '785686847856' FROM DUAL
    UNION ALL
    ...
  )
WHERE
  order_number NOT IN
  (
    SELECT
      order_number
    FROM
      order_table o
  )
;

Another common technique is LEFT JOIN + IS NULL:

SELECT
  input.order_number,
  'not found' AS status
FROM
  (
    SELECT '43454235423' AS order_number FROM DUAL
    UNION ALL
    SELECT '45423534252' FROM DUAL
    UNION ALL
    SELECT '785686847856' FROM DUAL
    UNION ALL
    ...
  ) input
  LEFT JOIN order_table ot ON o.order_number = input.order_number
WHERE
  o.id IS NULL
;

And then there is also MINUS (Oracle's equivalent of the SQL standard's EXCEPT operator):

SELECT
  order_number,
  'not found' AS status
FROM
  (
    SELECT '43454235423' AS order_number FROM DUAL
    UNION ALL
    SELECT '45423534252' FROM DUAL
    UNION ALL
    SELECT '785686847856' FROM DUAL
    UNION ALL
    ...
  )

MINUS

SELECT
  order_number,
  'not found' AS status
FROM
  order_table
;
  • Thank you for your answer, however, I'm doing like thousands of thousands of values every query. Imagine If I do that SELECT 'value' AS ordernumber FROM DUAL a thousand times? It's really a pain in the ass right? Is there any other solution like putting all the values in one group like ('value1','value2','value3','value4',......)? – WashichawbachaW Oct 5 '17 at 23:34
  • Other database products support the VALUES table constructor syntax, like VALUES ('value1'), ('value2'), ('value3), … which allows you to create a table in a syntactically more compact way. I'm not sure if the latest release of Oracle DB supports it. And if it doesn't, I have no other ideas at the moment, sorry. – Andriy M Oct 6 '17 at 7:22
  • 1
    On the other hand, why are there so many values to check? Do you need to check all of them in one query? Also, where do they come from? If they are already in some table, perhaps you could use it in an anti-join using any of the patterns in my answer? – Andriy M Oct 6 '17 at 7:24
  • I'm running my query on Oracle SQL Developer (3.2.09). Yes! I need to check all of them in one query because every value or order_number is part of one file e.g. file_A = 1000 order_numbers. So if I find like 100 missing order_numbers out of 1000 then I'll report it like, In file_A there are 100 missing order_numbers and I will also include the missing order_umbers in my report. By the way, can we move this convo to chat? – WashichawbachaW Oct 6 '17 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.