Join one table using one of two tables conditionally

Last Post 25 Aug 2009 11:54 AM by tcunningham. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
tcunningham
New Member
New Member

--
24 Aug 2009 12:54 PM
I am trying to retreive a description for a code from a code table where the code or the key could come from one of two different tables. When I attempt to do it I get an error "Subquery returned more than 1 value." Here is the code I am using.

Case
When dbo.tarInvoice.ReasonCodeKey is null Then
(Select dbo.tciReasonCode.Description
From dbo.tciReasonCode
Join dbo.tsoSalesOrder ON
dbo.tsoSalesOrder.UserFld3 = dbo.tciReasonCode.ReasonCodeID AND dbo.tsoSalesOrder.CompanyID =dbo.tciReasonCode.CompanyID)
Else
(Select dbo.tciReasonCode.Description
From dbo.tciReasonCode
JOIN dbo.tarInvoice ON dbo.tarInvoice.ReasonCodeKey = dbo.tciReasonCode.ReasonCodeKey)
End as OrderType,
tcunningham
New Member
New Member

--
25 Aug 2009 11:54 AM
Never mind, problem solved


Acceptable Use Policy
---