SQL Server 2005 Outer Join Gotcha

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

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.

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

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

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

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

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

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

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

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

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

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 .....................

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'

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

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

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

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

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'

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.

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

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

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

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!!!

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

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 =* )

@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

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

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

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???

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!

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.

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?

@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

@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 coul

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?

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

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

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!

Estou eu aqui em 2023 atualizando um sql de 2005 para 2019 e me deparei com a mesma coisa, ainda bem que este link esta online salvou muito