The "in" syntax
-
I'm trying to convert my application from Oracle to SqlServer and have run into an interesting problem. This is a valid statement in Oracle, but not in SQLServer. Has anyone run into this before???
select
table1.col_A,
table1.col_B
from table1
where (col_C, col_D) IN (value_1, value_2)
Any help or work-arounds would be helpful. Our app really relys on this and I would like to avoid changing everything!
Thanks in advance
-
I haven't done this in a while but doesn't
(col_c, col_d) In (val1, val2)
translate to
col_c IN (val1, val2) OR col_d IN (val1, val2)
which I may be wrong but if that is right then use the second form in SQL and will work.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
SQL Server can't do row comparisons (that is, multiple values compared to eachother). The example Antares686 showed will probably work, but I seem to recall from the standard that row comparisons are done from left to right, i.e. if col_c = val_1 but col_d <> val_2, then the comparison is still true, but not the other way round. I may very well be wrong on this though, so please try this example in Oracle (I'd love to see the results but I don't have an Oracle machine where I am right now):
create table foo (a int, b int);
insert into foo values (1, 2);
insert into foo values (3, 4);
insert into foo values (1, 2);
insert into foo values (1, 3);
insert into foo values (11, 2);
insert into foo values (11, 12);
insert into foo values (11, 22);
insert into foo values (3, 12);
-- SQL Server version
select * from foo
where a in (1)
or b in (2);
/* Results:
a b
----------- -----------
1 2
1 2
1 3
11 2
*/
-- Standard SQL version:
select * from foo
where (a, b) in (1, 2);
/* Results?
*/Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Edited by - chrhedga on 08/21/2002 06:43:35 AM
Edited by - chrhedga on 08/21/2002 06:47:18 AM
-
quote:
I seem to recall from the standard that row comparisons are done from left to right, i.e. if col_c = val_1 but col_d <> val_2, then the comparison is still true, but not the other way round.Well, as I kind of expected I had this mixed up in my head. Checked it up now, and I was only sort of right, or rather mostly wrong.
The standard says this:
RX = RY is TRUE if and only if RXi = RYi for all i.
RX and RY are rows, and RXi is the ith column of the row RX. What I was thinking of was this rule:
RX < RY is TRUE if and only if RXi = RYi for all i < n and RXn < RYn for some n.
Still interested in what Oracle gives as a result though.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
I tried this on Oracle now and it was as I thought, the results where:
a b
---
1 2
1 2
So, the correct SQL Server version of the Oracle example you gave would be:
SELECT *
FROM TABLE1
WHERE col_C IN value_1
AND col_D IN value_2
But, this will only work the specific example you gave, which could also be written like this: (col_C, col_D) = (value_1, value_2)
However, a row comparison with the IN operator can of course have many rowvalues to compare against (maybe that was what you meant):
select * from foo where (a, b) IN ((1, 2), (11, 12));
A B
------ ----------
1 2
1 2
11 12To produce this you would need to execute this query in SQL Server:
select * from foo
where a = 1
and b = 2
or a = 11
and b = 12;
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply