How to se IIF or Case statements in a view to create a column 
Author Message
 How to se IIF or Case statements in a view to create a column
Try using Query Analyzer

Quote:
> But I get the "The query designer does not support the case SQL construct"
> error

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Sat, 25 Oct 2003 03:31:46 GMT
 How to se IIF or Case statements in a view to create a column

SQL Server does not support IIF.
You need to use CASE.

SELECT 'Applicable' = CASE WHEN value1 IN ('y', 'n') THEN -1 ELSE 0 END
FROM SomeTable

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I have a view and I want to make a new column based upon the values in
> another column.

> Here is the IIF representaton of what I was trying to do:
> IIF(value1 = 'Y' or value2='N',-1,0) as Applicable

> It would not work.  Then I think I read that I can't use IIF in my View,
so
> I tried using the case statement:

> case when value1 = 'Y' or value1 = 'N' then -1 else 0 end as Applicable

> But I get the "The query designer does not support the case SQL construct"
> error

> Will someone tell me how do I do this?

> Thanks

> -C



Sat, 25 Oct 2003 03:30:59 GMT
 How to se IIF or Case statements in a view to create a column
I have a view and I want to make a new column based upon the values in
another column.

Here is the IIF representaton of what I was trying to do:
IIF(value1 = 'Y' or value2='N',-1,0) as Applicable

It would not work.  Then I think I read that I can't use IIF in my View, so
I tried using the case statement:

case when value1 = 'Y' or value1 = 'N' then -1 else 0 end as Applicable

But I get the "The query designer does not support the case SQL construct"
error

Will someone tell me how do I do this?

Thanks

-C



Sat, 25 Oct 2003 03:23:11 GMT
 How to se IIF or Case statements in a view to create a column
Don't use the query designer for this. It is (as you noticed) rather
limited. Create the view using TSQL CREATE VIEW from Query Analyzer.

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.


Quote:
> I have a view and I want to make a new column based upon the values in
> another column.

> Here is the IIF representaton of what I was trying to do:
> IIF(value1 = 'Y' or value2='N',-1,0) as Applicable

> It would not work.  Then I think I read that I can't use IIF in my View,
so
> I tried using the case statement:

> case when value1 = 'Y' or value1 = 'N' then -1 else 0 end as Applicable

> But I get the "The query designer does not support the case SQL construct"
> error

> Will someone tell me how do I do this?

> Thanks

> -C



Sat, 25 Oct 2003 03:33:32 GMT
 How to se IIF or Case statements in a view to create a column
If you have an older version of SQL in which QA doesn't allow view editing,
you can also double click on the view in Enterprise Mangler, in order to
edit the SQL directly and get around this 'feature'.

"Keith Kratochvil" <Keith.please email only when

Quote:
> Try using Query Analyzer

> > But I get the "The query designer does not support the case SQL
construct"
> > error



Sat, 01 Nov 2003 10:19:47 GMT
 How to se IIF or Case statements in a view to create a column
You can also run sp_helptext 'objectname'
or
You can grab the script from your favorite version control system, make the
changes, create the object within the database, and then check the file back
in to your favorite version control system.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> If you have an older version of SQL in which QA doesn't allow view
editing,
> you can also double click on the view in Enterprise Mangler, in order to
> edit the SQL directly and get around this 'feature'.

> "Keith Kratochvil" <Keith.please email only when


> > Try using Query Analyzer

> > > But I get the "The query designer does not support the case SQL
> construct"
> > > error



Sat, 01 Nov 2003 22:50:20 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Problem converting IIF to CASE statement

2. IIF or CASE type function in SQL views

3. Converting iif to case-statement

4. If statement in SQL7 View (IIF)

5. Case Statement Dependent On Another Case Statement Possible?

6. Create View Help - change column data type from Money(16,2) to FLOAT in the view

7. Complex View using Case Statement?

8. indexed views with case statement

9. how to view resulting SQl statement from conditional statemens (CASE)

10. Repeating CASE statement in the view

11. View in 7.0 doesn't support Case statement

12. Error w/ CASE statements in SQL view


 
Powered by phpBB® Forum Software