[dba-SQLServer] Use one field or another in query based on acondition

I had already tried the exact case statement you gave prior to my post
and I swear to the gods above that it did not work.. And today.. Well it
works perfectly.  Maybe I had a typo or something.. Who knows.. 

Just to be correct in the post a Case statement uses When not an = .. 

CASE RGA.PCCOrderNo WHEN 'No Order' THEN NoOrderAddress.Address1 ELSE
CustomerAddress.Sys_address_1 END AS NoOrdAdd1

Thanks for your response Francisco
Rich 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Francisco Tapia
Sent: Thursday, May 19, 2005 2:21 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Use one field or another in query based on
acondition


Try including the following Case statement...
if the PCCOrderNO = No Order then the NoOrder address will be displayed,

otherwise the Order one will.


CASE RGA.PCCOrderNo = 'No Order' THEN NoOrderAddress.Address1 ELSE
CustomerAddress.Sys_address_1 END AS NoOrdAdd1, 


On 5/19/05, Lavsa, Rich <Rich_Lavsa at pghcorning.com> wrote:
> 
> 
> In the Middle of the Select portion, you will see 
> CustomerAddress.Sys_address_1 and at the end you will see 
> NoOrderAddress.Address1 AS NoOrdAdd1. What can happen in this scenario

> is that sometimes the information is brought back from our ERP system 
> in which the Order Number will return all the pertant information, 
> however in very few cases I had to allow the entry of data even though

> there wasn't an Order number to refer to, so the information on a NO 
> ORDER would be manually entered. So what I hope to achieve is to 
> return the value CustomerAddress.Sys_address_1 if RGA.PCCOrderNo <> 
> 'No Order' else return NoOrderAddress.Address1 AS NoOrdAdd1. I tried 
> the ISNULL approach, Case statement, and an IF statement neither of 
> which worked in returning a value that is inline, however all worked 
> in testing for the condition and returning text but none acutally 
> returned the field value when I ask it to return 
> NoOrderAddress.Address1.
> 
> Any help would be greatly appreciated.
> 
> 


-- 
-Francisco