SQL Server - Case function in SQL Vs iff function in access

Asked By sahirkalee

29-Jan-07 05:22 AM

Hi I'm new to SQL and I'm having an issue with the Case function. I=20
have a field which has the Amount in various currencies. The problem=20
is that Yen has no decimal places so the flat files I use to pickup=20
the data does not include any leading zero.

e=2Eg. For =A3100 the field would show as 10000 where as
Yen 100 would show as 100.

In access I had the following query

AMOUNT: IIf([Gw078p]![Curr_Code]=3D'JPY',[Gw078p]![Bal_Orig_Curr],
[Gw078p]![Bal_Orig_Curr]/100)

So if the currency was Japanese Yen I would take the figure from the=20
field otherwise just divide by 100.

How would I do something similar in SQL 2000. Any help would be=20
appreciated. Thanks.

29-Jan-07 05:48 AM

SELECT ...
CASE WHEN CurrCode = 'JPY' THEN Bal_Orig_Curr ELSE Bal_Orig_Curr/100 END
FROM ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


Hi I'm new to SQL and I'm having an issue with the Case function. I
have a field which has the Amount in various currencies. The problem
is that Yen has no decimal places so the flat files I use to pickup
the data does not include any leading zero.

e.g. For £100 the field would show as 10000 where as
Yen 100 would show as 100.

In access I had the following query

AMOUNT: IIf([Gw078p]![Curr_Code]='JPY',[Gw078p]![Bal_Orig_Curr],
[Gw078p]![Bal_Orig_Curr]/100)

So if the currency was Japanese Yen I would take the figure from the
field otherwise just divide by 100.

How would I do something similar in SQL 2000. Any help would be
appreciated. Thanks.

29-Jan-07 06:18 AM

Thanks for replying, but I tried the in query analyser and I get the=20
message:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.

SELECT  *
CASE WHEN CurrCode =3D 'JPY' THEN Bal_Orig_Curr ELSE Bal_Orig_Curr/100=20
END
FROM gw078p

I'm probably doing something really stupid here. Thanks again.


On 29 Jan, 10:48, "Tibor Karaszi"=20
http://www.solidqualitylearning.com/
g2000cwj.googlegroups.com...

29-Jan-07 06:39 AM

Its OK I was doing something stupid. It was Curr_code not Currcode.=20
Thanks for your help.

END
sphttp://www.solidqualityle...
27g2000cwj.googlegroups.com...