Need equivalent of iif on sql server

[JoJo Sharp via AccessMonster.com]

Please help with using Access’ iif on SQL server. The following query works
perfect on Access but NOT on SQL server. I tried to use CASE to replace iif
to no avail, possibly because iif stmt could be equivalent to null. Thanks a
lot in advance!!

SELECT DISTINCT tblOrder.id,
tblOrder.dt_ordered, tblPlan.plan,
iif(tblPlan.id is Not Null,'y','n') AS covered
FROM tblOrder LEFT JOIN tblPlan
ON (tblOrder.dt_ordered Between tblPlan.start_dt And tblPlan.end_dt)
AND (tblOrder.id=tblPlan.id);

The tables and sql results sample as following:
tblOrder tblPlan
id dt_ordered start end plan
1 2/5/2004 1 1/1/2004 2/28/2004 HP
1 3/5/2004 3 6/1/2004 12/31/9999 PC
2 4/5/2004
3 5/5/2004

Desirable sql result from the qry
id dt_filled plan covered
1 2/5/2004 HP y
1 3/5/2004 n
2 4/5/2004 n
3 5/5/2004 n

I used iif statement to create a new field called “covered” and mark it as
following:

If tblOrder has matching id in tblPlan and its dt_ordered is between
tblPlan’s start and end date, then mark field “covered” as ‘Y’;
If tblOrder has mataching id in tblPlan BUT its dt_ordered NOT between
tblPlan’s start and end date, then mark field “covered” as ‘N”;
If tblOrder has NO mataching id in tblPlan then mark field “covered” as ‘y”;

Please help!

 

[Brendan Reynolds]

You were on the right track with CASE. There are two forms of CASE in T-SQL,
the 'Simple' form and the 'Searched' form. You were probably trying to use
the 'Simple' form. Here's an example using the 'searched' form ...

SELECT CASE WHEN Address2 IS NULL THEN 'y' ELSE 'n' END AS TestCol
FROM dbo.Address

See the 'CASE' topic in SQL Server Books Online for details.

 

[JoJo Sharp via AccessMonster.com]

Thanks for your reply.
Sorry, I forgot to mention an important element. The qry is run on Access
with tblOrder on an Access db and tabPlan on SQL server in a data warehouse.
As a result, my iif statement is ignored by Access and I don't seem to be
able to use CASE due to Access does not recognize it. Am I stuck? Thank you.

You were on the right track with CASE. There are two forms of CASE in T-SQL,
the 'Simple' form and the 'Searched' form. You were probably trying to use
the 'Simple' form. Here's an example using the 'searched' form ...

SELECT CASE WHEN Address2 IS NULL THEN 'y' ELSE 'n' END AS TestCol
FROM dbo.Address

See the 'CASE' topic in SQL Server Books Online for details.

Please help with using Access’ iif on SQL server. The following query
works

[quoted text clipped - 37 lines]

Please help!

 

[Brendan Reynolds]

If it's a Jet query run from within Access then you should be able to use
IIF - the fact that one of the tables is a linked SQL Server table shouldn't
prevent you from using IIF. Is the field that you are checking for Null a
text field? If so, perhaps the reason that your IIF appears to be ignored
may be that the field doesn't contain any Null values - fields that appear
to be empty may contain empty strings rather than Null values.

--
Brendan Reynolds (MVP)

Thanks for your reply.
Sorry, I forgot to mention an important element. The qry is run on Access
with tblOrder on an Access db and tabPlan on SQL server in a data
warehouse.
As a result, my iif statement is ignored by Access and I don't seem to be
able to use CASE due to Access does not recognize it. Am I stuck? Thank
you.

You were on the right track with CASE. There are two forms of CASE in
T-SQL,
the 'Simple' form and the 'Searched' form. You were probably trying to use
the 'Simple' form. Here's an example using the 'searched' form ...

SELECT CASE WHEN Address2 IS NULL THEN 'y' ELSE 'n' END AS TestCol
FROM dbo.Address

See the 'CASE' topic in SQL Server Books Online for details.

Please help with using Access’ iif on SQL server. The following query
works

[quoted text clipped - 37 lines]

Please help!

 

[JoJo Sharp via AccessMonster.com]

Thanks for pointing out what I don’t know. I see iif is not the problem here.


You are right that the field checked (plan.id) is a text field. It is NOT
NULL in the sense that the id is found in both tables. But on the other hand
it is NULL in the sense that the same id’s dt_ordered field IS NOT FOUND in
tblPlan as defined by Left Join On stmt (because it is not covered by plan).
Maybe this is where Jet qry is confused and just completely drop such
transactions. But since it is a LEFT JOIN, all transactions in tblOrder (left
tbl) should be in the final result and marked with “y” or “n”. It works fine
when I tested it on Access.

Right now I solved the jet qry problem by doing 2nd qry where I make tblOrder
to LEFT JOIN the qry result ON id and dt_ordered to find nulls. Those nulls
are the ones that have ids found in both tbls but with dt_ordered NOT FOUND
in tblPlan.

Any input would be greatly appreciated. Thanks!

If it's a Jet query run from within Access then you should be able to use
IIF - the fact that one of the tables is a linked SQL Server table shouldn't
prevent you from using IIF. Is the field that you are checking for Null a
text field? If so, perhaps the reason that your IIF appears to be ignored
may be that the field doesn't contain any Null values - fields that appear
to be empty may contain empty strings rather than Null values.

Thanks for your reply.
Sorry, I forgot to mention an important element. The qry is run on Access

[quoted text clipped - 19 lines]

 

[JoJo S via AccessMonster.com]

Well, I did further testing. When I test on a single record, the qry
generates the right info. But when I retreive data on about 150k records, the
qry drops info (it failed to retrieve the ones that have id identified but
havedt_ordered not between the plan start and end date). I think the qry is
fine and this is some non-sql-jet qry problem. I'll just let it be and just
do a 2nd qry to compensate this.

Thank you very much for your help!!

Thanks for pointing out what I don’t know. I see iif is not the problem here.

You are right that the field checked (plan.id) is a text field. It is NOT
NULL in the sense that the id is found in both tables. But on the other hand
it is NULL in the sense that the same id’s dt_ordered field IS NOT FOUND in
tblPlan as defined by Left Join On stmt (because it is not covered by plan).
Maybe this is where Jet qry is confused and just completely drop such
transactions. But since it is a LEFT JOIN, all transactions in tblOrder (left
tbl) should be in the final result and marked with “y” or “n”. It works fine
when I tested it on Access.

Right now I solved the jet qry problem by doing 2nd qry where I make tblOrder
to LEFT JOIN the qry result ON id and dt_ordered to find nulls. Those nulls
are the ones that have ids found in both tbls but with dt_ordered NOT FOUND
in tblPlan.

Any input would be greatly appreciated. Thanks!

If it's a Jet query run from within Access then you should be able to use
IIF - the fact that one of the tables is a linked SQL Server table shouldn't

[quoted text clipped - 8 lines]