display custom values in select statement

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

General SQL Server Forums

New to SQL Server Programming

display custom values in select statement

Author

Topic

toprasad
Starting Member

4 Posts

Posted - 2008-05-08 : 07:26:10

Hi,
I have a table called emp, having 2 field name & sex

values are:
name sex
a 1
b 2
c 1

now i want to display the values in above table as like below...
name sex
a Male
b Female
c Male

How to do that...?

Vadivu
Starting Member

31 Posts

Posted - 2008-05-08 : 07:30:01

update emp set sex = case when sex =1 then 'Male' else ' Female' end

hope the column is not of type int

Thought u wanted to update the table.. In case u just want to display the data, then

select sex = case when sex =1 then 'Male' else 'Female' end from emp

toprasad
Starting Member

4 Posts

Posted - 2008-05-08 : 07:54:10

Hi,

Thanks for reply.

I dont want to update the table as u expected.

I just want to display the result using select statement.

I executed the ur mentioned statement but i got the following error.

"ORA-00923: FROM keyword not found where expected"

It seems to be syntax problem....

?????

Vadivu
Starting Member

31 Posts

Posted - 2008-05-08 : 07:58:03

this query works perfectly fine in sql server

toprasad
Starting Member

4 Posts

Posted - 2008-05-08 : 08:04:06

Hi,

I tried to execute similar type of query like below....

select SUB_STATUS = CASE WHEN SUB_STATUS = "A" THEN "Male" ELSE "Female" end CASE FROM subscriber;

I got error "ORA-00923: FROM keyword not found where expected"

????

Vadivu
Starting Member

31 Posts

Posted - 2008-05-08 : 08:15:39

try removing the CASE keyword at the end... that is not needed in sql server

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-08 : 08:50:37

quote:
Originally posted by toprasad

Hi,

I tried to execute similar type of query like below....

select SUB_STATUS = CASE WHEN SUB_STATUS = "A" THEN "Male" ELSE "Female" end CASE FROM subscriber;

I got error "ORA-00923: FROM keyword not found where expected"

????

Try

select CASE WHEN SUB_STATUS = 'A' THEN 'Male' ELSE 'Female' end as SUB_STATUS FROM subscriber;

This is MS SQL Server Forum
Post your question at Oracle forums
www.orafaq.com
www.dbforums.com

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-08 : 08:55:14

As mentioned, this is not an Oracle forum, this is a SQL Server forum.

However, for both products: you should have a tables of Genders or Sex, that has the 2 values (or more?) and the description. Then, you simply JOIN to your Sex table to get the description. This is the way databases are designed to be used; don't hard-code or embed descriptions or data in your SQL statements, store your data in your tables.

see:
http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

toprasad
Starting Member

4 Posts

Posted - 2008-05-08 : 23:40:10

Hi Madhivanan,

This statement worked:

select CASE WHEN SUB_STATUS = 'A' THEN 'Male' ELSE 'Female' end as SUB_STATUS FROM subscriber;

Many thanks
Prasad

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-09 : 02:39:34

quote:
Originally posted by toprasad

Hi Madhivanan,

This statement worked:

select CASE WHEN SUB_STATUS = 'A' THEN 'Male' ELSE 'Female' end as SUB_STATUS FROM subscriber;

Many thanks
Prasad

Thanks and keep in mind that you should post oracle related questions at ORACLE forums