CODEWITHSUNDEEP
sqlsql-serveroracle-database
Technogix
Technogix

Reputation: 77

How to rewrite SQL query without IN clause

Schema:

Table A: AID(PK), RECEIVE_DATE
Table B: BID(PK), AID(FK), MESSAGE, ITEMID, ITEMTYPE

Tables A-to-B have a one-to-many mapping.

Here is a working SQL query (in SQL Server) to find out the latest message grouped by ITEMID i.e for different ITEMID (of ITEMTYPE say as 'XYZ').

 SELECT 
     b.MESSAGE, b.ITEMID
 from a
 inner join b on b.aid = a.aid AND b.ITEMTYPE = 'XYZ'
 where a.receive_date in (select max(receive_date)
                                             from a a1
                                             inner join b b1 on b1.aid = a1.aid   
                                             where b1.itemid = b.itemid
                                             );

How can we rewrite this SQL query without IN clause [also without rownumber concept in use], as ORACLE is having restriction for IN clause. Getting java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 for above expression.

Upvotes: 1

Views: 2470

Answers (2)

e_i_pi
e_i_pi

Reputation: 4820

EXISTS and IN tend to be interchangeable, and EXISTS performs better in some engines (not sure about Oracle) due to the fact that it returns true on the first match, rather than generating a subset and checking against that. I'm not familiar with Oracle, but I imagine you could use the following to circumvent your 1000 row limit on IN:

SELECT 
    b.MESSAGE, b.ITEMID
from a
inner join b on b.aid = a.aid AND b.ITEMTYPE = 'XYZ'
where exists (
    SELECT 1
    from a a1
    inner join b b1 on b1.aid = a1.aid   
    where b1.itemid = b.itemid
    having MAX(a1.receive_date) = a.receive_date
)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520998

It isn't clear to me why you are getting ORA-01795. Your subquery only selects a max value, which should be a single value. In addition, the 1000 value limit only applies to a list of literals, not a subquery. In any case, you could rephrase this query using a join instead of WHERE IN:

SELECT 
    b.MESSAGE,
    b.ITEMID
FROM a
INNER JOIN b
    ON b.aid = a.aid AND b.ITEMTYPE = 'XYZ'
INNER JOIN
(
    SELECT
        b1.itemid,
        MAX(receive_date) AS max_receive_date
    FROM a a1
    INNER JOIN b b1
        ON b1.aid = a1.aid   
    GROUP BY b1.itemid
) t
    ON b.itemid = t.itemid
WHERE a.receive_date = t.max_receive_date

Upvotes: 3

Related Questions