October 10, 2003 at 12:49 am
I have a Table named "Table1" where I have about 7 columns.
"Col1" and "Col2" are 2 such column names in this table "Table1".
My query goes like this
"Select * from Table1 where Col1 > 100"
but if for a few rows Col1 has no value(or null/empty) then for that particular row I want to check on Col2 instead of Col1
ie for these records where Col1 has no value the query should be
"Select * from Table1 where Col2 > 100"
If both Col1 and Col2 are null then leave that record.
Thank you.
October 10, 2003 at 6:06 am
SELECT *
FROM Table1
WHERE COALESCE(Col1, Col2) > 200
--Jonathan
--Jonathan
October 10, 2003 at 7:36 am
Thanks its working in sql but what if I change to a access database. COALESCE doesn't seem to work in access.
October 10, 2003 at 8:39 am
Not so elegant but more generic.
SELECT * FROM Table1
WHERE Col1 > 100
UNION
SELECT * FROM Table1
WHERE Col1 IS NULL AND Col2 > 100
Hint: You could use an OR statement and put both criteria in a single where clause - but that causes a table or index scan.
Guarddata-
October 10, 2003 at 9:36 am
quote:
Thanks its working in sql but what if I change to a access database. COALESCE doesn't seem to work in access.
I know no Access, but COALESCE (unlike ISNULL) is standard SQL. How about:
SELECT *
FROM Table1
WHERE Col1 > 200 OR (Col1 IS NULL AND Col2 > 200)
or:
SELECT *
FROM Table1
WHERE CASE WHEN Col1 IS NOT NULL THEN Col1 ELSE Col2 END > 200
--Jonathan
--Jonathan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply