February 13, 2008 at 8:17 am
Hello,
I have a problem where I need to include data from a table where one of the joined columns can contain empty strings. The emptry string column and its related columns need to be included in the results of the query.
The Join looks like this:
SalesData.dbo.tbl_CYProcessedSales ps
INNER JOIN SalesData.dbo.vw_Product_CYProcessedSalesXref xr
ON ps.Prod = xr.Prod
AND ps.Acct = xr.AcctCode
INNER JOIN TxnRptg.dbo.vw_NetNewRevenueUnion nn
ON xr.BillingType = nn.BillingType
AND xr.ProdGroup = nn.Product
AND xr.AcctCode = nn.AcctCode
The problem occurs on the xr.BillingType = nn.BillingType portion of the join. the nn.BillingType can contain empty strings. I've tried a LEFT join to vw_NetNewRevenueUnion, but this has not worked.
in vw_NetNewRevenueUnion, BillingType becomes an empty string within a CASE statement if BillingType is NULL. I've thought about using something like 'All' in the ELSE condition, and doing the same in the vw_Product_CYProcessedSales view just so I don't have to deal with an empty string.
If I were not to do that, is there some way I can handle the empty string issue within the join so that empty string records will be included in the query results?
Thank you for your help!
CSDunn
February 13, 2008 at 8:25 am
Can you make it as LEFT OUTER JOIN instead of LEFT JOIN ?
Lucky
February 13, 2008 at 8:27 am
Wouldn't it be a RIGHT join vs a Left join to get nulls from the table you are joining?
Toni
February 13, 2008 at 8:34 am
I noticed you didn't mention using the CASE statement to remove nulls from your second view. I suspect that is your issue. In other words - I wouldn't be surprised to find out that nn.BillingType is on occasion NULL, and thuse your issue
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 13, 2008 at 8:38 am
Can you not add a where clause (or add to it) ?
SalesData.dbo.tbl_CYProcessedSales ps
INNER JOIN SalesData.dbo.vw_Product_CYProcessedSalesXref xr
ON ps.Prod = xr.Prod
AND ps.Acct = xr.AcctCode
INNER JOIN TxnRptg.dbo.vw_NetNewRevenueUnion nn
ON xr.BillingType = nn.BillingType
AND xr.ProdGroup = nn.Product
AND xr.AcctCode = nn.AcctCode
WHERE nn.BillingType <> ''
February 13, 2008 at 9:09 am
Does the 'XR' table contain BillingTypes that are an empty string as well?
You're joining from PS to XR, and then from XR to NN. It would be between the XR and NN tables that you would be losing those empty BillingType rows.
A trick I've employed when troubleshooting queries is to find a single row result that should show up (i.e. a row that has the empty BillingType) and add a WHERE to filter for that row result. Then mess around with your joins until you get that result.
Hope this helps.
Dan
February 13, 2008 at 9:36 am
Thanks for your responses. I'm going to do more troubleshooting, and I'll come back to this if a solution does not arise from my findings.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply