case to determine which field from which table?

[timscronin]

I have a select statement which needs to apply some logic to determine which fields to use. If the modify_timestamp = create_timestamp, then I want to use a location field from patient_medication table. If the modify_timestamp and create_timestamp are not equal, I need to use the location field from a location table. So my select would be
select account_id,ndc,startdate -- and then my logic. Am I correct that you can't use a case for this?

 

[bborissov]

SELECT account_id,
       ndc,
       startdate,
       CASE WHEN modify_timestamp = create_timestamp THEN
                 patient_medication.Location
            ELSE 
                 Location.Location
            END AS Location
....

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[NSMan]

I'm not sure I understand what you're trying to do, but if I am understanding right, you have three tables you're selecting on. Assuming all of them are joined later in the select statement, would this work?

select
 account.account_id,
 account.ndc,
 account.startdate,
 case when account.modify_timestamp =    account.createtimestamp then patient_medication.location else location.location end as timestamp