issue with deprecayted sql

[niall5098]

HI All,

I am trying to run the following code:

IF exists (SELECT TOP 1 tstrv.tstrv_refno tstrv_refno,
tstrv.tstrd_refno tstrd_refno,
tstrv.code tstrv_code,
tstrv.name tstrv_name,
tstrv.parnt_refno tstrv_parnt_refno,
tstrv.proca_refno tstrv_proca_refno,
tstrs.authorised_flag tstrs_authorised_flag,
tstrv.rutyp_refno tstrv_rutyp_refno,
tstrv.result_dttm tstrv_result_dttm,
tstrv.numeric_result_value tstrv_numeric_result_value,
tstrv.textual_result_value tstrv_textual_result_value,
tstrv.lo_value tstrv_lo_value,
tstrv.hi_value tstrv_hi_value,
tstrv.ref_range tstrv_ref_range,
tstrv.ranal_refno tstrv_ranal_refno,
tstrv.sort_group sort_group,
tstrv.sort_order sort_order,
tstrs.tstrs_refno tstrs_refno,
tstrs.sample_taken_dttm tstrs_sample_taken_dttm,
tstrs.rssts_refno tstrs_rssts_refno,
tgreq.perform_dttm tgreq_perform_dttm,
tgreq.reason tgreq_reason,
patnt.pasid patnt_pasid,
patnt.private_identifier patnt_private_identifier,
tstdf.name tstdf_name,
tstdf.samty_refno tstdf_samty_refno,
tfreq.form_req_number tfreq_form_req_number,
tfreq.spont_refno tfreq_spont_refno,
tfreq.samty_refno tfreq_samty_refno,
tfreq.lab_code tfreq_lab_code,
tfreq.colby_Refno tfreq_colby_Refno,
tfreq.perform_dttm tfreq_perform_dttm,
tfreq.samdttm_dttm tfreq_samdttm_dttm,
tereq.test_req_number tereq_test_req_number,
tereq.create_dttm tereq_create_dttm,
ISNULL(tstrg.tstrg_refno,0) tstrg_tstrg_refno
FROM test_result_values tstrv with(nolock),
test_result_sample_values tstrs with(nolock),
test_group_requests tgreq with(nolock),
test_definitions tstdf with(nolock),
test_form_requests tfreq with(nolock),
test_requests tereq with(nolock),
patients patnt with(nolock),
test_result_ranges tstrg with(nolock)
WHERE tstrv.tstrs_refno = tstrs.tstrs_refno
AND tgreq.tgreq_refno = tstrs.tgreq_refno
AND tfreq.tfreq_refno = tstrs.tfreq_refno
AND tereq.tereq_refno = tstrs.tereq_refno
AND tstdf.tstdf_refno = tstrs.tstdf_refno
AND patnt.patnt_refno = tstrs.patnt_refno
AND tstrv.tstrd_refno *= tstrg.tstrd_refno
AND tstrv.tstrs_refno = 4290098
AND ISNULL(tstrv.archv_flag,'N') = 'N'
AND ISNULL(tstrg.archv_flag,'N') = 'N'
ORDER BY tstrv.sort_group,
tstrv.sort_order,
UPPER(tstrv.name))

BEGIN EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA',
@recipients = 'test@test.ie',
@subject = 'CREATE alert',
@query = N'SELECT TOP 1 tstrv.tstrv_refno tstrv_refno,
tstrv.tstrd_refno tstrd_refno,
tstrv.code tstrv_code,
tstrv.name tstrv_name,
tstrv.parnt_refno tstrv_parnt_refno,
tstrv.proca_refno tstrv_proca_refno,
tstrs.authorised_flag tstrs_authorised_flag,
tstrv.rutyp_refno tstrv_rutyp_refno,
tstrv.result_dttm tstrv_result_dttm,
tstrv.numeric_result_value tstrv_numeric_result_value,
tstrv.textual_result_value tstrv_textual_result_value,
tstrv.lo_value tstrv_lo_value,
tstrv.hi_value tstrv_hi_value,
tstrv.ref_range tstrv_ref_range,
tstrv.ranal_refno tstrv_ranal_refno,
tstrv.sort_group sort_group,
tstrv.sort_order sort_order,
tstrs.tstrs_refno tstrs_refno,
tstrs.sample_taken_dttm tstrs_sample_taken_dttm,
tstrs.rssts_refno tstrs_rssts_refno,
tgreq.perform_dttm tgreq_perform_dttm,
tgreq.reason tgreq_reason,
patnt.pasid patnt_pasid,
patnt.private_identifier patnt_private_identifier,
tstdf.name tstdf_name,
tstdf.samty_refno tstdf_samty_refno,
tfreq.form_req_number tfreq_form_req_number,
tfreq.spont_refno tfreq_spont_refno,
tfreq.samty_refno tfreq_samty_refno,
tfreq.lab_code tfreq_lab_code,
tfreq.colby_Refno tfreq_colby_Refno,
tfreq.perform_dttm tfreq_perform_dttm,
tfreq.samdttm_dttm tfreq_samdttm_dttm,
tereq.test_req_number tereq_test_req_number,
tereq.create_dttm tereq_create_dttm,
ISNULL(tstrg.tstrg_refno,0) tstrg_tstrg_refno
FROM test_result_values tstrv with(nolock),
test_result_sample_values tstrs with(nolock),
test_group_requests tgreq with(nolock),
test_definitions tstdf with(nolock),
test_form_requests tfreq with(nolock),
test_requests tereq with(nolock),
patients patnt with(nolock),
test_result_ranges tstrg with(nolock)
WHERE tstrv.tstrs_refno = tstrs.tstrs_refno
AND tgreq.tgreq_refno = tstrs.tgreq_refno
AND tfreq.tfreq_refno = tstrs.tfreq_refno
AND tereq.tereq_refno = tstrs.tereq_refno
AND tstdf.tstdf_refno = tstrs.tstdf_refno
AND patnt.patnt_refno = tstrs.patnt_refno
AND tstrv.tstrd_refno *= tstrg.tstrd_refno
AND tstrv.tstrs_refno = 4290098
AND ISNULL(tstrv.archv_flag,''N'') = ''N''
AND ISNULL(tstrg.archv_flag,''N'') = ''N''
ORDER BY tstrv.sort_group,
tstrv.sort_order,
UPPER(tstrv.name)',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Memory Values.txt'

