Converting data types in a view

Last Post 07 Jun 2005 09:41 PM by Lee-Z2. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
bullhead
New Member
New Member

--
02 Jun 2005 11:21 AM
I am currently working on a view and I need to be able to convert 0's and 1's into yes and no respectively. Is there a way to do this in the view?
Lee-Z2
New Member
New Member

--
02 Jun 2005 10:58 PM
SELECT
CASE YourField WHEN 0 THEN 'Yes' WHEN 1 THEN 'No' ELSE NULL END AS TranslatedField
FROM
Yourtable
bullhead
New Member
New Member

--
03 Jun 2005 06:26 AM
i tired that solution, but i get an error about CASE not allowd in view designer, and i also get an error near the word WHEN. I am working in a exsisting view i built with view design.
Lee-Z2
New Member
New Member

--
07 Jun 2005 09:41 PM
Ah, okay, I never use the view designer for views (not even new views), because as you noticed it complains about stuff it shouldn't complain about: a CASE Statement is perfectly usable in views, but not with the designer.....

So, my advice would be to not use the designer, but if that's not an option for you, than maybe someone else has a solution for your problem....
SQLUSA
New Member
New Member

--
12 Jun 2005 03:37 PM
How about trying UNION? See how smart is the view designer:

select convert(char(3),'Yes),..... from TableX when FlagM=1
UNION
select Response=convert(char(3),'No'),..... from TableX when FlagM=0

Kalman Toth, SQL Guru
http://www.sqlusa.com
dbandee
New Member
New Member

--
14 Jun 2005 04:48 AM
Lee-Z2 solution work fine in QA, dont use view designer it is limited to whet statements it supports plus its all GUI u dont want to use GUI you want to write SQL statements
Create view vw_deleteme as
SELECT
CASE Field2 WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE NULL END AS Field2
FROM Table6
Lee-Z2
New Member
New Member

--
20 Jun 2005 12:54 AM
Somewhat OT and slightly irrelevant,
but you (bullhead) use 0 as Yes and 1 as No....usually it's the other way around
(i noticed dbandee, like me initially, automatically uses "when 1 then yes when 0 then no" :-) )
You are not authorized to post a reply.

Acceptable Use Policy