iif functions in views / procedures 
Author Message
 iif functions in views / procedures
Hi,

Very very basic question but when creating views / procedures, does SQL
server have an IIF function?

For example, I have an Access 2000 query which I am attempting to recreate
in SQL Server 2000. The query has a couple of fields that use IIF functions.

Does SQL server has an equivilient feature or do I have to simulate the IIF
function as a UDF?

Advice is gratefully received.

Regards

CS



Sat, 30 Jul 2005 00:36:43 GMT
 iif functions in views / procedures

You have to create a UDF
use the Case statement

On Mon, 10 Feb 2003 16:36:43 -0000, "Chris Strug"

Quote:

>Hi,

>Very very basic question but when creating views / procedures, does SQL
>server have an IIF function?

>For example, I have an Access 2000 query which I am attempting to recreate
>in SQL Server 2000. The query has a couple of fields that use IIF functions.

>Does SQL server has an equivilient feature or do I have to simulate the IIF
>function as a UDF?

>Advice is gratefully received.

>Regards

>CS



Sat, 30 Jul 2005 00:47:01 GMT
 iif functions in views / procedures
Checkout for CASE - WHEN clause in SELECT statement syntax
of SQL server.

--Vishal

Quote:
>-----Original Message-----
>Hi,

>Very very basic question but when creating views /

procedures, does SQL
Quote:
>server have an IIF function?

>For example, I have an Access 2000 query which I am

attempting to recreate
Quote:
>in SQL Server 2000. The query has a couple of fields that
use IIF functions.

>Does SQL server has an equivilient feature or do I have
to simulate the IIF
>function as a UDF?

>Advice is gratefully received.

>Regards

>CS

>.



Sat, 30 Jul 2005 00:46:43 GMT
 iif functions in views / procedures


Quote:
> You have to create a UDF
> use the Case statement

Rather a pity, I'm going to end  up with an awful lot of UDF's...

Thank you for your reply.

Regards

CS



Sat, 30 Jul 2005 00:53:03 GMT
 iif functions in views / procedures

Quote:
>-----Original Message-----
>Hi,

>Very very basic question but when creating views /

procedures, does SQL

Quote:
>server have an IIF function?

This is a very frequently asked question. SQL Server has a
CASE expression which accomplishes the same thing as IIF
().

Select iif(<boolean expression>,<true value>, <false
value>)

would be rewritten as:

Select CASE WHEN <boolean expression> THEN <value when
true> ELSE <value when false> END

Look it up in BOL - you wll find that it's much more
powerful than iif().

HTH,
Bob Barrows



Sat, 30 Jul 2005 00:55:51 GMT
 iif functions in views / procedures
no need for a UDF, just use CASE

--
Keith, SQL Server MVP

Quote:



> > You have to create a UDF
> > use the Case statement

> Rather a pity, I'm going to end  up with an awful lot of UDF's...

> Thank you for your reply.

> Regards

> CS



Sat, 30 Jul 2005 01:39:32 GMT
 iif functions in views / procedures
but he wanted it in a View, cannot do a Case in a view

On Mon, 10 Feb 2003 08:55:51 -0800, "Bob Barrows"

Quote:

>>-----Original Message-----
>>Hi,

>>Very very basic question but when creating views /
>procedures, does SQL
>>server have an IIF function?

>This is a very frequently asked question. SQL Server has a
>CASE expression which accomplishes the same thing as IIF
>().

>Select iif(<boolean expression>,<true value>, <false
>value>)

>would be rewritten as:

>Select CASE WHEN <boolean expression> THEN <value when
>true> ELSE <value when false> END

>Look it up in BOL - you wll find that it's much more
>powerful than iif().

>HTH,
>Bob Barrows



Sat, 30 Jul 2005 04:43:19 GMT
 iif functions in views / procedures
You can. Just don't use the EM interfaces...

--
- Anith
(Please respond only to newsgroups)



Sat, 30 Jul 2005 04:47:45 GMT
 iif functions in views / procedures
First of all, thanks to everyone for their help - spending your time on such
a trivial question is greatly appreciated.

Taking my existing (simplified) SQL form access 2000

SELECT IIF([Status]="Empty ex NMUK","NMUK","Empty") As NullDest FROM STOCK;

I have rewritten it as

SELECT  CASE WHEN [Status] = 'Empty ex NMUK' THEN 'NMUK' ELSE 'Empty' END AS
NullDest FROM STOCK

in SQl server.

The curious thing is that attempting to execute this statement gives me the
error message 'The Query Designer does not support the CASE SQL construct.'
and it then works.... Of course, nothing appears in the Grid Pane or the
Diagram Pane so I guess I'm going to have to write all the views containing
CASE's by hand but it's a start.

Am I right in assuming that this will work as per any other view?

Finally, what do you mean by "EM Interfaces".

Thanks again for all your help.

CS


Quote:
> You can. Just don't use the EM interfaces...

> --
> - Anith
> (Please respond only to newsgroups)



Sat, 30 Jul 2005 18:36:15 GMT
 iif functions in views / procedures


Quote:
> First of all, thanks to everyone for their help - spending your time on
> such
> a trivial question is greatly appreciated.

> Taking my existing (simplified) SQL form access 2000

> SELECT IIF([Status]="Empty ex NMUK","NMUK","Empty") As NullDest FROM
> STOCK;

> I have rewritten it as

> SELECT  CASE WHEN [Status] = 'Empty ex NMUK' THEN 'NMUK' ELSE 'Empty' END
> AS
> NullDest FROM STOCK

> in SQl server.

> The curious thing is that attempting to execute this statement gives me
> the
> error message 'The Query Designer does not support the CASE SQL
> construct.'
> and it then works.... Of course, nothing appears in the Grid Pane or the
> Diagram Pane so I guess I'm going to have to write all the views
> containing
> CASE's by hand but it's a start.

> Am I right in assuming that this will work as per any other view?

> Finally, what do you mean by "EM Interfaces".

He means the Query Designer you are attempting to use. You can create this
view by using Query Analyzer  as follows:

CREATE VIEW myviewname AS
SELECT  CASE WHEN [Status] = 'Empty ex NMUK' THEN 'NMUK' ELSE 'Empty' END AS
NullDest FROM STOCK

This will work fine. The good news is that this will force you to focus on
learning to write SQL instead of depending on visual tools which may or may
not give you the optimum sql statements.

HTH,
Bob Barrows



Sat, 30 Jul 2005 19:52:48 GMT
 iif functions in views / procedures
Hi,

I'm not sure whether you still need this information, but
anyway:

Quote:
>Very very basic question but when creating views /

procedures, does SQL

Quote:
>server have an IIF function?

I found the TSQL case when command working pretty much
like iif, but the syntax is somewhat different to Access-
Iif.

Regards,

Roland{*filter*}



Tue, 20 Sep 2005 22:09:01 GMT
 iif functions in views / procedures
IIF's are not part of SQL Servers syntax, you need to use the CASE expression instead.

SELECT CASE WHEN Col1 = 'Y' THEN 1 ELSE 0 END FROM <<YourTable>>

Check out CASE in BooksOnLine.

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions

Quote:

> Hi,

> I'm not sure whether you still need this information, but
> anyway:

> >Very very basic question but when creating views /
> procedures, does SQL
> >server have an IIF function?

> I found the TSQL case when command working pretty much
> like iif, but the syntax is somewhat different to Access-
> Iif.

> Regards,

> Roland{*filter*}



Wed, 21 Sep 2005 14:47:21 GMT
 
 [ 12 post ] 

 Relevant Pages 

1. iif function in views

2. IIF function inside a view

3. IIF function in view ???

4. How to use IIF function in a view

5. IIF or CASE type function in SQL views

6. IIF FUNCTION IN A VIEW

7. Simulating IIF function in View

8. IIF Function not recognized as valid function

9. Using functions in views and procedures

10. maximum stored procedure,function,trigger, or view nesting level exeeeded

11. How can I view the signature of the function/procedure

12. dependecies between procedures, functions, views ...


 
Powered by phpBB® Forum Software