end


However I get the following error:

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

the bad line appears to be the *=

My issue is I am not sure where to place the left join, has anyone had this issue and resolved it before?

Thanks,

Niall

 

[gmmastros]

You're right in that *= indicates a left join. What you should do is rewrite the query using a combination of inner joins and left joins.

Since you are having this problem, it's likely that this problem is occurring in other queries. Any query that uses the old style of left and/or right joins will have this problem.

Try this query:

	SELECT	TOP 1 
			tstrv.tstrv_refno tstrv_refno, 
			tstrv.tstrd_refno tstrd_refno, 
			tstrv.code tstrv_code, 
			tstrv.name tstrv_name, 
			tstrv.parnt_refno tstrv_parnt_refno, 
			tstrv.proca_refno tstrv_proca_refno, 
			tstrs.authorised_flag tstrs_authorised_flag, 
			tstrv.rutyp_refno tstrv_rutyp_refno, 
			tstrv.result_dttm tstrv_result_dttm, 
			tstrv.numeric_result_value tstrv_numeric_result_value, 
			tstrv.textual_result_value tstrv_textual_result_value, 
			tstrv.lo_value tstrv_lo_value, 
			tstrv.hi_value tstrv_hi_value, 
			tstrv.ref_range tstrv_ref_range, 
			tstrv.ranal_refno tstrv_ranal_refno, 
			tstrv.sort_group sort_group, 
			tstrv.sort_order sort_order, 
			tstrs.tstrs_refno tstrs_refno, 
			tstrs.sample_taken_dttm tstrs_sample_taken_dttm, 
			tstrs.rssts_refno tstrs_rssts_refno, 
			tgreq.perform_dttm tgreq_perform_dttm, 
			tgreq.reason tgreq_reason, 
			patnt.pasid patnt_pasid, 
			patnt.private_identifier patnt_private_identifier, 
			tstdf.name tstdf_name, 
			tstdf.samty_refno tstdf_samty_refno, 
			tfreq.form_req_number tfreq_form_req_number, 
			tfreq.spont_refno tfreq_spont_refno, 
			tfreq.samty_refno tfreq_samty_refno, 
			tfreq.lab_code tfreq_lab_code, 
			tfreq.colby_Refno tfreq_colby_Refno, 
			tfreq.perform_dttm tfreq_perform_dttm, 
			tfreq.samdttm_dttm tfreq_samdttm_dttm, 
			tereq.test_req_number tereq_test_req_number, 
			tereq.create_dttm tereq_create_dttm, 
			ISNULL(tstrg.tstrg_refno,0) tstrg_tstrg_refno 
	FROM	test_result_values tstrv with(nolock)
			Inner Join test_result_sample_values tstrs with(nolock)
				On tstrv.tstrs_refno = tstrs.tstrs_refno
			Inner Join test_group_requests tgreq with(nolock)
				On tgreq.tgreq_refno = tstrs.tgreq_refno
			Inner Join test_definitions tstdf with(nolock)
				On tstdf.tstdf_refno = tstrs.tstdf_refno
			Inner Join test_form_requests tfreq with(nolock)
				On tfreq.tfreq_refno = tstrs.tfreq_refno
			Inner Join test_requests tereq with(nolock)
				On tereq.tereq_refno = tstrs.tereq_refno
			Inner Join patients patnt with(nolock)
				On patnt.patnt_refno = tstrs.patnt_refno 
			Left Join test_result_ranges tstrg with(nolock) 
				On tstrv.tstrd_refno = tstrg.tstrd_refno 
	WHERE	tstrv.tstrs_refno = 4290098 
			AND ISNULL(tstrv.archv_flag,'N') = 'N' 
			AND ISNULL(tstrg.archv_flag,'N') = 'N' 
	ORDER BY tstrv.sort_group, 
			tstrv.sort_order, 
			UPPER(tstrv.name)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom