CODEWITHSUNDEEP
sqlsql-server
Mike Payne
Mike Payne

Reputation: 107

Using a dynamic column in the WHERE clause of a select statement

I have this query:

select 
    *
    ,(select location from [order] where [id] = oi.OrderID) as orderlocation
    ,case 
        when oi.location is not null then oi.location
        when oi.location is null then orderlocation
        end
from
    orderitem oi

and I get an error:

Invalid column name 'orderlocation'

The following query works fine and does what is intended:

select  
    *
    ,(select location from [order] where [id] = oi.OrderID) as orderlocation
    ,case 
        when oi.location is not null then oi.location
        when oi.location is null then (select location from [order] where [id] = oi.OrderID)
        end
from
    orderitem oi

Realistically, this isn't a huge problem, but it's a bit of a pain to have to repeat the same text twice, and to have to remember to update it in both places if it changes. Is there a better way to write this query so that there is no repetition? Maybe using variables or something? I'm not an SQL expert so I'm not sure a better way to write this.

Any help would be appreciated. Thanks in advance!

Upvotes: 0

Views: 101

Answers (3)

Mike Payne
Mike Payne

Reputation: 107

I actually was already using a join, but I simplified the query for the question. The original query with the case statement was

select * from orderitem oi inner join [order] o on oi.orderid = o.id

I was not aware of the coalesce or isnull commands, though. They worked perfectly. I ended up with:

select *
   ,coalesce(oi.location, o.location) as ManufacturingLocation
from orderitem oi inner join [order] o
on oi.orderid = o.id

Thanks for the help!

Upvotes: 0

Yarner
Yarner

Reputation: 115

To expand on @John Cappelletti's comment. 

select *
    ,ord.location  as orderlocation
    ,case 
        when oi.location is not null then oi.location
        when oi.location is null then ord.location
        end
from orderitem oi
LEFT JOIN [order] ORD on ord.[id] = oi.OrderID

Upvotes: 1

Thom A
Thom A

Reputation: 96038

You could move the subquery to the FROM:

select *, --This should really be a proper list
       ISNULL(oi.location,ol.orderlocation) AS OrderLocation2 --ISNULKL or COALESCE are much more succinct
from orderitem oi
     CROSS APPLY (select location AS orderlocation from [order] o where o.[id] = oi.OrderID)) o; --Use OUTER APPLY if this may not return a result

Though looking at your code, wouldn't this really be:

SELECT oi.*, --still should be a distinct column list
       o.location,
       ISNULL(oi.location,o.location ) AS OrderLocation
FROM dbo.orderitem oi
     JOIN dbo.[order] O ON o.[id] = oi.OrderIDl --LEFT JOIN if there may not be a row here.

Upvotes: 3

Related Questions