Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with CASE statements Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 10:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:53 PM
Points: 69, Visits: 187
TableA

Key AgencyFrom ValueFrom AgencyTo ValueTo
---------------------------------------------------
223 <blank> 300 washington 200


TableA has the above mentioned columns. I want to derive either ValueFrom or ValueTO only if AgencyFrom or AgencyTo is <blank>

How do it do that? Below is my script but it returns something like this.

SELECT
CASE WHEN AgencyFrom = '' then ValueFrom
Else '' End as From
CASE WHEN AgencyTo = '' then ValueTo
Else '' End as To
From TableA
Where Code = 223

Output:

ValueFrom ValueTo
-------------------------------
300

Desired Output
ValueFrom
----------
300



Post #1413704
Posted Wednesday, January 30, 2013 10:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:32 PM
Points: 490, Visits: 1,865
To clarify. You're looking for only a one column output? What you're describing sounds like two separate queries, one for blank AgencyFrom and another for blank AgencyTo.
Post #1413715
Posted Wednesday, January 30, 2013 11:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:53 PM
Points: 69, Visits: 187
Yes. I want only one column as the output. And the condition for the output is the <blank> value in one of the columns. In my table, only one column (AgencyFrom or AgencyTo) hold <blank> data per row. Both do not hold <blank> together.

Does this make sense?
Post #1413725
Posted Wednesday, January 30, 2013 1:27 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:32 PM
Points: 490, Visits: 1,865
Ok, here's one possibility. I'm still not sure that I understand the requirements but I believe this will get the type of output you're looking for. Note that it requires a consistent name for the final output column.

A suggestion. To get the best help, you'll do better posting ddl for the tables you need and sample data to insert. That saves folks time when they're trying to provide help. It also helps clarify what you're looking for. See the article http://www.sqlservercentral.com/articles/Best+Practices/61537/ about best practices for getting help on the forums.

Create Table TableA
(
PK int primary key clustered,
AgencyFrom varchar(30),
ValueFrom int,
AgencyTo varchar(30),
ValueTo int
)
;

Insert TableA (PK, AgencyFrom, ValueFrom, AgencyTo, ValueTo)
Values
(223, '', 300, 'Washington', 200),
(230, 'Boston', 400, '', 100)
;


With blankFrom
As
(
Select
ValueFrom as Value
From
TableA
Where
AgencyFrom = ''
),
blankTo
As
(
Select
ValueTo as Value
From
TableA
Where
AgencyTo = ''
)

Select Value
From blankFrom
Union
Select Value
From blankTo
;


Drop table TableA;

Post #1413757
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse