Jeff Moden (6/27/2008)
I don't see where you've used that particular ISNULL as a join. It think it would be a problem if it were actually in a join because it uses two tables. But, using it in the SELECT list of a derived table like you have is not a problem. In fact, that's one of the reasons why ISNULL and COALESCE exist.
Sorry, didn't use that in this example. What I ended up doing in my live version is this:
LEFT JOIN #member ON (ISNULL(PLANDATA_Claim.enrollid,ENCOUNTER_Claim.enrollid) = #member.enrollid )
Had to do that because the member had multiple enrollments in the member table, but I only wanted the one on the claim. (I didn't have that column in the #member or claims tables in my example, guess I wrote this up before I thought of it)
SQL's not yelling at me, and I'm getting results, so I think it's working, but it's kind of bothering me, because it seems like it could be wrong. The two claim tables are mutually exclusive, and theoretically every claimid will be in one of the tables.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."