|
Home > Archive > MS SQL Server New Users > November 2005 > "If" statement in Query Analyzer
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
"If" statement in Query Analyzer
|
|
| James_101 2005-11-23, 8:24 pm |
| 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(& #91;Mod_Compl_Intro]
="1" Or
& #91;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?
Thanks.
Jim
| |
| Raymond D'Anjou 2005-11-23, 8:24 pm |
|
"James_101" < James101@discussions
.microsoft.com> wrote in message
news:8721FEF0-6AF8-4EC1-B50E- 222A61FD47BA@microso
ft.com...
>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(& #91;Mod_Compl_Intro]
="1" Or
> & #91;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?
>
> Thanks.
>
> Jim
No, there is no IIF in SQL server.
Look up CASE in Books Online.
Use single ' in SQL.
AS SELECT Unit AS Facility,
CASE WHEN (& #91;Mod_Compl_Intro]
='1' Or & #91;Mod_Compl_Intro]
='-1' then 'Complete'
ELSE '-' END AS Introduction
FROM User_Data_Table
| |
| Hugo Kornelis 2005-11-23, 8:24 pm |
| 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(& #91;Mod_Compl_Intro]
="1" Or
>& #91;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)
| |
| James_101 2005-11-23, 8:24 pm |
| Raymond and Hugo: thanks for the help.
Jim
"Hugo Kornelis" wrote:
> On Wed, 23 Nov 2005 12:16:13 -0800, James_101 wrote:
>
>
> 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)
>
|
|
|
|
|