IIF(access) to SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

IIF(access) to SQL query

Author

Topic

ema
Starting Member

6 Posts

Posted - 2006-03-02 : 13:58:04

I know that in SQL, does not work. I need to use "Sum (CASE", but I have trouble using that in query, can you help to make this sql query? (SQL Server 2000)

select ... Sum(IIf([ItmMrp]="1" Or "3" Or "4" Or "8" Or "9",([Dmd2]+[Dmd3]),0)) AS ProjUsageStock FROM Itm_forecast_cur

Thanks.
Ema

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 14:11:04

That IIF() expression is poorly written; are you aware that it always returns the TRUE part?

ema
Starting Member

6 Posts

Posted - 2006-03-02 : 15:19:01

Didn't know that. Actually I am just trying to convert that access query(which is working) into sql query...please help
Thanks,
Ema

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 16:01:12

Is that the actual IIF() expression? If so, and it is returning the intended results, remove the entire IIF() part because it is not doing anything!

As for the SQL translation, do you want what you think it *should* be doing, or what it is *actually* doing?

Converting IIF() to CASE is pretty easy to do:

IIF(Condition, TruePart, FalsePart)

translates almost exactly to:

CASE WHEN Condition THEN TruePart ELSE FalsePart END

So what problems are you having? What have you tried?

ema
Starting Member

6 Posts

Posted - 2006-03-02 : 16:07:20

I tried this:
select ...Sum(Case [ItmMrp] when '1' then ([Dmd2]+[Dmd3])
when '3' then ([Dmd2]+[Dmd3])
when '4' then ([Dmd2]+[Dmd3])
when '8' then ([Dmd2]+[Dmd3])
when '9' then ([Dmd2]+[Dmd3]) else 0 end) AS ProjUsageStock from Itm_forecast_cur

and it doesn't return me the same results as the access query.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 16:17:56

Ema -- did I tell you yet that the Access query ALWAYS RETURNS THE TRUE PART ???

ema
Starting Member

6 Posts

Posted - 2006-03-02 : 16:31:50

So, what do I need to do to make it work the same way?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 16:34:49

If you want it do do what the Access query is currently doing (which is ignoring the IIF() completely, as I've already mentioned at least twice) you don't need a CASE at all:

Select SUM([Dmd2] +[Dmd3]) AS ProjUsageStock from Itm_forecast_cur

But, again, I suspect that the Access query SHOULD be evaluating the condition properly but was written wrong. You need to find out what it should be doing, and do it correctly.

Does that make sense?

ema
Starting Member

6 Posts

Posted - 2006-03-02 : 16:37:34

ok, will try that. Thank you,
Ema

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 16:42:40

Good luck!

ema
Starting Member

6 Posts

Posted - 2006-03-02 : 16:46:33

This is working the same way, thanks a lot.
Ema