One thing that constantly amazes me is how few troubleshooting skills many coders have. To me, it’s as natural as breathing to go through one step at a time, commenting out code lines in huge chunks and slowly adding them back in to locate my error. Reverse engineer my own code, so to speak. And in that spirit, let me share you something that’s been tripping me up for almost an hour.
My code goes something like this:
Declare @Sev1 varchar(8), @Sev2 varchar(8), @BeginDate datetime, @EndDate datetime
Set @Sev1 = ‘Top4’
Set @Sev2 = ‘Bottom3′ — pretend I set the dates here —
Select fcs.store_id, …many columns here…., ISNULL(ddc3.value,’NA’) as Col1, ISNULL(ddc4.value,’NA’) as Col2,
–ddc4.value, @Sev2, vLTRIM(Substring(ddc3.value,4,9)),
fcs.DateOpened, fcs.DateClosed
from dbo.Stores fcs
join … insert 8 table joins here…
join dbo.dropdown_code ddc3
on fcs.StoreCol5 = ddc3.code
and ddc3.locale = 409 and ddc3.type = ‘StoreType1’
and (@Sev1 = LTRIM(Substring(ddc3.value,4,9)) or @Sev1 = ‘All’)
join dbo.dropdown_code ddc4
on fcs.StoreCol6 = ddc4.code
and ddc4.locale = 409 and ddc4.type = ‘StoreType2’
and (@Sev2 = LTRIM(Substring(ddc3.value,4,9)) or @Sev2 = ‘All’)
Where fcs.DateOpened > @BeginDate and fcs.DateOpened <= @EndDate
Now this is pseudo-code, obviously, and I’m missing some Declare statements for my variables, but you get the idea of what I’m dealing with.
I’m not getting any results by having my two @Sev variables set. But I’ve physically checked my dropdown_code table and know for a fact that the values are THERE and that the records in my original FROM clause points to those values. Frustrating. So I take my code, cut-n-paste it to a new window, and cut out all the extraneous tables and columns that don’t have anything to do with these values. Then I start experimenting with changing my ON clauses for the JOINS. I first solve my @Sev1 problem (forgot to trim my SubString, it was putting a space before my value), then I’m having a problem with @Sev2.
So I start all over and just as I’m getting to the offending SubString JOIN condition, I see it. Do you?
I’m joining my variable to the table ABOVE the current table (aliased ddc3) instead of the current table (aliased ddc4). And I never would have caught it if I hadn’t split my code out, deleting anything that wasn’t relevant to the problem at hand. By shortening my code for troubleshooting, I made it easier to scan and find my mistakes.
So, maybe this post will prevent someone from banging their head against the wall. Maybe everyone already practices this technique. Regardless, I thought I’d mention it. Now I just have to remember to add it to my SQL Saturday JOINS presentation for next month.
