September 10, 2013 at 1:06 pm
I was given this query this morning. It's not a pattern of syntax I can ever recall seeing before. I guess it works, but I can't figure out how to write a query using these conventions. Does this make any sense to anyone here?
(Note - I've replaced the table names with 'aaaa', 'bbbb', etc in a lame attempt to disguise where the data comes from, but aside from that, no changes.
select rtrim(dbo.aaaa.vst_ext_id),
rtrim(convert(char,dbo.aaaa.adm_ts,1)),
rtrim(convert(char,dbo.aaaa.dschrg_ts,1)),
rtrim(convert(char,bth_ts,1)),
datediff(year,bth_ts,dbo.aaaa.dschrg_ts),
isnull(pat_calc_age_no,0),
isnull(pat_calc_age_unit,''),
rtrim(bbbb.cod_dtl_ext_id),
rtrim(cccc.cod_dtl_ext_id),
rtrim(dddd.cod_dtl_ext_id),
rtrim(dbo.eeee.plan_ext_id),
rtrim(ffff.cod_dtl_ext_id)
from dbo.kkkk right join
(dbo.eeee right join
(dbo.gggg right join
(dbo.hhhh ffff inner join
(dbo.hhhh dddd inner join
(dbo.jjjj inner join
(dbo.hhhh cccc inner join
(dbo.hhhh bbbb inner join dbo.aaaa
on bbbb.cod_dtl_int_id = dbo.aaaa.pat_ty)
on dbo.aaaa.vst_sta_cd = cccc.cod_dtl_int_id)
on dbo.jjjj.psn_int_id = dbo.aaaa.psn_int_id)
on dddd.cod_dtl_int_id = dbo.aaaa.fin_cls_cd)
on ffff.cod_dtl_int_id = dbo.aaaa.dschg_srv_cd)
on dbo.gggg.vst_int_id = dbo.aaaa.vst_int_id)
on dbo.eeee.plan_int_id = dbo.gggg.plan_int_id)
on dbo.kkkk.vst_int_id = dbo.aaaa.vst_int_id
where cccc.cod_dtl_ext_id <> 'ADMIT' and
dbo.gggg.pyr_seq_no = 4981 and
dbo.aaaa.dschrg_ts > '12/31/2003' and
dbo.aaaa.dschrg_ts < '1/1/2005'
order by VisitID
September 10, 2013 at 1:51 pm
rray 44280 (9/10/2013)
I was given this query this morning. It's not a pattern of syntax I can ever recall seeing before. I guess it works, but I can't figure out how to write a query using these conventions. Does this make any sense to anyone here?(Note - I've replaced the table names with 'aaaa', 'bbbb', etc in a lame attempt to disguise where the data comes from, but aside from that, no changes.
select rtrim(dbo.aaaa.vst_ext_id),
rtrim(convert(char,dbo.aaaa.adm_ts,1)),
rtrim(convert(char,dbo.aaaa.dschrg_ts,1)),
rtrim(convert(char,bth_ts,1)),
datediff(year,bth_ts,dbo.aaaa.dschrg_ts),
isnull(pat_calc_age_no,0),
isnull(pat_calc_age_unit,''),
rtrim(bbbb.cod_dtl_ext_id),
rtrim(cccc.cod_dtl_ext_id),
rtrim(dddd.cod_dtl_ext_id),
rtrim(dbo.eeee.plan_ext_id),
rtrim(ffff.cod_dtl_ext_id)
from dbo.kkkk right join
(dbo.eeee right join
(dbo.gggg right join
(dbo.hhhh ffff inner join
(dbo.hhhh dddd inner join
(dbo.jjjj inner join
(dbo.hhhh cccc inner join
(dbo.hhhh bbbb inner join dbo.aaaa
on bbbb.cod_dtl_int_id = dbo.aaaa.pat_ty)
on dbo.aaaa.vst_sta_cd = cccc.cod_dtl_int_id)
on dbo.jjjj.psn_int_id = dbo.aaaa.psn_int_id)
on dddd.cod_dtl_int_id = dbo.aaaa.fin_cls_cd)
on ffff.cod_dtl_int_id = dbo.aaaa.dschg_srv_cd)
on dbo.gggg.vst_int_id = dbo.aaaa.vst_int_id)
on dbo.eeee.plan_int_id = dbo.gggg.plan_int_id)
on dbo.kkkk.vst_int_id = dbo.aaaa.vst_int_id
where cccc.cod_dtl_ext_id <> 'ADMIT' and
dbo.gggg.pyr_seq_no = 4981 and
dbo.aaaa.dschrg_ts > '12/31/2003' and
dbo.aaaa.dschrg_ts < '1/1/2005'
order by VisitID
UGH!!! That looks like a query that was generated with the query wizard or Access. I had to unravel those nightmares in the past. You have to work backwards and change them to more standard join syntax.
I think this should be the same thing. If not, I think it is at least close.
select rtrim(dbo.aaaa.vst_ext_id),
rtrim(convert(char,dbo.aaaa.adm_ts,1)),
rtrim(convert(char,dbo.aaaa.dschrg_ts,1)),
rtrim(convert(char,bth_ts,1)),
datediff(year,bth_ts,dbo.aaaa.dschrg_ts),
isnull(pat_calc_age_no,0),
isnull(pat_calc_age_unit,''),
rtrim(bbbb.cod_dtl_ext_id),
rtrim(cccc.cod_dtl_ext_id),
rtrim(dddd.cod_dtl_ext_id),
rtrim(dbo.eeee.plan_ext_id),
rtrim(ffff.cod_dtl_ext_id)
from dbo.kkkk
inner join dbo.aaaa on dbo.kkkk.vst_int_id = dbo.aaaa.vst_int_id
inner join dbo.hhhh bbbb on bbbb.cod_dtl_int_id = dbo.aaaa.pat_ty
inner join dbo.hhhh cccc on dbo.aaaa.vst_sta_cd = cccc.cod_dtl_int_id
inner join dbo.jjjj on dbo.jjjj.psn_int_id = dbo.aaaa.psn_int_id
inner join dbo.hhhh dddd on dddd.cod_dtl_int_id = dbo.aaaa.fin_cls_cd
right join dbo.hhhh ffff on ffff.cod_dtl_int_id = dbo.aaaa.dschg_srv_cd
right join dbo.gggg on dbo.gggg.vst_int_id = dbo.aaaa.vst_int_id
right join dbo.eeee on dbo.eeee.plan_int_id = dbo.gggg.plan_int_id
where cccc.cod_dtl_ext_id <> 'ADMIT' and
dbo.gggg.pyr_seq_no = 4981 and
dbo.aaaa.dschrg_ts > '12/31/2003' and
dbo.aaaa.dschrg_ts < '1/1/2005'
order by VisitID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2013 at 7:42 am
That's actually a syntax I use sort of. The JOIN order is being enforced by the order of the ON clauses. I basically never user RIGHT JOIN's but convert them to LEFT's because I find them more readable. I use this syntax when there is a LEFT JOIN to a table and then that table needs to be JOINed to a type table something like this:
SELECT
*
FROM
dbo.CUSTOMER AS C
LEFT JOIN dbo.ORDERS AS O
JOIN dbo.ORDER_STATUS AS OS
ON OS.ORDER_STATUS_ID = O.ORDER_STATUS_ID
ON O.CUSTOMER_ID = C.CUSTOMER_ID
BY placing the ON for the JOIN from CUSTOMERS to ORDERS AFTER the JOIN from ORDERS to ORDER_STATUS the LEFT JOIN is enforced. You could also do it with PARENTHESES.
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
September 12, 2013 at 7:55 am
I agree with Sean's version. As you have found out this is a nightmare to figure out, it may work but it doesn't make it right. I think you always need to keep in mind when writing code that someone else may have to maintain it. Just because you understand it doesn't mean everyone else will.
If this is the 'best' way to write the code, I would rather have the 'good' version of Sean's, sorry Jack.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
September 12, 2013 at 8:02 am
below86 (9/12/2013)
I agree with Sean's version. As you have found out this is a nightmare to figure out, it may work but it doesn't make it right. I think you always need to keep in mind when writing code that someone else may have to maintain it. Just because you understand it doesn't mean everyone else will.If this is the 'best' way to write the code, I would rather have the 'good' version of Sean's, sorry Jack.
No problem, I was just answering your question about having seen that type of syntax before with how I use it. I didn't really analyze the code you posted, and, if I had, I probably would have rewritten it using LEFT JOIN's because I have trouble with RIGHT JOIN's, my brain just doesn't process them well.
Sean's code is definitely more readable and maintainable than the original.
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
September 12, 2013 at 8:12 am
I'm the original poster and I can't even figure out why the query *should* work, let alone whether it does or not. I'm pretty certain Jack didn't think it was a thing of beauty, but his comment did help me understand a bit about why the guy I inherited this from might have done it that way.
In any event, I will endeavor to find an algorithmic method of converting the syntax I posted into more conventional form as I have dozens (hundreds?) of these queries to try to figure out!
September 12, 2013 at 8:15 am
Jack Corbett (9/12/2013)
below86 (9/12/2013)
I agree with Sean's version. As you have found out this is a nightmare to figure out, it may work but it doesn't make it right. I think you always need to keep in mind when writing code that someone else may have to maintain it. Just because you understand it doesn't mean everyone else will.If this is the 'best' way to write the code, I would rather have the 'good' version of Sean's, sorry Jack.
No problem, I was just answering your question about having seen that type of syntax before with how I use it. I didn't really analyze the code you posted, and, if I had, I probably would have rewritten it using LEFT JOIN's because I have trouble with RIGHT JOIN's, my brain just doesn't process them well.
Sean's code is definitely more readable and maintainable than the original.
I agree Jack. Right joins are really difficult to wrap your brain around most of the time. I also tend to flip the tables around so it can be a left join instead.
I doubt that the code I posted will produce the same results as the original query because I did not spend a lot of effort trying to unravel the right joins and parenthesis.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2013 at 8:16 am
I agree I would do left joins also, I can't remember writing a right join. I didn't post the code, and I'm sorry when I read your post I made the assumption it was the way you would have coded it. I see now you were just commenting on it, sorry.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
September 12, 2013 at 1:45 pm
Sean Lange (9/12/2013)
I agree Jack. Right joins are really difficult to wrap your brain around most of the time. I also tend to flip the tables around so it can be a left join instead.I doubt that the code I posted will produce the same results as the original query because I did not spend a lot of effort trying to unravel the right joins and parenthesis.
I'm sure it won't. In the original, absence of a row in kkkk which doesn't match a row in aaaa on the vst_int_id attribute doesn't mean that the aaaa row will be excluded from the results (because of the right join between kkkk and some stuff including aaaa on kkkk.vst_int_id=aaaa.vst_int_id); in your code that is an inner join, so failure of the on condition can exclude the row in aaaa.
Tom
September 13, 2013 at 12:10 pm
Looking at this reinforces the fact that not all programmers are good programmers. Some coders just don't get it. Sorry.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply