May 27, 2011 at 2:22 pm
I just ran accross a query that uses "=*" in a where clause. I know I read an article recently about this, but can't seem to find it now. How would I re-write this query to eliminate it? I think I read that it was a depricated join syntax... any explaination would also be greatly appreciated because I'm pretty sure I'm going to hit it many times in this particular DB.
SELECT tb_Action.Action_id,
tb_Action.off1_cd,
tb_Action.off2_cd,
tb_Action.off3_cd,
tb_Action.descr_text,
at_pdesc_off.pers_kind_cd,
tb_Action.Action_from_dt,
tb_Action.Action_to_dt,
tb_Action.Action_date_type,
tb_Action.Action_date_approx,
tb_Action.Action_date_range,
tb_Action.Action_date_unit,
tb_Action.legacy_source,
tb_Action.file_id,
tb_Action.rpt_seq,
999 as access_right,
tb_Action.formatted_name,
tb_code_value.code_display
FROM at_pdesc_off,
tb_Action,
tb_code_value
WHERE ( tb_Action.Action_id = at_pdesc_off.Action_id ) and
( ( at_pdesc_off.file_id = 'N3B2110001' ) AND
( at_pdesc_off.rpt_seq = 1 ) AND
( at_pdesc_off.pdescr_id = '00006595MSTS2473' ) AND
( tb_Action.delete_flg = 'N' ) ) AND
( tb_code_value.code_subcat = 'PERS_KIND/PDOF' ) AND
( tb_code_value.code_value =* at_pdesc_off.pers_kind_cd )
ORDER BY tb_Action.Action_from_dt ASC,
tb_Action.off3_cd ASC,
at_pdesc_off.pers_kind_cd ASC
Thanks
Kevin
May 27, 2011 at 2:43 pm
That is deprecated syntax for doing a join. Remove it from your where clause and replace
FROM at_pdesc_off,
tb_Action,
tb_code_value
with standard JOIN syntax
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 27, 2011 at 3:10 pm
I think =* in particular is a RIGHT OUTER JOIN?
May 27, 2011 at 3:19 pm
The =* JOIN-syntax is only valid when a 2008 database (the forum you posted in) is in 80 compatibility mode.
If you think you may run into this a lot here is a post that explains how to use SSMS to help you get a starting point for some of these query conversions.
http://www.sqlservercentral.com/Forums/FindPost1098339.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 27, 2011 at 3:19 pm
It is the RIGHT OUTER JOIN. Re-writing queries that use outer joins can be tricky. Once you have it looking like it should make sure that it returns the data the way you expect. I experienced some surprises that were fairly easy to fix.
CEWII
May 27, 2011 at 3:25 pm
The Dixie Flatline (5/27/2011)
That is deprecated syntax for doing a join.
It has been deprecated since SQL 2005 and support will be officially dropped from the next version of SQL Server (SQL 11 / "Denali") via the dropping of support for 80 compatibility mode.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 27, 2011 at 3:35 pm
Thanks for all the quick responses!
Dixie/diamondgm - yes, I was pretty sure the article I read defined it as "Right outer join" and that it was depricated.
opc.three - yeah, I realise this is a 2008 forum, but I had to make a decision. I knew it was old syntax, but I'm working in 2008r2, so that's the version I was looking for advise on 😉
Elliott - yes, that's why I was looking for an example on converting THIS query hoping I'd get a good understanding before moving forward.
it's the end of a rough week/day and my brain is tired. I think I understand it, but I need to get a few minutes to concentrate on it so that I can pull it together. First I had to do a memory check to make sure =* really was right outer join 😉
thanks again!!
and a good weekend to all.
Kevin
May 27, 2011 at 7:03 pm
Uripedes Pants (5/27/2011)
opc.three - yeah, I realise this is a 2008 forum, but I had to make a decision. I knew it was old syntax, but I'm working in 2008r2, so that's the version I was looking for advise on 😉
Sorry if my comment implied anything that offended you. You knew the syntax might have been deprecated but didn't mention you were in compat mode so I thought you may still be on 2000. I probably should never post after 2PM on a Friday because I tend to have lost some of my sensibilities by this time in the week 😀
Have a great weekend!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 31, 2011 at 4:57 am
SELECT *
FROM at_pdesc_off
JOIN tb_Action
ON at_pdesc_off.Action_id = tb_Action.Action_id
AND tb_Action.delete_flg = 'N'
-- * is against the table where all the rows should be kept.
LEFT JOIN tb_code_value
ON at_pdesc_off.pers_kind_cd = tb_code_value.code_value
AND tb_code_value.code_subcat = 'PERS_KIND/PDOF'
WHERE at_pdesc_off.[file_id] = 'N3B2110001'
AND at_pdesc_off.rpt_seq = 1
AND at_pdesc_off.pdescr_id = '00006595MSTS2473'
May 31, 2011 at 7:59 am
All - thanks for all the replies. It wasn't really fair of me to post an hour or so before a long weekend, but all replies were appreciated.
opc.three - nope, you didn't say anything that I took offensively. You're idea about the query builder looks pretty interesting.
Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply