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.
SQL Server 2005 Outer Join Gotcha
39 responses to “SQL Server 2005 Outer Join Gotcha”
-
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 -
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. -
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 -
How do you work around this problem if you are using joined tables within the same query?
-
Amy, you can do outer self joins using LEFT OUTER and LEFT INNER suntax and table aliases.
— Ben -
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 -
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 -
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 -
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 ………………… -
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 -
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 -
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’ -
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 -
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 ,
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. -
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 =* ) -
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 -
@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,
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??? -
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 -
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? -
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.
-
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! -
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? -
@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 -
@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 -
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 -
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 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
Leave a Reply