October 23, 2008 at 8:52 pm
Hi all,
I am trying to convert some Ansi sql code to new version of sql for compliance with SQL Server 2005 code.
(Ansi SQL uses (1) ' =* ' for Right outer join (2) ' *= ' for left outer join and ' = ' sign for inner join)
I have tried converting some code to newer version . Please see below and do let me know, is this right way of doing (or) not.
Thanks.
Code :
Ansi syntax ( valid on 80 compatible databases ( SQL Server 2000) using '=*' for Right outer join and '=' sign for inner join)
Select
FROM v_unit v,
device d,
romver r1,
romver r2,
romver r3,
romver r4,
otap_profile op
WHERE v.cid = @intcid
and v.status < 4
and r1.dsn=*v.dsn and r1.module_id=0
and r2.dsn=*v.dsn and r2.module_id=1001
and r3.dsn=*v.dsn and r3.module_id=1003
and r4.dsn=*v.dsn and r4.module_id=2000
and d.dsn=v.dsn
and op.opid=*d.opid
Modified script for SQL Server 2005:
Select
FROM romver r1
RIGHT OUTER JOIN v_unit v ON r1.dsn = v.dsn
LEFT OUTER JOIN romver r2 ON v.dsn = r2.dsn
LEFT OUTER JOIN romver r3 ON v.dsn = r3.dsn
LEFT OUTER JOIN romver r4 ON v.dsn = r4.dsn
INNER JOIN device d ON v.dsn = d.dsn
LEFT OUTER JOIN otap_profile op ON d.opid = op.opid
WHERE v.cid = @intcid
AND v.status < 4
AND r1.module_id = 0
AND r2.module_id = 1001
AND r3.module_id = 1003
AND r4.module_id = 2000
October 23, 2008 at 10:32 pm
It may not be. Old style outer join syntax confuses me. Conditions in your where clause are independent of your join structure. For instance, if you do this:
SELECT A.*
FROM A LEFT JOIN B ON A.ID = B.ID
WHERE B.ID = 4
You will only get 1 row. (Or however many rows in B have an ID of 4)
However, if you do:
SELECT A.*
FROM A LEFT JOIN B ON A.ID = B.ID AND B.ID = 4
You will get all rows from A, and only the matching rows from B, which is the point of the LEFT join. The only difference between a LEFT join and a RIGHT join is the order that you put the tables.
For instance:
SELECT *
FROM TableA A LEFT JOIN TableB B ON A.ID = B.ID
is the same as:
SELECT *
FROM TableA A RIGHT JOIN TableB B ON B.ID = A.ID
Notice that I swapped the Position of B.ID AND A.ID.
October 23, 2008 at 10:35 pm
Your terminology is not really correct here. What you are calling "Ansi" syntax, is in fact ANSI SQL-89 syntax. What you are calling SQL Server 2005 syntax is actually ANSI SQL-92 syntax. Since SQL-92 syntax superceeded SQL-89 syntax a long time ago (guess when) it is no longer correct to call SQL-89 "Ansi syntax". In fact the JOIN type syntax is properly speaking, the actual "ANSI syntax".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 8:03 am
Can someone provide some info here (i.e.) How to convert OLDER OUTER JOIN to NEWER OUTER JOIN
Can someone please me know :
(1) Is there any utility where we can input our old sql code ( SQL 2000 ) and get new sql code ( for SQL 2005).
(2) can someone let me know , the way I changed the code, is that correct.
Thanks.
October 24, 2008 at 8:42 am
As I recall, here is how you convert:
1) Place all WHERE clause comparisons in the order which their tables occur in the FROM clause. For instance, since "v" occurs in the From clause before "r1", then this "r1.dsn=*v.dsn" should be changed to this "v.dsn*=r1.dsn=".
2) Change all of the commas in the FROM clause to JOINS: If an "*" appears in the where clause for a table to table comparision, then the JOIN between them should be a LEFT or RIGHT OUTER JOIN, with the LEFT or RIGHT reflecting which side the "*" appears on. Otherwise a JOIN should be an INNER JOIN.
3) For the outer joins, move ALL of the related where clauses into the appropiate ON clauses.
4) For the INNER JOINS, you move some or all of the where clauses into the ON clauses (I would suggest only moving the "table1.column=table2.column" type where clauses into the ON clauses).
So I would convert your example to this:
Select
FROM v_unit v
Inner JOIN device d ON (d.dsn=v.dsn)
Left Outer Join romver r1 ON (r1.dsn=v.dsn and r1.module_id=0)
Left Outer Join romver r2 ON (r2.dsn=v.dsn and r2.module_id=1001)
Left Outer Join romver r3 ON (r3.dsn=v.dsn and r3.module_id=1003)
Left Outer Join romver r4 ON (r4.dsn=v.dsn and r4.module_id=2000)
Left Outer Join otap_profile op ON (op.opid=d.opid)
WHERE v.cid = @intcid
and v.status < 4
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply