round function in sql statement not executing

Hi , I'm new to t-sql so bear with me cos this might be a stupid question.

The code below

SELECT [CODE], [PERIOD], [OPERATOR], [RATE], Round(IIF([OPERATOR]='/',[RATE],1/[RATE]),3) AS EXCHRATE
INTO tbl_currency_test
FROM [SUNDB].[dbo].[SSRFCNV]
WHERE [CODE]='NGN' AND [PERIOD]<>0

always gives me the following error message

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

However when i remove the round() code from the statement it executes fine.

Can someone please please let me know what i'm doing wrong here....

Cheers

Hi,

The IIF is for MDX statements. For SQL statements, you need to use the CASE keyword.

SELECT [CODE], [PERIOD], [OPERATOR], [RATE], Round(CASE WHEN [OPERATOR]='/' THEN [RATE] ELSE 1/[RATE] END,3) AS EXCHRATE
INTO tbl_currency_test
FROM [SUNDB].[dbo].[SSRFCNV]
WHERE [CODE]='NGN' AND [PERIOD]<>0

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Here is the definition for the CASE keyword:

http://msdn2.microsoft.com/en-us/library/ms181765.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Dude,

That was pure ....

Thanks a mil