Using CASE in a View - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using CASE in a View

Author

Topic

dominican1979
Starting Member

18 Posts

Posted - 2010-04-08 : 14:03:12

Hello everyone,

I don't know if this can be done somehow, but I've seen some samples online where you can supposedly use CASE on a view, and I've tried but keep getting syntax errors that are not specific such as error next to '=' whatever that means.

Basically I have a view where I'm creating a product description field by concatenating the fields listed below. The correct description would look something like this:
1001 0 c. 1238 145-5
broken down like this:
prodno = 1001
nosepad = 0
c. (this is just concatenated text)
color = 1218
size = 145-5

However, the nosepad field will always be either 1 or 0. If it is 0 then i want to replace it with ' ' (space) on the description like this:
1001 c. 1238 145-5

and if its 1 I want to include it in the description like this:
1001 1 c. 1238 145-5

Here is the SQL for the View i'm trying to create, any help

select prodno, nosepad, size, case when gr10 = '0' then gr10 = ' ' else gr10 end
from prod

Thank you sooo much in advance for reading my post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 14:08:32

it should be

select prodno, nosepad, size, case when gr10 = '0' then ' ' else gr10 end
from prod

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dominican1979
Starting Member

18 Posts

Posted - 2010-04-08 : 14:52:27

hi visakh16,

Thank you very much for your reply. I tried what you said, but still got the same error message "Incorrect syntax near ="

select prodno, nosepad, size, case when gr10 = '0' then '' else gr10 end
from prod

Then I just tried just for kicks because I just realized that gr10 is an int field
select prodno, nosepad, size, case when gr10 = 1 then 1 else gr10 end
from prod

Then I tried casting the field like this:

select prodno, descr, gr10, case WHEN (CAST(Gr10 AS varchar(8)) = '0') THEN (CAST(Gr10 AS varchar(8)) = '') else gr10 end
from prod

they all give me the same exact error, I don't know what's wrong, is this really possible on a view?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-09 : 03:00:16

You can't show '' along with integer value
Where do you want to show data?

One option is to convert it to varchar

select prodno, nosepad, size, case when gr10 = 0 then ' ' else cast(gr10 as varchar(10)) end
from prod

Madhivanan

Failing to plan is Planning to fail

dominican1979
Starting Member

18 Posts

Posted - 2010-04-09 : 10:10:40

Hi madhivanan

Thank you very very much for your help, that works like a charm. It was driving me nuts, thanks again and have a great day!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-09 : 10:33:54

quote:
Originally posted by dominican1979

Hi madhivanan

Thank you very very much for your help, that works like a charm. It was driving me nuts, thanks again and have a great day!

Thanks and you are welcome

Madhivanan

Failing to plan is Planning to fail

dominican1979
Starting Member

18 Posts

Posted - 2010-04-09 : 11:10:30

Hi again Madhivanan,

I was wondering if you could help me with this, its the same topic, but I just need integrate that CASE part into the view below. My goal is to have a description field like this: 1001 1 c. 1238 145-5 if gr10 = 1 and 1001 c. 1238 145-5 if gr10 = 0. This view works just great without the case statement (in bold) but right now I'm getting syntax errors the way I have written it, can CASE be used in this context? meaning in a concatenated string? if so would you mind pointing me in the right direction? Thanks a million again

SELECT CAST(ModelNo AS varchar(8)) + ' ' + case when gr10 = 0 then ' ' else cast(gr10 as varchar(10)) + ' ' + 'c. ' + CAST(ColorNo AS varchar(8)) + CAST(ColorShade AS varchar(8)) + ' ' + ISNULL ((SELECT TOP (1) Txt
FROM dbo.Txt AS Txt_1
WHERE (TxtNo = dbo.Prod.Gr4) AND (TxtTp = 45) AND (Lang = 1)), '') AS description
FROM dbo.Prod

dominican1979
Starting Member

18 Posts

Posted - 2010-04-09 : 13:28:24

Hello,

I just wanted to thank you all for your help, I figured out a solution without using case, I just used REPLACE(CAST(Gr10 AS nvarchar(8)), '0', ' ') and works perfect. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources