I'm trying to create a view that has a case statement in it, but it is not allowing me...it says it is not supported. However there are other views in this db with case statements that work just fine. It seems it won't allow me to create a new one..anyone know why??
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Case statement in view?
- Thread starter nuVBer
- Start date
- Thread starter
- #3
OK the database is SQL Server 7.0.
The view looks like this:
SELECT comp_sk, comp_name, tow_id = CASE WHEN tow_id IN ('', 'unknown') OR tow_id IS NULL THEN 'unknown' ELSE tow_id END, curr_status
FROM vc_wellcomp
WHERE curr_status not in ('S')
A similar ( in fact the original view that I'm trying to simulate ) is this one that works fine:
SELECT comp_sk, comp_name, tow_id = CASE WHEN tow_id IN ('', 'unknown') OR tow_id IS NULL THEN 'unknown' ELSE tow_id END
FROM vc_wellcomp
The view looks like this:
SELECT comp_sk, comp_name, tow_id = CASE WHEN tow_id IN ('', 'unknown') OR tow_id IS NULL THEN 'unknown' ELSE tow_id END, curr_status
FROM vc_wellcomp
WHERE curr_status not in ('S')
A similar ( in fact the original view that I'm trying to simulate ) is this one that works fine:
SELECT comp_sk, comp_name, tow_id = CASE WHEN tow_id IN ('', 'unknown') OR tow_id IS NULL THEN 'unknown' ELSE tow_id END
FROM vc_wellcomp
I'm afraid that didn't help. I can create a SQL Server 7 table with these columns, insert some data, and successfully run the query that's failing for you. I can also create a view based on your query.
Is there any chance you had some minor syntax error when you tried to create your view?
I'm not sure if it makes a difference, but what is the structure of your table? In particular what are the datatypes, length and nullability of the columns in your query?
Is there any chance you had some minor syntax error when you tried to create your view?
I'm not sure if it makes a difference, but what is the structure of your table? In particular what are the datatypes, length and nullability of the columns in your query?
tlbroadbent
MIS
Are you using the View Designer in Enterprise Manager to create the view with the CASE function? If so, use SQL Server Query Analyzer to create the view. The problem is not SQL. The problem exists the View Designer in SQL Enterprise Manager.
I recommend posting MS SQL Server questions in forum181. Though Microsoft is striving to make T-SQL ANSI compliant, there are still differences. Questions like the one you posted are very product specific. At least the answer is product specific. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
I recommend posting MS SQL Server questions in forum181. Though Microsoft is striving to make T-SQL ANSI compliant, there are still differences. Questions like the one you posted are very product specific. At least the answer is product specific. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Status