December 2, 2009 at 12:23 pm
According to BOL:
Searched CASE function:
Evaluates, in the order specified, Boolean_expression for each WHEN clause.
Returns result_expression of the first Boolean_expression that evaluates to TRUE.
If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.
I have the following searched case statement and it doesn't seem to be working right.
CASE
WHEN ser.Patient_Primary_Policy_Carrier_ID = '-1' Then 'None/Uninsured'
WHEN ISNULL(cval1.Control_Value1,'') = '' Then 'Private Insurance'
ELSE cval1.Control_Value1
END as Primary_Policy_Insurance_Type
I am seeing rows where the Patient_Primary_Policy_Carrier_ID = '-1' but the Primary_Policy_Insurance_Type = 'Private Insurance'.
'Private Insurance' is NOT one of the possible values for cval1.Control_Value1.
I'm sure I'm missing something obvious here but I just don't see it. Any help would be appreciated.
"Beliefs" get in the way of learning.
December 2, 2009 at 2:12 pm
Why are there quotes around the -1? Is that column a varchar? If so, check for leading spaces.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2009 at 2:15 pm
'Private Insurance' is NOT one of the possible values for cval1.Control_Value1.
Maybe not, but the 2nd branch of the case allows for 'Private Insurance' to be returned when Control_Value1 is NULL or ''. Question is, if the ID is -1, why it's falling through the first branch.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2009 at 2:36 pm
I didn't code this but I'm trying to maintain it. The field is an integer so I got rid of the quotes around the '-1' on the off chance that it was causing problems but I'm still seeing the same results.
As for the other thing, I'd be okay with seeing 'Private Insurance' if the preceding boolean test was false and the CTL_Value1 was null but it isn't. It's baffling.
"Beliefs" get in the way of learning.
December 2, 2009 at 2:46 pm
Ok, don't know what the rest of the query looks like, but to be 100% sure that we're looking in the right place at the right rows, please complete and run this.
SELECT ser.Patient_Primary_Policy_Carrier_ID,
cval1.Control_Value1,
'[' + ISNULL(cval1.Control_Value1,'') + ']',
CASE
WHEN ser.Patient_Primary_Policy_Carrier_ID = -1 Then 'Branch 1'
WHEN ISNULL(cval1.Control_Value1,'') = '' Then 'Branch 2'
ELSE 'Default branch'
END as Primary_Policy_Insurance_Type
FROM <Whereever>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2009 at 2:56 pm
Thanks. I've got the query working but I'm out of time to do the analysis. I will get back to it either later tonight or in the morning and I'll let you know what I learn.
Thanks again for your help. That was a good idea.
"Beliefs" get in the way of learning.
December 3, 2009 at 7:23 am
In every case, the query properly branched to 'Branch 1' when Patient_Primary_Policy_carrier_id = -1. Here's the entire query where the searched case is NOT working properly. Maybe you'll see something else that's amiss.
INSERT INTO dbo.DWUDS_Data_Mart_2009_Service
(UDS_Encounter,
Medical_Service,
Client_ID,
Database_ID,
Service_ID,
Patient_ID,
Service_Date_From,
Voucher_Number,
Department_Abbr,
Department_Descr,
Location_Abbr,
Location_Descr,
Last_UDS_Service,
First_UDS_Service,
Provider_Type,
Actual_Provider_Abbr,
Actual_Provider_Name,
Procedure_Code,
Proc_Category_Abbr,
Proc_Category_Descr,
Primary_Diagnosis_Code,
Primary_Diagnosis_Category_Abbr,
Primary_Diagnosis_Category_Descr,
Primary_Policy_Carrier_ID,
Primary_Policy_Effective_Date,
Primary_Policy_Expiration_Date,
Primary_Policy_Insurance_Category_Abbr,
Primary_Policy_Insurance_Group_Abbr,
Primary_Policy_Insurance_Type)
SELECT
0 as UDS_Encounter,
0 as Medical_Service,
ser.Client_ID,
ser.Database_ID,
ser.Service_ID,
ser.Patient_ID,
Service_Date_From,
Voucher_Number,
Department_Abbr,
Department_Descr,
Location_Abbr,
Location_Descr,
0 as Last_UDS_Service,
0 as First_UDS_Service,
ISNULL(cval.Control_Value1,'') as Provider_Type,
Actual_Dr_Abbr,
Actual_Dr_Name,
Procedure_Code,
Proc_Category_Abbr,
Proc_Category_Descr,
Diagnosis1_Code,
Diag1_Category_Abbr,
Diag1_Category_Descr,
ISNULL(Patient_Primary_Policy_Carrier_ID,-1) AS Primary_Policy_Carrier_ID,
ISNULL(Effective_Date,'12/30/1988') AS Primary_Policy_Effective_Date,
ISNULL(Expiration_Date,'12/30/1988') AS Primary_Policy_Expiration_Date,
ISNULL(Patient_Primary_Policy_Carrier_Category_Abbr,'Uninsur') AS Primary_Policy_Insurance_Category_Abbr,
'' AS Primary_Policy_Insurance_Group_Abbr,
CASE
WHEN ser.Patient_Primary_Policy_Carrier_ID = -1 Then 'None/Uninsured'
WHEN ISNULL(cval1.Control_Value1,'') = '' Then 'Private Insurance'
ELSE cval1.Control_Value1
END as Primary_Policy_Insurance_Type
From dbo.PM_Services AS ser
LEFT JOIN dbo.PM_Policies AS pol ON ser.Client_ID = pol.Client_ID AND ser.Database_ID = pol.Database_ID
AND ser.Patient_Primary_Policy_Patient_Policy_ID = pol.Patient_Policy_ID
LEFT JOIN dbo.UDS_Control_Values AS cval ON ser.Client_ID = cval.Client_ID AND ser.Database_ID = cval.Database_ID
AND ser.Actual_Dr_ID = cval.Control_Value AND cval.Control_Type = 31
LEFT JOIN dbo.UDS_Control_Values AS cval1 ON ser.Client_ID = cval1.Client_ID AND ser.Database_ID = cval1.Database_ID
AND ser.Patient_Primary_Policy_Carrier_ID = cval1.Control_Value AND cval1.Control_Type = 29
INNER JOIN dbo.UDS_Control_Values AS cval2 ON ser.Client_ID = cval2.Client_ID AND ser.Database_ID = cval2.Database_ID
AND ser.Location_Abbr = cval2.Control_Value AND cval2.Control_Type = 32
Where Update_Status in (1,2)
AND (@Client_ID = ser.Client_ID)
AND (@Database_ID = ser.Database_ID)
AND (Service_Date_From BETWEEN @Report_From AND @Report_To)
AND Proc_Category_Abbr NOT IN
(SELECT Control_Value FROM dbo.UDS_Control_Values WHERE Client_ID = @Client_id and Database_ID = @Database_ID and Control_Type = 1)
"Beliefs" get in the way of learning.
December 3, 2009 at 8:42 am
Most of the select statement is a non-issue.
Run this, see what it produces. There's got to be something subtly wrong with the values somewhere.
SELECT
ser.Patient_Primary_Policy_Carrier_ID,
'[' + ISNULL(cval1.Control_Value1,'') + ']'
cval1.Control_Value1,
CASE
WHEN ser.Patient_Primary_Policy_Carrier_ID = -1 Then 'None/Uninsured'
WHEN ISNULL(cval1.Control_Value1,'') = '' Then 'Private Insurance'
ELSE cval1.Control_Value1
END as Primary_Policy_Insurance_Type
From dbo.PM_Services AS ser
LEFT JOIN dbo.PM_Policies AS pol ON ser.Client_ID = pol.Client_ID AND ser.Database_ID = pol.Database_ID
AND ser.Patient_Primary_Policy_Patient_Policy_ID = pol.Patient_Policy_ID
LEFT JOIN dbo.UDS_Control_Values AS cval ON ser.Client_ID = cval.Client_ID AND ser.Database_ID = cval.Database_ID
AND ser.Actual_Dr_ID = cval.Control_Value AND cval.Control_Type = 31
LEFT JOIN dbo.UDS_Control_Values AS cval1 ON ser.Client_ID = cval1.Client_ID AND ser.Database_ID = cval1.Database_ID
AND ser.Patient_Primary_Policy_Carrier_ID = cval1.Control_Value AND cval1.Control_Type = 29
INNER JOIN dbo.UDS_Control_Values AS cval2 ON ser.Client_ID = cval2.Client_ID AND ser.Database_ID = cval2.Database_ID
AND ser.Location_Abbr = cval2.Control_Value AND cval2.Control_Type = 32
Where Update_Status in (1,2)
AND (@Client_ID = ser.Client_ID)
AND (@Database_ID = ser.Database_ID)
AND (Service_Date_From BETWEEN @Report_From AND @Report_To)
AND Proc_Category_Abbr NOT IN
(SELECT Control_Value FROM dbo.UDS_Control_Values WHERE Client_ID = @Client_id and Database_ID = @Database_ID and Control_Type = 1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2009 at 9:00 am
That works fine so you're right, there's something subtle but I can't pinpoint it.
I tried moving the searched case to the top of the SELECT just to see if that helped but it still does it wrong. Is this a bug? (Certainly seems like it from my point of view.)
"Beliefs" get in the way of learning.
December 3, 2009 at 9:11 am
Robert Frasca (12/3/2009)
That works fine so you're right, there's something subtle but I can't pinpoint it.
Weird.
Take out the three extra column that I added for testing, see if it still works. If it does, start adding the rest of the columns back, one by one, see where it goes fro working correctly to not working.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2009 at 9:13 am
I'll do that and let you know what happens.
"Beliefs" get in the way of learning.
December 3, 2009 at 9:41 am
ISNULL(Patient_Primary_Policy_Carrier_ID,-1) AS Primary_Policy_Carrier_ID,
Now that I look at it more closely, I'm not surprised that this is the line. It appears to be an order of execution thing as this line is four lines above the CASE. It's almost like, in some cases, the ISNULL test above has not yet occurred. It is interesting to note that I always get exactly the same number of rows that evaluate incorrectly.
"Beliefs" get in the way of learning.
December 3, 2009 at 10:49 am
I didn't even notice that. Aliases are supposed to be applied after the select is evaluated (which is why you can't refer to an aliased column elsewhere in the SELECT clause), but not inconceivable that there's some question of which happens first.
Can you alias that line as something different so that the case can't see the ISNULL'd expression and works on the actual table column? If not, we may have to pull out the subquery hammer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2009 at 12:24 pm
I checked the data and with the business source and was able to determine that the value will never be null. I removed the ISNULL and the problem went away.
"Beliefs" get in the way of learning.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply