Results 1 to 7 of 7

Thread: SQL conversion

  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Thumbs down Unanswered: SQL conversion

    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)

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2009
    Posts
    3

    result

    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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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 ...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flum
    I am now convinced
    You mean you weren't before?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2009
    Posts
    3

    Red face chnages

    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)

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by srivastava.hemant
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •