Bad join [modified]
-
I was debugging a problem where one policy was not returning a rate. As I drilled into the problem it became clear there was a bad join, I just had no idea how bad. Here it is, all 1732 characters of it.
join policy_rates.dbo.policy_type_rate on rating_exercise.rating_exercise_id=policy_type_rate.rating_exercise_id and RatingPolicyTypeInfo.policy_type_id=policy_type_rate.policy_type_id and isnull(convert(int,datediff(d,birth_date,RatingPolicyTypeInfo.effective_date)/365.25),case when case when issue_date<case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end then convert(int,datediff(d,birth_date,case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end)/365.25) when isnull(uw_issue_date,issue_date)<issue_date then convert(int,datediff(d,birth_date,uw_issue_date)/365.25) else issue_age end<18 then 18 else case when issue_date<case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end then convert(int,datediff(d,birth_date,case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end)/365.25) when isnull(uw_issue_date,issue_date)<issue_date then convert(int,datediff(d,birth_date,uw_issue_date)/365.25) else issue_age end end) between policy_type_rate.minimum_age_number and policy_type_rate.maximum_age_number and convert(int,datediff(d,birth_date,getdate())/365.25) between isnull(policy_type_rate.minimum_current_age_number,18) and isnull(policy_type_rate.maximum_current_age_number,110)
I'm not sure if it's a good or bad thing that I can just forwarded the problem to the original developer. Ed: I formatted this so I could tell what was going on. Maybe this will help others - Chris
JOIN policy_rates.dbo.policy_type_rate
ON rating_exercise.rating_exercise_id = policy_type_rate.rating_exercise_id
AND RatingPolicyTypeInfo.policy_type_id = policy_type_rate.policy_type_id
AND ISNULL(CONVERT(INT,DATEDIFF(d,birth_date,RatingPolicyTypeInfo.effective_date)/365.25),
CASE
WHEN
CASE
WHEN issue_date < -
I was debugging a problem where one policy was not returning a rate. As I drilled into the problem it became clear there was a bad join, I just had no idea how bad. Here it is, all 1732 characters of it.
join policy_rates.dbo.policy_type_rate on rating_exercise.rating_exercise_id=policy_type_rate.rating_exercise_id and RatingPolicyTypeInfo.policy_type_id=policy_type_rate.policy_type_id and isnull(convert(int,datediff(d,birth_date,RatingPolicyTypeInfo.effective_date)/365.25),case when case when issue_date<case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end then convert(int,datediff(d,birth_date,case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end)/365.25) when isnull(uw_issue_date,issue_date)<issue_date then convert(int,datediff(d,birth_date,uw_issue_date)/365.25) else issue_age end<18 then 18 else case when issue_date<case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end then convert(int,datediff(d,birth_date,case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end)/365.25) when isnull(uw_issue_date,issue_date)<issue_date then convert(int,datediff(d,birth_date,uw_issue_date)/365.25) else issue_age end end) between policy_type_rate.minimum_age_number and policy_type_rate.maximum_age_number and convert(int,datediff(d,birth_date,getdate())/365.25) between isnull(policy_type_rate.minimum_current_age_number,18) and isnull(policy_type_rate.maximum_current_age_number,110)
I'm not sure if it's a good or bad thing that I can just forwarded the problem to the original developer. Ed: I formatted this so I could tell what was going on. Maybe this will help others - Chris
JOIN policy_rates.dbo.policy_type_rate
ON rating_exercise.rating_exercise_id = policy_type_rate.rating_exercise_id
AND RatingPolicyTypeInfo.policy_type_id = policy_type_rate.policy_type_id
AND ISNULL(CONVERT(INT,DATEDIFF(d,birth_date,RatingPolicyTypeInfo.effective_date)/365.25),
CASE
WHEN
CASE
WHEN issue_date <Aren't you just so glad we are no longer stuck with 80 character coding sheets... :laugh:
If Barbie is so popular, why do you have to buy her friends? Eagles may soar, but weasels don't get sucked into jet engines. If at first you don't succeed, destroy all evidence that you tried.
-
I was debugging a problem where one policy was not returning a rate. As I drilled into the problem it became clear there was a bad join, I just had no idea how bad. Here it is, all 1732 characters of it.
join policy_rates.dbo.policy_type_rate on rating_exercise.rating_exercise_id=policy_type_rate.rating_exercise_id and RatingPolicyTypeInfo.policy_type_id=policy_type_rate.policy_type_id and isnull(convert(int,datediff(d,birth_date,RatingPolicyTypeInfo.effective_date)/365.25),case when case when issue_date<case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end then convert(int,datediff(d,birth_date,case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end)/365.25) when isnull(uw_issue_date,issue_date)<issue_date then convert(int,datediff(d,birth_date,uw_issue_date)/365.25) else issue_age end<18 then 18 else case when issue_date<case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end then convert(int,datediff(d,birth_date,case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end)/365.25) when isnull(uw_issue_date,issue_date)<issue_date then convert(int,datediff(d,birth_date,uw_issue_date)/365.25) else issue_age end end) between policy_type_rate.minimum_age_number and policy_type_rate.maximum_age_number and convert(int,datediff(d,birth_date,getdate())/365.25) between isnull(policy_type_rate.minimum_current_age_number,18) and isnull(policy_type_rate.maximum_current_age_number,110)
I'm not sure if it's a good or bad thing that I can just forwarded the problem to the original developer. Ed: I formatted this so I could tell what was going on. Maybe this will help others - Chris
JOIN policy_rates.dbo.policy_type_rate
ON rating_exercise.rating_exercise_id = policy_type_rate.rating_exercise_id
AND RatingPolicyTypeInfo.policy_type_id = policy_type_rate.policy_type_id
AND ISNULL(CONVERT(INT,DATEDIFF(d,birth_date,RatingPolicyTypeInfo.effective_date)/365.25),
CASE
WHEN
CASE
WHEN issue_date <Never realized that SQL allows you to place case statements within a join statement, though I guess nothing would syntatically prevent such. I would highly recommend placing those conversions/etc. into the secondary query being joined (or wrapped in a second query) just to remove the eyebleeding IsNull that trails into the mile long case statements. :cool:
-
I was debugging a problem where one policy was not returning a rate. As I drilled into the problem it became clear there was a bad join, I just had no idea how bad. Here it is, all 1732 characters of it.
join policy_rates.dbo.policy_type_rate on rating_exercise.rating_exercise_id=policy_type_rate.rating_exercise_id and RatingPolicyTypeInfo.policy_type_id=policy_type_rate.policy_type_id and isnull(convert(int,datediff(d,birth_date,RatingPolicyTypeInfo.effective_date)/365.25),case when case when issue_date<case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end then convert(int,datediff(d,birth_date,case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end)/365.25) when isnull(uw_issue_date,issue_date)<issue_date then convert(int,datediff(d,birth_date,uw_issue_date)/365.25) else issue_age end<18 then 18 else case when issue_date<case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end then convert(int,datediff(d,birth_date,case when plan_provision_value_effective_date is null or coverage.effective_date>plan_provision_value_effective_date then coverage.effective_date else plan_provision_value_effective_date end)/365.25) when isnull(uw_issue_date,issue_date)<issue_date then convert(int,datediff(d,birth_date,uw_issue_date)/365.25) else issue_age end end) between policy_type_rate.minimum_age_number and policy_type_rate.maximum_age_number and convert(int,datediff(d,birth_date,getdate())/365.25) between isnull(policy_type_rate.minimum_current_age_number,18) and isnull(policy_type_rate.maximum_current_age_number,110)
I'm not sure if it's a good or bad thing that I can just forwarded the problem to the original developer. Ed: I formatted this so I could tell what was going on. Maybe this will help others - Chris
JOIN policy_rates.dbo.policy_type_rate
ON rating_exercise.rating_exercise_id = policy_type_rate.rating_exercise_id
AND RatingPolicyTypeInfo.policy_type_id = policy_type_rate.policy_type_id
AND ISNULL(CONVERT(INT,DATEDIFF(d,birth_date,RatingPolicyTypeInfo.effective_date)/365.25),
CASE
WHEN
CASE
WHEN issue_date <Jeremy Hutchinson wrote:
I'm not sure if it's a good or bad thing that I can just forwarded the problem to the original developer.
I think you should hire the original developer if he is not part of your company. Anyone who can handle that monster join is definetly a good asset for the company. :laugh:
I have no smart signature yet...
-
Jeremy Hutchinson wrote:
I'm not sure if it's a good or bad thing that I can just forwarded the problem to the original developer.
I think you should hire the original developer if he is not part of your company. Anyone who can handle that monster join is definetly a good asset for the company. :laugh:
I have no smart signature yet...
That developer does still work here. What I need is someone who is not only smart enough to handle that monster join, but also smart enough not to write it. At the very least don't write it as one continuous line with no spaces. Throw a little formatting in there.
-
That developer does still work here. What I need is someone who is not only smart enough to handle that monster join, but also smart enough not to write it. At the very least don't write it as one continuous line with no spaces. Throw a little formatting in there.
Jeremy Hutchinson wrote:
Throw a little formatting in there.
I looked, it's still about as appealing as having a limb turned around.
Panic, Chaos, Destruction. My work here is done. or "Drink. Get drunk. Fall over." - P O'H
-
That developer does still work here. What I need is someone who is not only smart enough to handle that monster join, but also smart enough not to write it. At the very least don't write it as one continuous line with no spaces. Throw a little formatting in there.
Jeremy Hutchinson wrote:
to handle that monster
Is the monster a manager now? :laugh:
Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
The woods are lovely, dark and deep, But I have promises to keep, And miles to go before I sleep, And miles to go before I sleep!
