no case statement in views + sql 7

I have a query which will be a subquery that I want to use a case statement on. (It is an outer join and I want to substitue nulls for a specific value.) I can make it work in query analyzer and can also make it work in a stored procedure, but I get a message that the case statement is not supported in views. So, I tried to make a function ,but they are not supported in SQL 7, and apparently stored procedures cannot be called inline in views, either. I can retrieve the desired recordset with exec sp_name, but I need the recordset to be used in another query and cannot figure out how to use the results from a Stored procedure as a subquery in a view. Any help is greatly appreciated.

Thanks,

Tman2Case statements are supported in views, but the GUI query designer built into Enterprise Manager cannot parse and graphically display statements using CASE. You can write your query using the Query Analyzer tool instead (no serious TSQL programmer uses the GUI).
Regardless, you do not need a CASE statement to substitute NULL for a specific value. Use the NULLIF() function instead.|||I was able to create a view in query designer that uses case, but it does not function correctly. (It defaults all values to 0). However, if I use the exact same SQL statement in query designer without it being a view, it works. (ie. create view sQL statement to create view, select * from view produces erroneous results, but SQL statement in query designer works.)

I will try the nullif function you mentioned, but htat will only work for this particular cases, and there will be many instances where I will need the case statement. Any ideas what to do? This only seems to be causing a problem in SQL 7, not the newer versions...|||Post your code if you want somebody here to review it. There could be a problem with default connection settings in Query Analyzer. The SET CONCAT NULL option, for instance, has bitten me several times...|||It probably is something like that. WHEN NULL was not identifying nulls as nulls. Any ideas, or this there something wrong with my syntax (ie must use isnull like in VB)? Thanks for all your help. I have it working now, (really can just use isnull without the case statement at all) but for academic purposes, I would be intrested to know if there is an option that changes this. I did a little research on the option you pointed out above, what a #!@.* that must have been to find...

Thanks again.

NHibernate, Views and SQL Server

Hi there,

First thing off, I'd like to know what Views are useful for? I thing it can speeds select query containing multiple join, but I'm not even sure if that's true.

Second, assuming that Views speeds thing up for SELECT query, can it be used by NHibernate when restoring object from their persistence store (in this case, SQL 2k5) ? If not, can they be (views) of any use if we use NHibernate?

Third, would it be better to create our crystal report using Views to improve performance?

Thanks for anyone who participates in this thread! :-)Views rarely speed anything up (partioning is a special case). Indexed views can help by providing a second pseudo clustered index. Generally views are to make life easy rather than faster, I'd avoid them wherever possible.