Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
In SQL how do I compare a number against a result of a nested query?
I have a query that is something like this:
SELECT field1, field2 FROM table1
WHERE field3 >= (SELECT field4 FROM table2 WHERE field5 IS NOT NULL)
field3 is a field of type integer and so is field4, and i know for sure that nested query returns only 1 row... However, the comparison never runs correctly and i can't figure out why. field3 is definitely greater than or equals to the returned field4, but runtime SQL never properly evaluates the comparison. What am I doing wrong?
2 Answers
- 1 decade agoFavorite Answer
maybe you could try using "isnull()" in your 2nd (nested) query. isnull will let you replace a null with a different value, say zero. then you can be assured you're comparing apples to apples (integers to integers, in your case).
SELECT field1, field2 FROM table1
WHERE field3 >= (SELECT isnull(field4,0) FROM table2)
you could also try using cast or convert to make sure the records coming in from the nested query are coming in as integers into your outter query
SELECT field1, field2 FROM table1
WHERE field3 >= (SELECT cast (field4 as integer) FROM table2 WHERE field5 IS NOT NULL)
i don't have access to a sql table to test either of my theories, sorry. good luck!
- 1 decade ago
There doesn't appear to be any reason why the query won't work. Can you paste the error? If not error, paste some sample data and also add the scripted tables for table1 and table 2.
