Blog

15Jan
2006
SQL Server 2005 Outer Join Gotcha

If you are upgrading to SQL Server 2005, pay attention to this one ...

SQL Server has long supported two forms of OUTER JOIN syntax, the ANSI syntax (using LEFT OUTER JOIN, RIGHT OUTER JOIN, etc.), and the simplified T-SQL syntax (using *= and =*). If you've always used ANSI syntax then you are safe, but if you have any existing code that uses the simplified T-SQL syntax, that code will not run on SQL Server 2005, and the following error message will be returned:

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Fortunately, that message also provides the solution. Using the sp_dbcmptlevel stored procedure you set the backwards compatibility level so that the old style outer joins work. Until you manually fix them all, that is.

Comments (38)



  • Jared Rypka-Hauer

    Find/Replace to the rescue!

    Well... at least find would let you track down the places where you used the fancy, non-standard syntax. I hadn't heard of this syntax until about 6 weeks ago, actually. Does anyone actually use it?

    Laterz...
    J

  • 6dust

    Absolutely! That shortcut method is used by all of the Oracle developers I work with.

    Unfortunately, my work place just recently added a Teradata server, which only supports the ANSI syntax, so we've had to do a lot of these same re-writes.

    #2Posted by 6dust | Jan 16, 2006, 10:05 AM
  • Jason

    Yeah, the *= notation is the ONLY way you can execute a JOIN using Oracle native drivers. Luckily, though, we haven't had a client insist on the Oracle natives for several years. Since that was a custom app, we didn't end up with a widely used codebase of *= statements, and, boy, am I glad now!

    Long live standards!

    J

    #3Posted by Jason | Jan 16, 2006, 10:44 AM
  • Amy Luttrell

    How do you work around this problem if you are using joined tables within the same query?

    #4Posted by Amy Luttrell | May 17, 2006, 03:20 PM
  • Ben Forta

    Amy, you can do outer self joins using LEFT OUTER and LEFT INNER suntax and table aliases.

    --- Ben

    #5Posted by Ben Forta | May 17, 2006, 03:23 PM
  • Jason

    Basically, instead of:

    SELECT p.firstName, u.userName
    FROM person p, user u
    WHERE p.personID = u.personID
    AND p.personID = '#url.id#'

    use

    SELECT p.firstName, u.userName
    FROM person p INNER JOIN
    user u ON p.personID = u.personID
    WHERE p.personID = '#url.id#'

    In the case of OUTER JOINs, instead of:

    SELECT p.firstName, ISNULL(u.userName, 'noUserName') AS userName
    FROM person p, user u
    WHERE p.personID *= u.personID
    AND p.personID = '#url.id#'

    use

    SELECT p.firstName, ISNULL(u.userName, 'noUserName') AS userName
    FROM person p LEFT OUTER JOIN
    user u ON p.personID = u.personID
    WHERE p.personID = '#url.id#'


    HTH,
    J

    #6Posted by Jason | May 17, 2006, 03:30 PM
  • Amy Luttrell

    I started using the T-SQL syntax because I was doing inner and outer joins within the same query. Now that I have to use the ANSI syntax I am not sure how to combine inner and outer joins or if I even can.

    Here is a sample of the query.

    select a.ApplName, a.Appl, c.CatName, s.SubCatName,h.OpHours,h.Target
    from tbl_dcHours as h, tbl_dcApps as a, tbl_dcCat as c, tbl_dcSubCat as s
    where h.MonthNum=4 and h.YearNum=2006 and a.ApplID=h.ApplID
          and a.CatID=c.CatID and a.SubCatID*=s.SubCatID
    order by c.CatName,s.SubCatName,a.ApplName

    #7Posted by Amy Luttrell | May 17, 2006, 03:46 PM
  • Amy Luttrell

    I started using the T-SQL syntax because I was doing inner and outer joins within the same query. Now that I have to use the ANSI syntax I am not sure how to combine inner and outer joins or if I even can.

    Here is a sample of the query.

    select a.ApplName, a.Appl, c.CatName, s.SubCatName,h.OpHours,h.Target
    from tbl_dcHours as h, tbl_dcApps as a, tbl_dcCat as c, tbl_dcSubCat as s
    where h.MonthNum=4 and h.YearNum=2006 and a.ApplID=h.ApplID
          and a.CatID=c.CatID and a.SubCatID*=s.SubCatID
    order by c.CatName,s.SubCatName,a.ApplName

    #8Posted by Amy Luttrell | May 17, 2006, 04:01 PM
  • Amy Luttrell

    I started using the T-SQL syntax because I was doing inner and outer joins within the same query. Now that I have to use the ANSI syntax I am not sure how to combine inner and outer joins or if I even can.

    Here is a sample of the query.

    select a.ApplName, a.Appl, c.CatName, s.SubCatName,h.OpHours,h.Target
    from tbl_dcHours as h, tbl_dcApps as a, tbl_dcCat as c, tbl_dcSubCat as s
    where h.MonthNum=4 and h.YearNum=2006 and a.ApplID=h.ApplID
          and a.CatID=c.CatID and a.SubCatID*=s.SubCatID
    order by c.CatName,s.SubCatName,a.ApplName

    #9Posted by Amy Luttrell | May 17, 2006, 04:02 PM
  • Jason

    Amy, this should be the equivalent:

    select a.ApplName, a.Appl, c.CatName, s.SubCatName, h.OpHours, h.Target
    from tbl_dcHours h inner join
       tbl_dcApps a on h.ApplID = a.ApplID inner join
       tbl_dcCat c on a.CatID = c.CatID left outer join
       tbl_dcSubCat s on a.SubCatID = s.SubCatID
    where h.MonthNum = 4
       and h.YearNum = 2006
    order by c.CatName, s.SubCatName, a.ApplName


    J

    #10Posted by Jason | May 17, 2006, 04:03 PM
  • RK

    Hi,

    I have the following script in which the old-style left join( *= ) is being used to join four separate tables, how do I change this script to the new ANSI standard format i.e. using LEFT OUTER JOIN?

    Thanks!

    -------------------------------------------------------------------------------------------------

    SELECT ..............


    FROM
    iv_execution_history_table,
    security_table,
    country_table ,
    iv_order_type_table,
    iv_broker_table,
    currency_table,
    DatabaseServerLocation,
    iv_subaccount_table,
    subaccount_table,
    CustodianTypes,
    Consultants,
    country_table c2,
    exchange_table e2,
    fx_rate_history_table fx,
    external_id_snapshot_table ext

    WHERE
    iv_execution_history_table.security_id = security_table.security_id
    and iv_execution_history_table.country_id = country_table.country
    and iv_execution_history_table.order_type = iv_order_type_table.type
    and iv_execution_history_table.broker_code = iv_broker_table.broker
    and iv_execution_history_table.fmc_currency_code = currency_table.currency
    and DatabaseServerLocation.ID = LocationID
    and iv_execution_history_table.subaccount = iv_subaccount_table.subaccount
    and iv_subaccount_table.subaccount = subaccount_table.subaccount
    and subaccount_table.CustodianTypeID *= CustodianTypes.CustodianTypeID
    and trade_date >= @fromTradeDate
    and trade_date <= @toTradeDate
    and iv_execution_history_table.CreditedToConsultant *= ConsultantID
    and iv_execution_history_table.exchange = e2.exchange
    and c2.country = e2.country
    and fx.data_date = iv_execution_history_table.trade_date
    and fx.currency = currency_table.currency
    and security_table.security_id = ext.security_id
    and ext.external_id_type = case when security_table.security_type = 8 then 19 else 2 end

    ORDER BY .....................

    #11Posted by RK | Aug 25, 2006, 04:17 AM
  • Jason

    RK, as you can see below, you'll need to move the JOINs to the FROM clause and then leave only the filters in the WHERE clause. Since there are two different OUTER joins, I moved one to the "front" as a RIGHT join and the other to the "end" as a LEFT join. Also, be sure to specify your table names in every join; there were 2 unspecified joins: one for ConsultantID, which I've changed to Consultants.ConsultantID, and one for joining the DatabaseServerLocation table, but I don't know what the table designation should be for the LocationID table. (I'd guess it's supposed to be iv_execution_history_table.LocationID, but I'm not sure.)

    Hope this helps,
    J


    SELECT ..............

    FROM
    Consultants RIGHT OUTER JOIN
          iv_execution_history_table ON Consultants.ConsultantID = iv_execution_history_table.CreditedToConsultant INNER JOIN
    security_table ON iv_execution_history_table.security_id = security_table.security_id INNER JOIN
    external_id_snapshot_table ext ON security_table.security_id = ext.security_id INNER JOIN
    country_table ON iv_execution_history_table.country_id = country_table.country INNER JOIN
    iv_order_type_table ON iv_execution_history_table.order_type = iv_order_type_table.type INNER JOIN
    iv_broker_table ON iv_execution_history_table.broker_code = iv_broker_table.broker INNER JOIN
    currency_table ON iv_execution_history_table.fmc_currency_code = currency_table.currency INNER JOIN
    DatabaseServerLocation ON DatabaseServerLocation.ID = xxxxxxx.LocationID INNER JOIN
    exchange_table e2 ON iv_execution_history_table.exchange = e2.exchange INNER JOIN
    country_table c2 ON c2.country = e2.country INNER JOIN
    fx_rate_history_table fx ON fx.data_date = iv_execution_history_table.trade_date
              AND fx.currency = currency_table.currency INNER JOIN
    iv_subaccount_table ON iv_execution_history_table.subaccount = iv_subaccount_table.subaccount INNER JOIN
    subaccount_table ON iv_subaccount_table.subaccount = subaccount_table.subaccount LEFT OUTER JOIN
    CustodianTypes ON subaccount_table.CustodianTypeID = CustodianTypes.CustodianTypeID

    WHERE
    trade_date >= @fromTradeDate
    and trade_date <= @toTradeDate
    and ext.external_id_type = case when security_table.security_type = 8 then 19 else 2 end

    #12Posted by Jason | Aug 25, 2006, 08:32 AM
  • sam

    Can you please explain to convert multiple outer joins on 2 tables. E.g. code below. Will appreciate respnse.

    SELECT XREF.*,
    isnull(CP.closing_date,'1900-01-01 12:00:00') closing_date,
    isnull(CP.WITYP,0) WITYP,
    isnull(CP.bidprice,-999.0) bidprice,
    isnull(CP.bidyield,-999.0) bidyield,
    isnull(CP.askprice,-999.0) askprice,
    isnull(CP.askyield,-999.0) askyield
    FROM Security_Id_Xref XREF,
    Closing_Prices CP
    WHERE sec_id = @id
    AND active_indicator = 1
    AND CP.std_sec_id =* XREF.std_sec_id
    AND CP.std_sec_id_type =* XREF.std_sec_id_type
    AND CP.closing_date = @date
    AND CP.eod = '1500'

    #13Posted by sam | Jan 29, 2007, 06:38 PM
  • Jason

    I think what you want is the following (I can't recall whether =* is LEFT OUTER or RIGHT OUTER, but the syntax of your WHERE clause suggests RIGHT OUTER). Note that the JOIN clause can contain its own AND operator, allowing for the 2-column test.

    SELECT XREF.*,
       isnull(CP.closing_date, '1900-01-01 12:00:00') closing_date,
       isnull(CP.WITYP, 0) WITYP,
       isnull(CP.bidprice, -999.0) bidprice,
       isnull(CP.bidyield, -999.0) bidyield,
       isnull(CP.askprice, -999.0) askprice,
       isnull(CP.askyield, -999.0) askyield
    FROM Security_Id_Xref XREF RIGHT OUTER JOIN
       Closing_Prices CP ON XREF.std_sec_id = CP.std_sec_id
          AND XREF.std_sec_id_type = CP.std_sec_id_type
    WHERE sec_id = @id
       AND active_indicator = 1
       AND CP.closing_date = @date
       AND CP.eod = '1500'

    HTH,
    J

    #14Posted by Jason | Jan 30, 2007, 08:32 AM
  • Trevor

    Hi,
    I am having this same issue. I read through all of the responses and understand the basic issue. I know I have to move the two parts of the WHERE clause below that have *= in them into the FROM clause, but I am not sure how to change the FROM clause so that I get the same results as the previous query. Any help you can give would be much appreciated.

    Thanks in advance,
    Trevor

       SELECT    d.documentId,
          d.publicationId,
          d.format,
          d.reportName,
          d.folder,
          d.date,
          d.seqNumber,
          CASE ISNULL(r.name,'NULL')
          WHEN 'NULL' THEN f.priority
          ELSE 0
          END AS real_priority
       INTO #tmpMasterDocs
       FROM    documents d,
          formats f,
          reports r,
          #tmpNewDocz nd
       WHERE d.folder = nd.folder
       AND    d.reportName = nd.reportName
       AND    d.seqNumber = nd.seqNumber
       AND    d.date = nd.date
       AND    d.format = f.format
       AND d.format *= r.defaultFormat
       AND d.reportProfile *= r.Name

    #15Posted by Trevor | Apr 12, 2007, 04:54 PM
  • Jason

    Trevor,

    Since you don't have one-to-one Foreign Keys, you can create compound JOIN statements right in the FROM block. As follows:

    SELECT d.documentId,
    d.publicationId,
    d.format,
    d.reportName,
    d.folder,
    d.date,
    d.seqNumber,
    CASE ISNULL(r.name,'NULL')
    WHEN 'NULL' THEN f.priority
    ELSE 0
    END AS real_priority
    INTO #tmpMasterDocs
    FROM formats f INNER JOIN
    documents d ON f.format = d.format INNER JOIN
    #tmpNewDocz nd ON d.folder = nd.folder
    AND d.reportName = nd.reportName
    AND d.seqNumber = nd.seqNumber
    AND d.date = nd.date LEFT OUTER JOIN
    reports r ON d.format = r.defaultFormat
    AND d.reportProfile = r.Name

    That should work.
    J

    #16Posted by Jason | Apr 14, 2007, 09:05 AM
  • Dhipak

    Hi ,
    I am having the same issue, The sql query contains many left and right outer joins.
    Can you please solve the same so that the result set should match with the orginal result set.
    Thanks.

    Select * -------------
    from
    FROM [order] o, offln_order_detail ood, order_pay op, order_addr_contact oac, prop, addr a, contact c, [user] u, [user] u2, cust_acct_lookup L1, bar_assoc ba, appl_code_type act, appl_code_val acv, appl_code_type act2, appl_code_val acv2, employee_info_vw eiv1, employee_info_vw eiv2, employee_info_vw eiv3
    WHERE o.order_id = 3832
    AND ood.order_id = o.order_id
    AND op.order_id = o.order_id
    AND prop.prop_id = o.prop_id
    AND oac.order_id = o.order_id
    AND oac.addr_contact_type_id = 1
    AND a.order_addr_id = oac.order_addr_id
    AND c.contact_id = oac.contact_id
    AND o.ship_to_acct_nbr = L1.acct_nbr
    AND o.create_by = u.empl_id
    AND o.mod_by = u2.empl_id
    AND ood.bar_assoc_id *= ba.bar_assoc_id
    AND op.cr_card_type *= acv.alt_key_val
    AND acv.appl_code_type_id =* act.appl_code_type_id
    AND act.appl_code_type_name = 'Credit Card Type'
    AND o.create_by *= eiv1.emplid
    AND o.owned_by *= eiv2.emplid
    AND o.mod_by *= eiv3.emplid
    AND op.order_pay_type_id *= acv2.alt_key_val
    AND acv2.appl_code_type_id =* act2.appl_code_type_id
    AND act2.appl_code_type_name = 'Payment Type'

    #17Posted by Dhipak | Apr 16, 2007, 06:07 AM
  • Jason

    Couldn't reach the blog from work earlier, so this is what I sent to Dhipak this morning.

    The strict version of your script would be the following:

    SELECT *

    FROM [order] o INNER JOIN

    offln_order_detail ood ON o.order_id = ood.order_id INNER JOIN

    order_pay op ON o.order_id = op.order_id INNER JOIN

    prop ON o.prop_id = prop.prop_id INNER JOIN

    order_addr_contact oac ON o.order_id = oac.order_id INNER JOIN

    addr a ON oac.order_addr_id = a.order_addr_id INNER JOIN

    contact c ON oac.contact_id = c.contactd_id INNER JOIN

    cust_acct_lookup L1 ON o.shipo_to_acct_nbr = L1.acct_nbr INNER JOIN

    [user] u ON o.create_by = u.empl_id INNER JOIN

    [user] u2 ON o.mod_by = u2.empl_id LEFT OUTER JOIN

    bar_assoc ba ON ood.bar_assoc_id = ba.bar_assoc_id LEFT OUTER JOIN

    appl_code_val acv ON op.cr_card_type = acv.alt_key_val RIGHT OUTER JOIN

    appl_code_type act ON acv.appl_code_type_id = act.appl_code_type_id

    AND act.appl_code_type_name = 'Credit Card Type' LEFT OUTER JOIN

    employee_info_vw eiv1 ON o.create_by = eiv1.emplid LEFT OUTER JOIN

    employee_info_vw eiv2 ON o.owned_by = eiv2.emplid LEFT OUTER JOIN

    employee_info_vw eiv3 ON o.mod_by = eiv3.emplid LEFT OUTER JOIN

    appl_code_val acv2 ON op.order_pay_type_id = acv2.alt_key_val RIGHT OUTER JOIN

    appl_code_type act2 ON acv2.appl_code_type_id = act2.appl_code_type_id

    AND act2.appl_code_type_name = 'Payment Type'

    WHERE o.order_id = 3832
    AND oac.addr_contact_type_id = 1

    But I think that the 2 RIGHT joins (=* in the original) won't cascade correctly, so I recommend trying this instead (make the =* into LEFT instead of RIGHT joins):

    SELECT *

    FROM [order] o INNER JOIN

    offln_order_detail ood ON o.order_id = ood.order_id INNER JOIN

    order_pay op ON o.order_id = op.order_id INNER JOIN

    prop ON o.prop_id = prop.prop_id INNER JOIN

    order_addr_contact oac ON o.order_id = oac.order_id INNER JOIN

    addr a ON oac.order_addr_id = a.order_addr_id INNER JOIN

    contact c ON oac.contact_id = c.contactd_id INNER JOIN

    cust_acct_lookup L1 ON o.shipo_to_acct_nbr = L1.acct_nbr INNER JOIN

    [user] u ON o.create_by = u.empl_id INNER JOIN

    [user] u2 ON o.mod_by = u2.empl_id LEFT OUTER JOIN

    bar_assoc ba ON ood.bar_assoc_id = ba.bar_assoc_id LEFT OUTER JOIN

    appl_code_val acv ON op.cr_card_type = acv.alt_key_val LEFT OUTER JOIN

    appl_code_type act ON acv.appl_code_type_id = act.appl_code_type_id

    AND act.appl_code_type_name = 'Credit Card Type' LEFT OUTER JOIN

    employee_info_vw eiv1 ON o.create_by = eiv1.emplid LEFT OUTER JOIN

    employee_info_vw eiv2 ON o.owned_by = eiv2.emplid LEFT OUTER JOIN

    employee_info_vw eiv3 ON o.mod_by = eiv3.emplid LEFT OUTER JOIN

    appl_code_val acv2 ON op.order_pay_type_id = acv2.alt_key_val LEFT OUTER JOIN

    appl_code_type act2 ON acv2.appl_code_type_id = act2.appl_code_type_id

    AND act2.appl_code_type_name = 'Payment Type'

    WHERE o.order_id = 3832
    AND oac.addr_contact_type_id = 1



    HTH,
    Jason

    #18Posted by Jason | Apr 16, 2007, 06:12 PM
  • Dhipak

    Hi Jason ,
    With the first code snippet, i am not able to fetch a single record also and with the second code snippet, 80 rows are being fetched.But in the original query , only one row is being fetched.
    Can you please look into it?
    Thank you very much for your valuable time.

    #19Posted by Dhipak | Apr 17, 2007, 01:26 AM
  • Jason

    Well, try this ... it's definitely an issue with those RIGHT/LEFT joins, so I've moved the compounds out to the WHERE clause. Maybe it'll help.

    SELECT *
    FROM [order] o INNER JOIN
    offln_order_detail ood ON o.order_id = ood.order_id INNER JOIN
    order_pay op ON o.order_id = op.order_id INNER JOIN
    prop ON o.prop_id = prop.prop_id INNER JOIN
    order_addr_contact oac ON o.order_id = oac.order_id INNER JOIN
    addr a ON oac.order_addr_id = a.order_addr_id INNER JOIN
    contact c ON oac.contact_id = c.contactd_id INNER JOIN
    cust_acct_lookup L1 ON o.shipo_to_acct_nbr = L1.acct_nbr INNER JOIN
    [user] u ON o.create_by = u.empl_id INNER JOIN
    [user] u2 ON o.mod_by = u2.empl_id LEFT OUTER JOIN
    bar_assoc ba ON ood.bar_assoc_id = ba.bar_assoc_id LEFT OUTER JOIN
    appl_code_val acv ON op.cr_card_type = acv.alt_key_val RIGHT OUTER JOIN
    appl_code_type act ON acv.appl_code_type_id = act.appl_code_type_id LEFT OUTER JOIN
    employee_info_vw eiv1 ON o.create_by = eiv1.emplid LEFT OUTER JOIN
    employee_info_vw eiv2 ON o.owned_by = eiv2.emplid LEFT OUTER JOIN
    employee_info_vw eiv3 ON o.mod_by = eiv3.emplid LEFT OUTER JOIN
    appl_code_val acv2 ON op.order_pay_type_id = acv2.alt_key_val RIGHT OUTER JOIN
    appl_code_type act2 ON acv2.appl_code_type_id = act2.appl_code_type_id
    WHERE o.order_id = 3832
    AND oac.addr_contact_type_id = 1
    AND act.appl_code_type_name = 'Credit Card Type'
    AND act2.appl_code_type_name = 'Payment Type'

    HTH,
    Jason

    #20Posted by Jason | Apr 17, 2007, 09:57 PM
  • Dhipak

    Hi Jason ,
    I have modified this query ( removed both the right outer joins) , and i am able to fetch one row correctly.
    Below is the modified query:
    Can you now change it to ge the desired results

    select *
    FROM [order] o, offln_order_detail ood, order_pay op, order_addr_contact oac, prop, addr a, contact c, [user] u, [user] u2, cust_acct_lookup L1, bar_assoc ba, appl_code_type act, appl_code_val acv, appl_code_type act2, appl_code_val acv2, employee_info_vw eiv1, employee_info_vw eiv2, employee_info_vw eiv3
    WHERE
    o.order_id = 3832
    AND ood.order_id = o.order_id
    AND op.order_id = o.order_id
    AND prop.prop_id = o.prop_id
    AND oac.order_id = o.order_id
    AND oac.addr_contact_type_id = 1
    AND a.order_addr_id = oac.order_addr_id
    AND c.contact_id = oac.contact_id
    AND o.ship_to_acct_nbr = L1.acct_nbr
    AND o.create_by = u.empl_id
    AND act2.appl_code_type_id = acv2.appl_code_type_id
    and act.appl_code_type_id = acv.appl_code_type_id
    And o.mod_by = u2.empl_id
    AND ood.bar_assoc_id *= ba.bar_assoc_id
    AND op.cr_card_type *= acv.alt_key_val
    AND act.appl_code_type_name = 'Credit Card Type'
    AND o.create_by *= eiv1.emplid
    AND o.owned_by *= eiv2.emplid
    AND o.mod_by *= eiv3.emplid
    AND op.order_pay_type_id *= acv2.alt_key_val
    AND act2.appl_code_type_name = 'Payment Type'

    Thanks a lot!!!!
    Dhipak

    #21Posted by Dhipak | Apr 18, 2007, 03:50 AM
  • Dhipak

    Hi Jason...
    Atlast i have resolved this query!!!!!!!
    Thanks a lot for your help.
    I tweaked your changed query a bit to get the desired results.
    With this new query , only one correct row is being fetched which is same as the original one.
    Thanks once again

    Regards
    Dhipak

    I am attaching the query .....
    FROM [order] o INNER JOIN offln_order_detail ood ON o.order_id = ood.order_id
    INNER JOIN order_pay op ON o.order_id = op.order_id
    INNER JOIN prop ON o.prop_id = prop.prop_id
    INNER JOIN order_addr_contact oac ON o.order_id = oac.order_id
    AND oac.addr_contact_type_id = 1
    INNER JOIN addr a ON oac.order_addr_id = a.order_addr_id
    INNER JOIN contact c ON oac.contact_id = c.contact_id
    INNER JOIN cust_acct_lookup L1 ON o.ship_to_acct_nbr = L1.acct_nbr
    INNER JOIN [user] u ON o.create_by = u.empl_id
    INNER JOIN [user] u2 ON o.mod_by = u2.empl_id
    LEFT OUTER JOIN bar_assoc ba ON ood.bar_assoc_id = ba.bar_assoc_id
    LEFT OUTER JOIN
    (appl_code_val acv right outer join appl_code_type act
    ON acv.appl_code_type_id = act.appl_code_type_id AND act.appl_code_type_name = 'Credit Card Type' )
    ON op.cr_card_type = acv.alt_key_val

    LEFT OUTER JOIN employee_info_vw eiv1 ON o.create_by = eiv1.emplid
    LEFT OUTER JOIN employee_info_vw eiv2 ON o.owned_by = eiv2.emplid
    LEFT OUTER JOIN employee_info_vw eiv3 ON o.mod_by = eiv3.emplid
    LEFT OUTER JOIN
    ( appl_code_val acv2 right outer join appl_code_type act2
    ON acv2.appl_code_type_id = act2.appl_code_type_id AND act2.appl_code_type_name = 'Payment Type')
    on op.order_pay_type_id = acv2.alt_key_val

    WHERE o.order_id = 3832

    #22Posted by Dhipak | Apr 20, 2007, 02:43 AM
  • Gene

    Hi everyone! Not sure if this was said i was to lazy to read the entire post. But for all of you looking for an *easy* way to upgrade your syntax without doing much work you can highlight your query and right click the selection and select "design query in editor..." This will automatily take those *= or =* and make the appropriate changes for you!!! Have fun!!!

    #23Posted by Gene | Aug 2, 2007, 10:42 PM
  • Jan

    I am trying to convert this query from *= to LEFT OUTER JOIN

    select count(*)
    --a.cmp_int, a.cmp_code, a.cmp_buss,a.cmp_cost, b.acct_int, ltrim(rtrim(b.acct_code)) as acct_code,
    --isnull(ROUND(c.annual_bud, 0),'') as ann, isnull(ROUND(c.cp_bud, 0),'') as cp,c.prd_buc_nbr
    --into #temp_data
    from (select b.cmp_int,b.cmp_code,b.cmp_buss,b.cmp_cost
    --into #temp_cmp
    from tbl_sd_data a,(select a.cmp_int, a.cmp_code, substring(a.cmp_code,1,3)as cmp_buss,substring(a.cmp_code,4,8)as cmp_cost
    --into #temp_cmp1
    from tbl_sd_components a
    where a.cmp_typ = 2 and a.origin_flg = 2 ) b
    where a.cmp_int = b.cmp_int
    and a.year = 2007 AND a.prd_typ = 1
    AND a.prd_buc_nbr = 1
    group by b.cmp_int,b.cmp_code,b.cmp_buss,b.cmp_cost) a , tbl_sd_account b, tbl_sd_data c
    where
    --a.cmp_typ = 2 and a.origin_flg = 2 and
    b.cmp_int in (select cmp_int from tbl_sd_components where cmp_code = substring(a.cmp_code,1,3))
    and b.origin_flg = 2 AND b.inact_flg = 0 AND b.acct_typ = 4
    AND rtrim(b.acct_code) not like '%[_]%' AND len(rtrim(b.acct_code)) <=6 AND b.acct_code not like 'CAT%'
    and a.cmp_int *= c.cmp_int AND b.acct_int *= c.acct_int
    AND c.year = 2007 AND c.prd_typ = 1
    AND c.prd_buc_nbr in (1,2,3,4,5,6,7,8,9,10,11,12)
    --order by a.cmp_code,b.acct_code,c.prd_buc_nbr

    The query which i have changed it to looks like this but takes very long to complete and then gives timeout. The foll is the query:
    select count(*)
    --a.cmp_int, a.cmp_code, a.cmp_buss,a.cmp_cost, b.acct_int, ltrim(rtrim(b.acct_code)) as acct_code,
    --isnull(ROUND(c.annual_bud, 0),'') as ann, isnull(ROUND(c.cp_bud, 0),'') as cp,c.prd_buc_nbr
    --into #temp_data
    from (select b.cmp_int,b.cmp_code,b.cmp_buss,b.cmp_cost
    --into #temp_cmp
    from tbl_sd_data a,(select a.cmp_int, a.cmp_code, substring(a.cmp_code,1,3)as cmp_buss,substring(a.cmp_code,4,8)as cmp_cost
    --into #temp_cmp1
    from tbl_sd_components a
    where a.cmp_typ = 2 and a.origin_flg = 2 ) b
    where a.cmp_int = b.cmp_int
    and a.year = 2007 AND a.prd_typ = 1
    AND a.prd_buc_nbr = 1
    group by b.cmp_int,b.cmp_code,b.cmp_buss,b.cmp_cost) a LEFT OUTER JOIN tbl_sd_data c ON a.cmp_int = c.cmp_int
    AND c.year = 2007 AND c.prd_typ = 1
    AND c.prd_buc_nbr in (1,2,3,4,5,6,7,8,9,10,11,12)
    RIGHT OUTER JOIN tbl_sd_account b ON c.acct_int = b.acct_int
    and b.cmp_int in (select cmp_int from tbl_sd_components where cmp_code = substring(a.cmp_code,1,3))
    and b.origin_flg = 2 AND b.inact_flg = 0 AND b.acct_typ = 4
    AND rtrim(b.acct_code) not like '%[_]%' AND len(rtrim(b.acct_code)) <=6 AND b.acct_code not like 'CAT%'

    Also using where clause in the above query doesnt help since it excludes 300000 rows.

    Plz advise

    Thanks,
    Jan

    #24Posted by Jan | Aug 10, 2007, 03:25 PM
  • David

    To be clear, this is only affecting *= and =* (outer joins) and not * (inner joins)? So this code example still works on SQL 2005?

    select col1, col2
    from t1, t2
    where t1.keyid = t2.keyid

    I sure hope I don't have to re-write that too! Too many instances of that... I'm okay with only re-writing the occasional outer joins ( *= and =* )

    #25Posted by David | Nov 1, 2007, 11:55 AM
  • Mike

    Hello there! I am new to the "join" world. This could be simple, but needed help in finding out how the following query will be converted to be SQL 2005 compatible.

    SELECT
    ...
    INTO #VTable
    FROM
    dbo.Truck TRE WITH (NOLOCK)
    ,dbo.Veh VEH
    ,dbo.Carrier CAR
    ,dbo.Device DEV
    WHERE
    TRE.ReportFK = @ReportPK
    AND VEH.Veh = TRE.Veh
    AND CAR.CarPK = VEH.CarFK
    AND VEH.VehPK *= DEV.VehFK

    #26Posted by Mike | Nov 12, 2007, 01:06 PM
  • Jason

    @Mike,

    Assuming I'm understanding your layout and conventions, try this:

    SELECT ...
    INTO #VTable
    FROM Carrier CAR INNER JOIN
    Vehicle VEH ON CAR.CarPK = VEH.CarFK INNER JOIN
    Truck TRE ON VEH.VehPK = TRE.VehFK LEFT OUTER JOIN
    Device DEV ON VEH.VehPK = DEV.VehFK
    WHERE TRE.ReportFK = @ReportPK

    @David,

    Yes, this only affects the 'old' style of OUTER JOIN's (both LEFT and RIGHtT), but does not change INNER JOIN's, afaik.

    -J-

    #27Posted by Jason | Nov 12, 2007, 05:22 PM
  • john

    Hello,

    I have a SQL call that was fine until I moved it to production with a SQL Server 2005 database. I think it has something to do with the joins. When the code runs it says returns "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

    My code looks like

    SELECT *many fields here*

    FROM Details LEFT OUTER JOIN Personnel AS Poriginator ON Details.PID = Poriginator.ID LEFT OUTER JOIN ReviewHistory ON Details.ID = ReviewHistory.DetailsID AND RIGHT(Details.CurrentStage, 2) = RIGHT(ReviewHistory.Stage, 2) LEFT OUTER JOIN Personnel AS Previewer ON ReviewHistory.PID = Previewer.ID WHERE (Details.ID = 1945) ORDER BY reviewer_SID

    When I bring the sql into query editor it runs fine. I moved the code into a standalone asp routine and I still get the error. Any suggestions???

    #28Posted by john | Dec 6, 2007, 11:42 AM
  • Jason

    john,

    Are you using CF for this? It looks like that's an ADO recordset error, which I've never seen in a CF context. It indicates that a recordset is empty but there is script still trying to use it. Could also happen within a stored proc, I guess, if the recordset was assumed to be populated and then used in a cursor loop or something. If this is in an ASP or ASP.NET script, then you'll have to test for empty recordsets instead of trying to show them directly (if (rs.BOF or rs.EOF) then /* don't show */ else ...).

    HTH

    #29Posted by Jason | Dec 6, 2007, 12:52 PM
  • John

    Thanks for the reply Jason. It's actually classic ASP. The code was tested using SQL Server 2000 but the production server got changed to one featuring SS 2005. For some reason, this query (and another one involving Null values in the join field) is failing even though it worked in SS2000. My DBA checked it out and feels that since the query works in Query Analyzer, it must have something to do with the ASP code. We also compared the settings in SQL Server (like compatibililty 80 and how Nulls are handled) and they match SQL Server 2000 settings. It's very puzzling and frustrating.

    Do you have any other suggestions?

    #30Posted by John | Dec 10, 2007, 01:46 AM
  • john

    Ive also now been able to run the SQL Query with ASP code on a different server with SQL Server 2005. Both environments are also using the same release/version of ASP.

    #31Posted by john | Dec 10, 2007, 01:13 PM
  • Jason

    Hmmmmm ... at that point, it begins to be clear that there has to be a difference between server environments. Unless I completely miss the mark, the culprit almost has to be the ODBC drivers. Verify that the 2 servers hitting SQL 2005 (the one that works and the one that doesn't) are running the same MDAC versions. It almost sounds like one is handling the NULLs correctly and the other isn't ... or some such difference. Luckily for me, CF Server has long been bundling its own JDBC connectors, so I know that two installtions of the same CF version will process SQL Server calls the same way. ASP, however, is relying on the ODBC drivers separately installed at the OS level, IIRC.

    Good luck!

    #32Posted by Jason | Dec 10, 2007, 01:28 PM
  • Michael Muller

    Hey all,

    I'm having trouble with an outer join query using SQL Server 2005.

    SELECT sum(a.clicks) as clicks, l.linksid, h.location, s.subcategory
    FROM aggregatelinkcounts as a
    LEFT OUTER JOIN links as l ON a.linkID = l.linksID
    LEFT OUTER JOIN subcategories as s ON s.id = l.subcategoryid
    LEFT OUTER JOIN locations as h ON h.locationsid = l.locationsid
    WHERE l.customersid = 44
    AND a.date >= '2007/1/1'
    AND a.date <= '2008/1/1'
    GROUP BY l.linksID, s.subcategory, h.location
    ORDER BY s.subcategory, h.location


    The table Links has more occurrences of linkID than aggregatelinkcounts and so 'clicks' should have zeros showing up on those result rows with no inner join.

    From some Googling I've just done, it may be that SQL 2005 doesn't do this correctly. Is there a tweak or something I can do to make this query work correctly, or do I have the incorrect table in the FROM clause?

    #33Posted by Michael Muller | Jul 21, 2008, 02:36 PM
  • jfish

    @Michael,

    Two things: 1) the aggregateLinkCounts table was your primary table, since it was the far left of your LEFT JOINs, and 2) the WHERE clause insisted on data in that aggregateLinkCounts table. The net result is that the only rows which could be returned were those where a match could be found in aggregateLinkCounts.

    The example below makes links your primary table (by using a RIGHT JOIN back to the aggregateLinkCounts table) and it allows returns with no match in aggregateLinkCounts by allowing for the NULL:

    SELECT sum(a.clicks) as clicks,
       l.linksid,
       h.location,
       s.subcategory
    FROM aggregatelinkcounts as a RIGHT OUTER JOIN
       links as l ON a.linkID = l.linksID LEFT OUTER JOIN
       subcategories as s ON s.id = l.subcategoryid LEFT OUTER JOIN
       locations as h ON h.locationsid = l.locationsid
    WHERE l.customersid = 44
       AND (
          a.date IS NULL
          OR a.date BETWEEN '2007/1/1' AND '2008/1/1'
       )
    GROUP BY l.linksID, s.subcategory, h.location
    ORDER BY s.subcategory, h.location


    HTH

    #34Posted by jfish | Jul 21, 2008, 03:30 PM
  • Michael Muller

    @HTH

    Rock on. I had given up and pulled the aggregate table from the query, sticking it into a new smaller count() query in the cfoutput. Cheezy, but got the job done instead of being frustrated.

    I'll try this and start getting smarter with my outer join queries. Better to offload as much to SQL as possible.

    Thanks again!

    #35Posted by Michael Muller | Jul 21, 2008, 04:47 PM
  • kim M

    I am struggling to convert this SQL for 2005 for the *= (left outer join). Any help would be appreciated. I have taken over this system and this was written by someone that left the compnay

    * Final Selection for Portfolio Trial Balance Report

    */
    select
    Loanhist.LOAN,
    Loanhist.LOAN_NAME,
    Loanhist.NINST_PAY_DATE,
    Comphist.PARTICIPANT,
    PRINCIPAL_BAL = Comphist.PRIN_BAL_P,
    #Tmppblp2.SUSGENRL_BAL,
    LATE_CHG_BAL = Comphist.LATE_CHG_BAL_P,
    ESCROW_BAL = Comphist.ESC_TAX_BAL_P + Comphist.ESC_INS_BAL_P,
    #Tmppblp1.ADDTL_ESC_BAL,
    YTD_INTEREST = Comphist.INT_COLL_P,
    ACCOUNTING_DATE = @Acct_Date,
    INT_RATE = CASE WHEN #Tmppblp3.INSTALL_TYPE = 'MULTIRATE' THEN 'MULTIRATE'
    ELSE convert (char(15),#Tmppblp3.INT_RATE)
    END,
    Comphist.PART_INC_COLL_P,
    Comphist.DEF_CAP_P,
    DEF_INT = Comphist.DEF_INT_P - COALESCE(#Tmppblp4.DEF_INT_P,0)

    from Loanhist,
    Comphist,
    #Tmppblp1,
    #Tmppblp2,
    #Tmppblp3,
    #Tmppblp4
    where Loanhist.LOAN = Comphist.LOAN and
    Loanhist.LOAN *= #Tmppblp1.LOAN and
    Loanhist.LOAN *= #Tmppblp2.LOAN and
    Loanhist.LOAN *= #Tmppblp3.LOAN and
    Loanhist.LOAN *= #Tmppblp4.LOAN and
    Comphist.PARTICIPANT *= #Tmppblp2.LOAN_KIND and
    Comphist.PARTICIPANT *= #Tmppblp1.PARTICIPANT and
    Comphist.PARTICIPANT *= #Tmppblp4.PARTICIPANT and
    Loanhist.ACCOUNTING_DATE = Comphist.ACCOUNTING_DATE and
    Loanhist.ACCOUNTING_DATE = @Acct_Date

    #36Posted by kim M | Sep 24, 2008, 03:42 PM
  • Jason Fisher

    Hi Kim,

    I think you want it to look like the SQL below.

    -jfish

    SELECT Loanhist.LOAN,
       Loanhist.LOAN_NAME,
       Loanhist.NINST_PAY_DATE,
       Comphist.PARTICIPANT,
       Comphist.PRIN_BAL_P AS PRINCIPAL_BAL,
       #Tmppblp2.SUSGENRL_BAL,
       Comphist.LATE_CHG_BAL_P AS LATE_CHG_BAL,
       Comphist.ESC_TAX_BAL_P + Comphist.ESC_INS_BAL_P AS ESCROW_BAL,
       #Tmppblp1.ADDTL_ESC_BAL,
       Comphist.INT_COLL_P AS YTD_INTEREST,
       @Acct_Date AS ACCOUNTING_DATE,
       CASE WHEN #Tmppblp3.INSTALL_TYPE = 'MULTIRATE' THEN 'MULTIRATE' ELSE convert (char(15),#Tmppblp3.INT_RATE) END AS INT_RATE,
       Comphist.PART_INC_COLL_P,
       Comphist.DEF_CAP_P,
       Comphist.DEF_INT_P - COALESCE(#Tmppblp4.DEF_INT_P,0) AS DEF_INT
    FROM Loanhist INNER JOIN
       Comphist ON Loanhist.LOAN = Comphist.LOAN
          AND Loanhist.ACCOUNTING_DATE = Comphist.ACCOUNTING_DATE LEFT OUTER JOIN
       #Tmppblp1 ON Loanhist.LOAN = #Tmppblp1.LOAN
          AND Comphist.PARTICIPANT = #Tmppblp1.PARTICIPANT LEFT OUTER JOIN
       #Tmppblp2 ON Loanhist.LOAN = #Tmppblp2.LOAN
          AND Comphist.PARTICIPANT = #Tmppblp2.LOAN_KIND LEFT OUTER JOIN
       #Tmppblp3 ON Loanhist.LOAN = #Tmppblp3.LOAN LEFT OUTER JOIN
       #Tmppblp4 ON Loanhist.LOAN = #Tmppblp4.LOAN
          AND Comphist.PARTICIPANT = #Tmppblp4.PARTICIPANT
    WHERE Loanhist.ACCOUNTING_DATE = @Acct_Date

    #37Posted by Jason Fisher | Sep 24, 2008, 04:12 PM
  • Toddzilla

    I just wanted to thank Jason for giving all those examples of converting T-SQL style outer joins to ANSI style. That was heroic. I know this is an ancient thread, but I searched all over the internet for examples to follow and couldn't find any as good as this. Thanks!

    #38Posted by Toddzilla | Aug 20, 2010, 04:31 PM