Change returned values - 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 2000 Forums

Transact-SQL (2000)

Change returned values

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2005-08-16 : 10:08:17

Hi everyone,

I have a boolean field in the table, and
during the course of a select, i would like
to return a text constant instead of the boolean value.

for example, if the value is a 0, then
i would like the string returned to be 'Pending'
and if the value is a 1, then the value
returned needs to be 'Sumitted'

Please advise if i can accomplish this in the query,
and how

thanks
tony

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 10:10:22

[code]
Select othercolumns,
case when booleanField=0 then 'Pending'
else 'Submitted'
end
from yourtable[/code]

Madhivanan

Failing to plan is Planning to fail

mr_mist
Grunnio

1870 Posts

Posted - 2005-08-16 : 10:11:45

You should probably have a table with the correct values in and join to it, but if you insist on doing the work within the code, then you can use a CASE statement.

SELECT CASE when yourvalue = 0 then 'Pending' else 'Submitted' end as yourvalue
FROM blah

-------
Moo. :)

pithhelmet
Posting Yak Master

183 Posts

Posted - 2005-08-16 : 10:28:19

Here is the query that is provided....

SELECT
mresi_lkey,
mresi_boolSubmitted,
mresi_dtsampledatetime,
mresi_szsampleid,
dspt_szname,
dev_szcode,
(mresi_szoperatorlname + ', ' + mresi_szoperatorfname + ' ' + ISNULL(mresi_szoperatormi, '')) as namefield,
(mresi_szoperatorfacility + ' / ' + mresi_szoperatorlocation) as facloc
FROM
tblMRESampleInformation
INNER JOIN tblDisposableType ON tblMRESampleInformation.MRESI_lfkDT = tblDisposableType.DSPT_lKey
INNER JOIN tblDevice ON tblDisposableType.DSPT_lfkDEVKey = tblDevice.DEV_lKEY
LEFT OUTER JOIN tblMREResultInformation ON tblMRESampleInformation.MRESI_lKey = tblMREResultInformation.MRERI_lKey
where
mresi_boolsubmitted <> 99
ORDER BY MRESI_lKey

I was thinking somewhere in the
(mresi_szoperatorfacility + ' / ' + mresi_szoperatorlocation) as facloc
line of code to add in the select???

thanks for the help!!

take care
tony

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 10:30:32

Yes you can add it at the last column of the query

Madhivanan

Failing to plan is Planning to fail

pithhelmet
Posting Yak Master

183 Posts

Posted - 2005-08-16 : 10:34:45

PERFECT!!

thanks a million for ya'lls help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources