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_curThanks.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 ENDSo 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_curand 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_curBut, 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 |
|
|
|