CODEWITHSUNDEEP
sqlsql-server
Sarah
Sarah

Reputation: 155

sub query within coalesce

I'm currently using a coalesce query to return all values when an input parameter isn't present as follows:

@unid int

SELECT
  *
FROM Bag_Dim
LEFT JOIN Bag_Action_Join
  ON Bag_Dim.Unid = Bag_Action_Join.Bag_Unid
WHERE Bag_Dim.Unid = COALESCE(@unid, [bag_dim].[unid])

I wanted to add an extra field to the return parameters that are only present on some of the records, so the code was adapted as follows: 

@unid int,
@location int

SELECT
  *,
  origin.location
FROM Bag_Dim
LEFT JOIN Bag_Action_Join
  ON Bag_Dim.Unid = Bag_Action_Join.Bag_Unid
LEFT JOIN Bag_Action_Join AS origin
  ON Bag_Dim.Unid = origin.Bag_Unid
  AND origin.action = 1
WHERE Bag_Dim.Unid = COALESCE(@unid, [bag_dim].[unid])
AND origin.location = COALESCE(@location, origin.location)

The problem is that not all the records have entries in the origin table for location = 1, so they get omitted when the @location parameter is null. Ideally I would adapt the final line of the query as follows, but the syntax doesn't work:

WHERE origin.location = coalesce(@location,(origin.location OR origin.location IS NULL))

Any suggestions on how I can get all records (whether null or not) if the input parameter isn't present?

Upvotes: 4

Views: 1641

Answers (2)

SMor
SMor

Reputation: 2872

It sounds like you need to move your new condition from the WHERE clause into the JOIN criteria. When you refer to the unpreserved table (from an outer join) in the where clause of the query, you logically convert the outer join to an inner join - and that probably isn't what you want.

Upvotes: 1

SqlZim
SqlZim

Reputation: 38043

I think this is what you want:

where (origin.location = @location or @location is null)

Upvotes: 2

Related Questions