Hi,

I have a SQL in Non-ANSI standard which returns different result when i change the SQL according to ANSI norms.

these SQL were ran against MS SQL Server 2000 and 2005.

could someone help me if this is wrong conversion or a Microsoft bug.

SQL in Non-ANSI standard
SELECT
dbo.issue_s.ref_num,
dbo.ca_location.comments,
dbo.ca_location.location_name,
AHD_ctct_Assignee.last_name +' '+ AHD_ctct_Assignee.first_name,
dbo.issue_s.description,
dbo.issue_s.z_Exit_date,
dbo.issue_s.z_Arrive_date,
dbo.issue_s.z_Done_date,
dbo.zAssetdetail.sym,
dbo.zAssetfamily.sym,
dbo.zFixtype.sym,
dbo.zAssetparts.sym,
dbo.issue_s.z_carNo,
dbo.issue_s.close_date,
dbo.issue_s.flag1,
datediff(minute,dbo.issue_s.z_Arrive_date,dbo.issu e_s.z_Exit_date)/60
FROM
dbo.issue_s,
dbo.ca_location,
dbo.ca_contact AHD_ctct_Assignee,
dbo.zAssetdetail,
dbo.zAssetfamily,
dbo.zFixtype,
dbo.zAssetparts,
dbo.ca_contact AHD_ctct_Requestor
WHERE
( dbo.issue_s.requestor=*AHD_ctct_Requestor.contact_ uuid )
AND ( dbo.issue_s.assignee=*AHD_ctct_Assignee.contact_uu id )
AND ( AHD_ctct_Requestor.location_uuid=*dbo.ca_location. location_uuid )
AND ( dbo.issue_s.z_fix_type=*dbo.zFixtype.persid )
AND ( dbo.issue_s.z_asset_family=*dbo.zAssetfamily.persi d )
AND ( dbo.zAssetparts.persid*=dbo.issue_s.z_asset_parts )
AND ( dbo.zAssetdetail.persid*=dbo.issue_s.z_asset_detai l )


SQL in ANSI standard

SELECT
dbo.issue_s.ref_num,
dbo.ca_location.comments,
dbo.ca_location.location_name,
AHD_ctct_Assignee.last_name +' '+ AHD_ctct_Assignee.first_name,
dbo.issue_s.description,
dbo.issue_s.z_Exit_date,
dbo.issue_s.z_Arrive_date,
dbo.issue_s.z_Done_date,
dbo.zAssetdetail.sym,
dbo.zAssetfamily.sym,
dbo.zFixtype.sym,
dbo.zAssetparts.sym,
dbo.issue_s.z_carNo,
dbo.issue_s.close_date,
dbo.issue_s.flag1,
datediff(minute,dbo.issue_s.z_Arrive_date,dbo.issu e_s.z_Exit_date)/60
FROM
dbo.ca_location LEFT OUTER JOIN dbo.ca_contact AHD_ctct_Requestor ON (AHD_ctct_Requestor.location_uuid=dbo.ca_location. location_uuid)
LEFT OUTER JOIN dbo.issue_s ON (dbo.issue_s.requestor=AHD_ctct_Requestor.contact_ uuid)
RIGHT OUTER JOIN dbo.ca_contact AHD_ctct_Assignee ON (dbo.issue_s.assignee=AHD_ctct_Assignee.contact_uu id)
RIGHT OUTER JOIN dbo.zFixtype ON (dbo.issue_s.z_fix_type=dbo.zFixtype.persid)
RIGHT OUTER JOIN dbo.zAssetfamily ON (dbo.issue_s.z_asset_family=dbo.zAssetfamily.persi d)
RIGHT OUTER JOIN dbo.zAssetparts ON (dbo.zAssetparts.persid=dbo.issue_s.z_asset_parts)
RIGHT OUTER JOIN dbo.zAssetdetail ON (dbo.zAssetdetail.persid=dbo.issue_s.z_asset_detai l)

The *= notation should no longer be used. I'm fairly sure this syntax has been removed from later releases of Microsoft SQL Server

What kind of different results are you seeing?

the number of rows fetched are different along with the data.

the notation *= is no longer support to i am migrating to ANSI standards.

with non-ansi it is returning 8 row while with ANSI it is just 5

I'm not much good reading non-ANSI but I think you translated it wrong.

Your "base table" should be issue_s. If I were you, when translating these queries I would dump RIGHT OUTER JOIN and use LEFT OUTER only. I am now convinced right outer is evil. This isn't ANSI SQL specific, but interesting:
On RIGHT OUTER JOINS ...

pootle flump ur codings are working excelent.

You mean you weren't before?

inter-changing the join orientation the results are same.
new SQL will be

dbo.ca_location RIGHT OUTER JOIN dbo.ca_contact AHD_ctct_Requestor ON (AHD_ctct_Requestor.location_uuid=dbo.ca_location. location_uuid)
RIGHT OUTER JOIN dbo.issue_s ON (dbo.issue_s.requestor=AHD_ctct_Requestor.contact_ uuid)
LEFT OUTER JOIN dbo.ca_contact AHD_ctct_Assignee ON (dbo.issue_s.assignee=AHD_ctct_Assignee.contact_uu id)
LEFT OUTER JOIN dbo.zFixtype ON (dbo.issue_s.z_fix_type=dbo.zFixtype.persid)
LEFT OUTER JOIN dbo.zAssetfamily ON (dbo.issue_s.z_asset_family=dbo.zAssetfamily.persi d)
LEFT OUTER JOIN dbo.zAssetparts ON (dbo.zAssetparts.persid=dbo.issue_s.z_asset_parts)
LEFT OUTER JOIN dbo.zAssetdetail ON (dbo.zAssetdetail.persid=dbo.issue_s.z_asset_detai l)


but is it correct one?

t1.c1 *= t2.c1 can be re-written as

T1 LEFT OUTER JOIN T2 ON (t1.c1 = t2.c1) or
T2 RIGHT OUTER JOIN T1 ON (t1.c1 = t2.c1)

Yes. The LEFT and RIGHT are just too different syntaxes for the same operation, which has always seemed daft to me. Like Pootle, I prefer to use only LEFT OUTER joins because I think consistency makes the logic easier to understand.

I have never played with the old Sybase *= syntax much. Some people have said that it could give unreliable results in some cases because the evaluation order wasn't always well defined and was not necessarily the same as the similar ISO syntax. That may be one of the reasons why *= was dropped.

So maybe you should re-state what the actual requirement is rather than trying to reproduce an equivalent of some old code that may or may not do what we think it does.