Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CorpDirect
Yak Posting Veteran
92 Posts |
 Posted - 2005-10-10 : 01:10:07
|
Good day,I tried to create a view from a query that uses CASE, and received the error "The Query Designer does not support the CASE SQL construct." -- so do I have to use something else instead of CASE?One column in the view needs to return 2 columns concatenated as one IF the second column contains data. The first column is CHAR(60), and the second is TEXT. Both often contain leading and trailing spaces. The data returned is the first matching from an ordered subquery.Here's the column selection:SELECT..., description = ( SELECT TOP 1 CASE WHEN CONVERT(VARCHAR,text_col) = '' THEN LTRIM(RTRIM(char_col)) ELSE LTRIM(RTRIM(char_col)) + ' / ' + CONVERT(VARCHAR(255),text_col) END FROM table_name WHERE where_condition ORDER BY some_order )... Can anyone suggest an alternative to CASE which would be permitted in a view definition?DDL and DML are not posted here because they would be time-intensive to create (a sample environment), but if required I will make something for you to work with, just let me know what you need.Thanks and regards,Daniel |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-10 : 01:35:23
|
| The construction looks fine to me."The Query Designer does not support the CASE SQL construct"What are you using to construct the Query? Maybe its just a limitation of that, rather than SQL itself?Kristen |
 |
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2005-10-10 : 01:55:42
|
| Just pasting the query (tested in Query Analyzer) into a "New View" design view window in Enterprise Manager. |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2005-10-10 : 03:00:02
|
| OK, I went a little further with it, trying an alternative approach. I was able to create a function that returned the results desired by the subquery using CASE, and reference this function to create the view.After that I was not satisfied about having to do that, so I tried to create the view using DDL from Query Analyzer instead. Guess what? It worked! CASE and all, it worked, and functions as expected.So, a lesson learned: don't rely on the GUI of Enterprise Manager when creating views. Create them manually instead; you'll avoid artificial limitations and gain a better understanding while you're at it.Daniel |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-10 : 04:21:53
|
| I guessed that might be the "case" !!FWIW I use Enterprise Manager to make Design Table alterations, because scripting them can be laborious, but I always use the "Create Change Script" tool to give me the script and abandon the change in E.M.I'm always happier seeing what script I am going to run in Query Analyser, rather than relying on what mangling a GUI might make in practice.Kristen |
|
|
|
anuj164
Starting Member
49 Posts |
Posted - 2005-10-11 : 16:52:35
|
| I agree to you Kristen, at times EM make things so hard to mange. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-12 : 16:20:36
|
quote:
Originally posted by Kristen
I'm always happier seeing what script I am going to run in Query Analyser, rather than relying on what mangling a GUI might make in practice.
Especially since EM doesn't always take the most direct route for things. It always picks a route that will work, but not necessarily the most direct route. And if you're dealing with lots of rows, that can be a huge difference in performance.---------------------------EmeraldCityDomains.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-13 : 09:27:16
|
| "doesn't always take the most direct route for things"Like: insert the rows into the new temporary table, unsorted, and then create the Clustered PK Index!Kristen |
|
|
|
|
|
|
|
|
alternative to CASE in view