CODEWITHSUNDEEP
sqlsyntax
Weckar E.
Weckar E.

Reputation: 777

Reusing result of ANY in SQL

I've got a bit of SQL much like the following:

(...) where usr.USR_START > ANY (1,5,9) AND usr.USR_END < ANY (1,5,9)

Ultimately, I'd really like USR_START and USR_END to be compared to the same value, but I see no way to 'store' the result (the 'pick') of the ANY clause. As it is - as it should - it is just looking at any value from the list for each individually, and these do not need to match.

An obvious option to me seemed:

(...) where usr.USR_START > ANY (1,5,9) > usr.USR_END

but that kind of syntax just does not work in SQL. Is there any way to really tackle this?

Upvotes: 0

Views: 40

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239734

If your database system supports row value constructors, the cleanest I can think if is something like this:

declare @t table (USR_START int not null,USR_END int not null)
insert into @t(USR_START,USR_END) values (6,4)

select * from @t usr
where EXISTS (
    select * from (VALUES (1),(5),(9)) t(u) where usr.USR_START > u and usr.USR_END < u)

(Also using SQL Server syntax for the table variable)

Upvotes: 2

Related Questions