CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11gwhere-clause
Bodhi
Bodhi

Reputation: 548

How to use multiple entry in where clause?

I have ninety customer order REFNOs and want to include all of them in WHERE clause.

I have written this:

select * 
from  TO_SALES_INVO 
where REFNO = '201582255'

The above will return me one record. But now I have list of REFNO (20190525,2018525, etc) and want to select all of them using SQL. The REF No is not in any sequence or range and they are random. Nevertheless I want the result in the same order I put in WHERE clause. For example : where REFNO in ( '12','2',' '5','1') should order the same way as in the parentheses.

How to do it?

Note : I am using Toad

Upvotes: 0

Views: 403

Answers (6)

ajz
ajz

Reputation: 255

This should get the results that you want, even though the syntax for the list of REFNO values is a bit different (i.e. comma separated string). It generates a set of rows for the items that you're filtering by and then joins to that.

It uses a technique to split the comma separated value as found here.

WITH
    DATA AS (SELECT '12,2,5,1' str FROM DUAL),
    LOOKUP AS
    (
        SELECT LEVEL lvl, REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) str FROM DATA
        CONNECT BY REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) IS NOT NULL
    )
SELECT TO_SALES_INFO.*
  FROM LOOKUP INNER JOIN TO_SALES_INFO ON LOOKUP.str = TO_SALES_INFO.REFNO
  ORDER BY LOOKUP.lvl;

If your list of REFNO values is going to be reused it is probably best to just create a separate table for them rather than using the complex CONNECT BY stuff that I've shown.

Upvotes: 0

Neo
Neo

Reputation: 51

SELECT *
FROM (select '12' as REFNO FROM DUAL
      UNION ALL
      select '2' FROM DUAL
      UNION ALL
      ....
     ) A
JOIN TO_SALES_INVO ON ...

Please notice that you must use UNION ALL instead of UNION or there will be a grouping performed in sub query "A" which will change the order.

Upvotes: 0

Andrea Annibali
Andrea Annibali

Reputation: 389

Assuming that you have the table ORDER with REFNO field, you can try with the query:

SELECT * from ORDER WHERE REFNO in (SELECT REFNO FROM TO_SALES_INVO);

Better choice with explicit JOIN:

SELECT * FROM ORDER t1 JOIN TO_SALES_INVO t2 on t1.REFNO=t2.REFNO;

In general I suggest to adopt the explicit JOIN, because has a lot of performance advantage in general than the IN (i.e. prefer the second type of query instead of the second type of query).

Upvotes: 2

Vivek
Vivek

Reputation: 436

As per my understanding, you have all REFNO which you can pass to sql IN clause like,

select * from  TO_SALES_INVO 
where REFNO IN ('201582255', '20190525', '2018525', ...)

Upvotes: 1

GMB
GMB

Reputation: 222582

You can use the IN operator:

SELECT * FROM TO_SALES_INVO WHERE REFNO IN (201582255, 20190525, 2018525)

Note: if REFNO's datatype is numeric, then don't surround the values with single quotes.

Upvotes: 4

Bill the Lizard
Bill the Lizard

Reputation: 405955

If they're not in a sequence or range, then you can list them out in an IN condition.

select * from  TO_SALES_INVO 
where REFNO in ('201582255', '20190525', '2018525', ...)

If you can get just the reference numbers that you want in a query, then you can use that as a subquery instead of listing them all out.

select * from  TO_SALES_INVO 
where REFNO in (select REFNO from ...)

Upvotes: 2

Related Questions