February 17, 2009 at 7:28 am
I am currently going through some of my predecesors code and came across something that does not make sense. I have included the code below:
SELECT
A.providerCode,
A.practiceCode,
A.POD,
A.activityMonth,
0,
0,
A.activity,
A.cost,
A.highCostRiskPool,
0
FROM
PBC_Budget_v_Actuals BvA RIGHT OUTER JOIN
vw_PBC_Report_Actuals A ON BvA.providerCode = A.providerCode AND BvA.practiceCode = A.practiceCode AND BvA.POD = A.POD AND
BvA.activityMonth = A.activityMonth AND BvA.highCostRiskPool = A.highCostRiskPool
WHERE
BvA.providerCode IS NULL
I do not understamd the significance of the Null in the where clause. The join is based on BvA.providerCode = A.providerCode etc. IF BvA.providerCode IS NULL then how can the join criteria work.
February 17, 2009 at 7:47 am
Because it is a RIGHT OUTER JOIN the WHERE clause is saying give me all the rows in vw_PBC_Report_Actuals that do NOT have a corresponding row in PBC_Budget_v_Actuals. This limits the output to only missing rows.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 17, 2009 at 7:53 am
eseosaoregie (2/17/2009)
I am currently going through some of my predecesors code and came across something that does not make sense. I have included the code below:
SELECT... FROM
PBC_Budget_v_Actuals BvA
RIGHT OUTER JOIN
vw_PBC_Report_Actuals A
ON BvA.providerCode = A.providerCode
AND BvA.practiceCode = A.practiceCode
AND BvA.POD = A.POD
AND BvA.activityMonth = A.activityMonth
AND BvA.highCostRiskPool = A.highCostRiskPool
WHERE
BvA.providerCode IS NULL
I do not understamd the significance of the Null in the where clause. The join is based on BvA.providerCode = A.providerCode etc. IF BvA.providerCode IS NULL then how can the join criteria work.
The query has a right outer join so all [A] records will be included even if no matching [BvA] record is encountered. The NULL criteria then filters the result set to only include [A] records with no matching [BvA] records.
February 17, 2009 at 8:12 am
Thanks. I forgot that it was a RIGHT outer join.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply