"Hugo Kornelis" wrote:

> On Wed, 23 Nov 2005 12:16:13 -0800, James_101 wrote:
>
> >I am replicating a SELECT statement from Microsoft Access to SQL Server.
> >
> >In Access, the SELECT statement used in a query is:
> >
> >AS SELECT Unit AS Facility, IIf([Mod_Compl_Intro]="1" Or
> >[Mod_Compl_Intro]="-1","Complete","-") AS Introduction
> >FROM User_Data_Table
> >
> >If a record contains 1 or -1 in the field Mod_Compl_Intro, then the query
> >returns the word Complete or -. The query runs OK in Access.
> >
> >When I include this statement with other code in Query Analyzer, I get this
> >message:
> >
> >Server: Msg 195, Level 15, State 10, Line 3
> >'IIf' is not a recognized function name.
> >
> >When I search for IIf in SQL Server Books Online, I get the standard
> >definition for IIf: IIf(«Logical Expression», «String Expression1», «String
> >Expression2»). The heading at the top of the page is Analysis Services
> >(which I have installed).
> >
> >Is the IIf function not permitted in Query Analyzer?
>
> Hi Jim,
>
> Though the IIf function is supported in Analysis Services, it is not
> part of either standard SQL nor the Transact-SQL extensions. Queries
> executed on the server should use standard SQL or T-SQL syntax.
>
> The SQL equivalent of IIf is CASE:
>
> SELECT Unit AS Facility,
> CASE WHEN Mod_Colmpl_Intro IN (1, -1)
> THEN 'Complete'
> ELSE '-'
> END AS Introduction
> FROM User_Data_Table
>
> As you see, I've also replaced the double quotes with single quotes.
> Single quotes are the standard way to delimit string constants in SQL.
> Double quotes are used to delimit identifiers that don't follow the
> rules for identifiers. T-SQL also permits square brackets as identifier
> delimiters, BTW.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)