REturning "Approve" when bit is 1 and "NotApproved

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)

REturning "Approve" when bit is 1 and "NotApproved

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-08-04 : 10:53:20

HI,

I have a table which has an "Active" field. The table has several hundred records. Active can be 1 or 0. Now I want to return "Approved" where Active is 1 and "Not Approved" where Active is 0.

Thanks,
Azam

Mohammad Azam
www.azamsharp.net

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-04 : 10:57:19

Select columns, case when active=1 then 'Approved' else 'Not Approved' end
from yourtable

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 11:05:22

Here Comes Mister Pedantic!


Select columns, 
    case active when 1 then 'Approved' 
                when 0 then 'Not Approved' 
                else 'Unknown' 
                end 
from yourtable

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-04 : 11:10:44

Yours is neat Kris

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 11:11:59

I was planning to fail

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-04 : 11:34:50

Or, just assume it's Not Approved if it isn't 1:

Select
	columns, 
	case active
	when 1
	then 'Approved' 
	else 'Not Approved' 
	end 
from
	yourtable

quote:

Originally posted by Kristen

Here Comes Mister Pedantic!


Select columns, 
    case active when 1 then 'Approved' 
                when 0 then 'Not Approved' 
                else 'Unknown' 
                end 
from yourtable

Kristen

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 11:53:50

That's what MADs code did, MVJ, this is what our esteemed SQL Team questioner asked for:

return "Approved" where Active is 1 and "Not Approved" where Active is 0.

I added "error handling"

By the by, and joking apart, our developers are required to write "defensive code" so in these situations it is never permitted [here] to assume that <> 1 is a valid condition. Something somewhere sometime will screw it up! and I'd prefer something to break, or complain, rather than it going unnoticed.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-04 : 12:02:33

and the crowd roars their approval......

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 14:23:53

Is that "in support" or "American sarcasm"?

I figure I should know by now Brett, but I just had to ask before I make an ar$e of myself!

Kristen

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-08-06 : 15:02:33

Thanks for the reply. Actually the requirments have changed a bit now and I am using Access database: I want to return '*****' if RO is not NULL and return 'Enter RO' when RO is NULL.

SELECT [ReportID], [Color],[DateCreated],[Name],[Year],[Make],[Model],[LoanerCar],[Estimator],[Insurance],[Wrecker],[Pls],[Lien],[Status],

CASE [RO] WHEN NOT NULL THEN '*****'
ELSE [RO]
END

FROM tblReports

Mohammad Azam
www.azamsharp.net

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-06 : 15:29:50

This is invalid syntax in SQL Server, and it wouldn't do what you described even if it was, because it would not return 'Enter RO' when RO is NULL:

CASE [RO] WHEN NOT NULL THEN '*****'
ELSE [RO]
END

This is the correct CASE syntax:


select
	case
	when [RO] is null
	then 'Enter RO'
	else '*****' 
	end
from
	tblReports

quote:
Originally posted by azamsharp

Thanks for the reply. Actually the requirments have changed a bit now and I am using Access database: I want to return '*****' if RO is not NULL and return 'Enter RO' when RO is NULL.

SELECT [ReportID], [Color],[DateCreated],[Name],[Year],[Make],[Model],[LoanerCar],[Estimator],[Insurance],[Wrecker],[Pls],[Lien],[Status],

CASE [RO] WHEN NOT NULL THEN '*****'
ELSE [RO]
END

FROM tblReports

Mohammad Azam
www.azamsharp.net

CODO ERGO SUM

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-08-06 : 15:31:29

but I need to return more columns and not only only [RO]

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-08-06 : 15:33:00

Even this does not work.

SELECT [ReportID], [Color],[DateCreated],[Name],[Year],[Make],[Model],[LoanerCar],[Estimator],[Insurance],[Wrecker],[Pls],[Lien],[Status],[RO]

CASE WHEN [RO] IS NULL THEN 'Enter RO'
ELSE '*****'
END
FROM tblReports

Mohammad Azam
www.azamsharp.net

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-06 : 15:55:17

You introduced a new error; you have a missing comma after a column name.

CODO ERGO SUM

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-08-06 : 16:08:38

where if you mean after [RO] then tried that also did not work.

Mohammad Azam
www.azamsharp.net

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 00:54:58

>>but I need to return more columns and not only only [RO]

What are the other columns?

Madhivanan

Failing to plan is Planning to fail