SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with CASE statements


Help with CASE statements

Author
Message
Sql Student-446896
Sql Student-446896
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 201
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
kl25
kl25
Say Hey Kid
Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)

Group: General Forum Members
Points: 690 Visits: 1875
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.
Sql Student-446896
Sql Student-446896
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 201
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?
kl25
kl25
Say Hey Kid
Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)

Group: General Forum Members
Points: 690 Visits: 1875
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;


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search