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

Hello all,

I came from an Access world, and so far have been mostly self taught on
the SQL Server side of things.  
One thing that I used to do in Access from time to time is have 2 fields
that were returned in a query, however for reporting purposes, sometimes
it was determined that one or the other was to be used depending on a
condition.  This was easy in Access as it allowed you to use an IIF()
statement and actually use the value of another field inline with each
record.  Now I'm trying to do the same in the following query in SQL
Server.

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.

SELECT 
RGA.RGANo, RGA.RGADate, 
RGA.ContactFName, 
RGA.ContactlName, 
isnull(RGA.ContactFName,'') + ' ' + isnull(RGA.ContactlName,'') as
ContactName,
CustomerAddress.CUSTOMER_NAME AS CompanyName, RGA.PCCOrderNo, 
UsedOrderInfo.CUSTOMER_REFERENCE AS CustomerRef, RGA.Freight, 
CASE rga.restockfee 
when 0 then 'No'
when 1 then 'Yes'
else 'N/A' End as Restockfee,
RGA.RGANotes, RGA.FreightStatus, PCCRep.FirstName, 
PCCRep.LastName, lkpReason.ReasonDesc, lkpWarehouse.Companyname as
whseCompanyName, 
lkpWarehouse.Address1 as whseAddress1, lkpWarehouse.Address2 as
whseAddress2, lkpWarehouse.City as whseCity, 
lkpWarehouse.State as whseState, lkpWarehouse.Zip as whseZip, RGA.RGAId,
dbo.NoOrderAddress.Address1 AS NoOrdAdd1,
CustomerAddress.SYS_ADDRESS_1, 
CustomerAddress.SYS_ADDRESS_2, 
CustomerAddress.SYS_POSTAL_CODE, 
CustomerAddress.SYS_CITY, 
CustomerAddress.SYS_STATE, 
UsedOrderInfo.CUSTOMER_NUMBER AS CompanyNumber, 
lkpWarehouse.Location, RGA.ReasonForCredit, RGA.DetailsOfCredit,
RGA.Approved, 
UsedOrderInfo.DELIVERY_ADDRESS_CODE, UsedOrderInfo.INVOICE_ADDRESS_CODE,

CustomerAddress.ADDRESS_CODE, RGA.RequestedCredit, lkpStatus.StatusDesc,
NoOrderAddress.CompanyName AS NoOrdCoName, 
                      NoOrderAddress.Address1 AS NoOrdAdd1,
NoOrderAddress.Address2 AS NoOrdAdd2, NoOrderAddress.City AS NoOrdCity, 
                      NoOrderAddress.State AS NoOrdState,
NoOrderAddress.ZipCode AS NoOrdZip, NoOrderAddress.PhoneNumber AS
NoOrdPhone, 
                      NoOrderAddress.FaxNumber AS NoOrdFax,
NoOrderAddress.DiscrepancyNotes AS NoOrdNotes

FROM 

dbo.lkpReason RIGHT OUTER JOIN
                      dbo.lkpWarehouse RIGHT OUTER JOIN
                      dbo.PCCRep RIGHT OUTER JOIN
                      dbo.NoOrderAddress RIGHT OUTER JOIN
                      dbo.RGA ON dbo.NoOrderAddress.RGAID =
dbo.RGA.RGAId ON dbo.PCCRep.PCCRepID = dbo.RGA.PCCRepID ON 
                      dbo.lkpWarehouse.WarehouseID = dbo.RGA.WarehouseID
ON dbo.lkpReason.reasonID = dbo.RGA.ReasonID LEFT OUTER JOIN
                      dbo.UsedOrderInfo LEFT OUTER JOIN
                      OPENQUERY(RENCS, 
                      'select Address_code, Customer_number,
sys_Postal_code, Customer_Name, sys_address_1, sys_address_2, sys_state,
sys_city from fin_prod.CUSTOMER_ADDRESSES')
                       CustomerAddress ON
dbo.UsedOrderInfo.DELIVERY_ADDRESS_CODE = CustomerAddress.ADDRESS_CODE
AND 
                      dbo.UsedOrderInfo.CUSTOMER_NUMBER =
CustomerAddress.CUSTOMER_NUMBER ON 
                      dbo.RGA.PCCOrderNo =
dbo.UsedOrderInfo.ORDER_NUMBER LEFT OUTER JOIN
                      dbo.lkpStatus ON dbo.RGA.RGAStatus =
dbo.lkpStatus.StatusID
Where 
RGA.PCCOrderNo = 'No Order'


Rich