SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 IIF(access) to SQL query
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

ema
Starting Member

6 Posts

Posted - 03/02/2006 :  13:58:04  Show Profile
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

USA
7423 Posts

Posted - 03/02/2006 :  14:11:04  Show Profile  Visit jsmith8858's Homepage
That IIF() expression is poorly written; are you aware that it always returns the TRUE part?
Go to Top of Page

ema
Starting Member

6 Posts

Posted - 03/02/2006 :  15:19:01  Show Profile
Didn't know that. Actually I am just trying to convert that access query(which is working) into sql query...please help
Thanks,
Ema
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/02/2006 :  16:01:12  Show Profile  Visit jsmith8858's Homepage
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?
Go to Top of Page

ema
Starting Member

6 Posts

Posted - 03/02/2006 :  16:07:20  Show Profile
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.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/02/2006 :  16:17:56  Show Profile  Visit jsmith8858's Homepage
Ema -- did I tell you yet that the Access query ALWAYS RETURNS THE TRUE PART ???
Go to Top of Page

ema
Starting Member

6 Posts

Posted - 03/02/2006 :  16:31:50  Show Profile
So, what do I need to do to make it work the same way?
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/02/2006 :  16:34:49  Show Profile  Visit jsmith8858's Homepage
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?
Go to Top of Page

ema
Starting Member

6 Posts

Posted - 03/02/2006 :  16:37:34  Show Profile
ok, will try that. Thank you,
Ema
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/02/2006 :  16:42:40  Show Profile  Visit jsmith8858's Homepage
Good luck!
Go to Top of Page

ema
Starting Member

6 Posts

Posted - 03/02/2006 :  16:46:33  Show Profile
This is working the same way, thanks a lot.
Ema
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000