The query uses non-ANSI outer join operators ("*="

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

The query uses non-ANSI outer join operators ("*="

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-04 : 21:22:09

Msg 4147, Level 15, State 1, Line 17
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.

declare @StoreID  varchar(50),
        @SortBy varchar(50),
        @OrderBy varchar(50),
        @BeginPeriod varchar(50),
        @SpecificPeriod varchar(50),
        @DeptID varchar(50),
		@Total varchar(50)
declare @qry varchar(1000)
declare @primSort varchar(255)
declare @ascDesc varchar(50)
declare @groupBy varchar(255)
declare @filterBy varchar(255)
declare @period varchar(10)
drop table #TmpRptData
create table #TmpRptData(
        DeptNum                 varchar(50)     null,
        DeptName                varchar(50)     null,
        NetAmt                  money           null,
        TranCount               int             null,
        SaleAmt                 money           null,
        SaleCount               int             null,
        ReturnAmt               money           null,
        ReturnCount             int             null,
        VoidAmt                 money           null,
        VoidCount               int             null
)
set @StoreID=''
set @DeptID='CAMERAS'
set @period='Last Week'
select @groupBy = ' group by A.MDSE_STRC_NBR, A.DESCR '
select @filterBy = ' '

if datalength(ltrim(rtrim(@StoreID))) > 0
        select @filterBy = ' and B.STR_ID = ' + @StoreID

select @filterBy = @filterBy + ' and B.PRD_AGGR_ID = ' + @period
select @filterBy = @filterBy + ' and B.RGST_MDE = 0 '
if upper(@DeptID) <> 'ALL'
        select @filterBy = @filterBy + ' and A.MDSE_STRC_NBR = ''' + @DeptID + ''''

select @qry = 'insert #TmpRptData select
        DeptNum =       A.MDSE_STRC_NBR,
        DeptName =       A.DESCR,
        NetAmt =        sum(SLS_ACCM_AMT + RTN_ACCM_AMT + MD_ACCM_AMT + EMP_DISC_ACCM_AMT + OTHR_MD_ACCM_AMT),
        TranCount =     isnull(sum(TRAN_CNT), 0),
        SaleAmt =       sum(SLS_ACCM_AMT),
        SaleCount =     isnull(sum(SLS_UNT_TOT), 0),
        ReturnAmt =     isnull(sum(RTN_ACCM_AMT), 0),
        ReturnCount =   isnull(sum(RTN_UNT_TOT), 0),
        VoidAmt =       isnull(sum(VOID_ACCM_AMT), 0),
        VoidCount =     isnull(sum(VOID_TRAN_CNT), 0)

from  MERCHANDISE A,MERCH_SLS_CUR B, MDSE_STRUCT C

where
       	C.MDSE_XREF_NO 		*= B.MDSE_XREF_NO
       	and A.MDSE_STRC_LVL 	= 2 
	AND  A.MDSE_XREF_NO	= C.PARNT_MDSE_XREF_NO
	'  + @filterBy + @groupBy
exec (@qry)
select * from #TmpRptData

I found when executing at "exec(@rqy)" from the top to bottom.
The error occuried. I am using SQL Server 2005.

Thanks for your help.

Jason100
Starting Member

34 Posts

Posted - 2008-11-04 : 22:46:00

SQL Server 2005 not allow use *=

the left join ...on can match your request

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-04 : 23:26:24

You should always use the ANSI join format.

Example:


from
	MERCHANDISE A
	join
	MDSE_STRUCT C
	on A.MDSE_XREF_NO = C.PARNT_MDSE_XREF_NO
	left join
	MERCH_SLS_CUR B
	on C.MDSE_XREF_NO = B.MDSE_XREF_NO
where
       	A.MDSE_STRC_LVL 	= 2

CODO ERGO SUM

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-05 : 09:34:34

OK!
Can you help me to look at the quotes?
Wrong somewhere but unable to locate it.

select @filterBy = @filterBy + ' and B.PRD_AGGR_ID = ' + @period
select @filterBy = @filterBy + ' and B.RGST_MDE = 0 '

if upper(@DeptID) <> 'ALL'
        select @filterBy = @filterBy + ' and A.MDSE_STRC_NBR = ''' + @DeptID + ''''

select @qry = 'insert #TmpRptData select
        DeptNum =       A.MDSE_STRC_NBR,
        DeptName =       A.DESCR,
        NetAmt =        sum(SLS_ACCM_AMT + RTN_ACCM_AMT + MD_ACCM_AMT + EMP_DISC_ACCM_AMT + OTHR_MD_ACCM_AMT),
        TranCount =     isnull(sum(TRAN_CNT), 0),
        SaleAmt =       sum(SLS_ACCM_AMT),
        SaleCount =     isnull(sum(SLS_UNT_TOT), 0),
        ReturnAmt =     isnull(sum(RTN_ACCM_AMT), 0),
        ReturnCount =   isnull(sum(RTN_UNT_TOT), 0),
        VoidAmt =       isnull(sum(VOID_ACCM_AMT), 0),
        VoidCount =     isnull(sum(VOID_TRAN_CNT), 0)
from
	MERCHANDISE A
	join
	MDSE_STRUCT C
	on A.MDSE_XREF_NO = C.PARNT_MDSE_XREF_NO'''+ @filterBy+@groupBy+'''
	left join
	MERCH_SLS_CUR B
	on C.MDSE_XREF_NO = B.MDSE_XREF_NO
where
       	A.MDSE_STRC_LVL 	= 2
'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 10:56:36

Why dynamic SQL at all?

insert		#TmpRptData
select		A.MDSE_STRC_NBR AS DeptNum,
		A.DESCR AS DeptName,
		sum(isnull(SLS_ACCM_AMT, 0) + isnull(RTN_ACCM_AMT, 0) + isnull(MD_ACCM_AMT, 0) + isnull(EMP_DISC_ACCM_AMT, 0) + isnull(OTHR_MD_ACCM_AMT, 0)) AS NetAmt,
		sum(isnull(TRAN_CNT, 0)) AS TranCount,
		sum(SLS_ACCM_AMT) AS SaleAmt,
		sum(isnull(SLS_UNT_TOT, 0)) AS SaleCount,
		sum(isnull(RTN_ACCM_AMT, 0)) AS ReturnAmt,
		sum(isnull(RTN_UNT_TOT, 0)) AS ReturnCount,
		sum(isnull(VOID_ACCM_AMT, 0)) AS VoidAmt,
		sum(isnull(VOID_TRAN_CNT, 0)) AS VoidCount
from		MERCHANDISE AS A
inner join	MDSE_STRUCT AS C on C.PARNT_MDSE_XREF_NO = A.MDSE_XREF_NO
left join	MERCH_SLS_CUR AS B ON B.MDSE_XREF_NO = C.MDSE_XREF_NO
			AND B.PRD_AGGR_ID = @period
			and B.RGST_MDE = 0 
where		A.MDSE_STRC_LVL = 2
		AND (@DeptID = 'ALL' OR A.MDSE_STRC_NBR = @DeptID

E 12°55'05.63"
N 56°04'39.26"

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-05 : 11:20:07

Incorrect syntax near the keyword 'where'.

 insert		#TmpRptData
select		A.MDSE_STRC_NBR AS DeptNum,
		A.DESCR AS DeptName,
		sum(isnull(SLS_ACCM_AMT, 0) + isnull(RTN_ACCM_AMT, 0) + isnull(MD_ACCM_AMT, 0) + isnull(EMP_DISC_ACCM_AMT, 0) + isnull(OTHR_MD_ACCM_AMT, 0)) AS NetAmt,
		sum(isnull(TRAN_CNT, 0)) AS TranCount,
		sum(SLS_ACCM_AMT) AS SaleAmt,
		sum(isnull(SLS_UNT_TOT, 0)) AS SaleCount,
		sum(isnull(RTN_ACCM_AMT, 0)) AS ReturnAmt,
		sum(isnull(RTN_UNT_TOT, 0)) AS ReturnCount,
		sum(isnull(VOID_ACCM_AMT, 0)) AS VoidAmt,
		sum(isnull(VOID_TRAN_CNT, 0)) AS VoidCount
from		MERCHANDISE AS A
inner join	MDSE_STRUCT AS C on C.PARNT_MDSE_XREF_NO = A.MDSE_XREF_NO
left join	MERCH_SLS_CUR AS B ON B.MDSE_XREF_NO = C.MDSE_XREF_NO
			AND B.PRD_AGGR_ID = @period
			and B.RGST_MDE = 0
 group by A.MDSE_STRC_NBR, A.DESCR
where		A.MDSE_STRC_LVL = 2
		AND @DeptID = 'ALL' OR A.MDSE_STRC_NBR = @DeptID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 11:22:41

quote:

Originally posted by zhshqzyc

Incorrect syntax near the keyword 'where'.

 insert		#TmpRptData
select		A.MDSE_STRC_NBR AS DeptNum,
		A.DESCR AS DeptName,
		sum(isnull(SLS_ACCM_AMT, 0) + isnull(RTN_ACCM_AMT, 0) + isnull(MD_ACCM_AMT, 0) + isnull(EMP_DISC_ACCM_AMT, 0) + isnull(OTHR_MD_ACCM_AMT, 0)) AS NetAmt,
		sum(isnull(TRAN_CNT, 0)) AS TranCount,
		sum(SLS_ACCM_AMT) AS SaleAmt,
		sum(isnull(SLS_UNT_TOT, 0)) AS SaleCount,
		sum(isnull(RTN_ACCM_AMT, 0)) AS ReturnAmt,
		sum(isnull(RTN_UNT_TOT, 0)) AS ReturnCount,
		sum(isnull(VOID_ACCM_AMT, 0)) AS VoidAmt,
		sum(isnull(VOID_TRAN_CNT, 0)) AS VoidCount
from		MERCHANDISE AS A
inner join	MDSE_STRUCT AS C on C.PARNT_MDSE_XREF_NO = A.MDSE_XREF_NO
left join	MERCH_SLS_CUR AS B ON B.MDSE_XREF_NO = C.MDSE_XREF_NO
			AND B.PRD_AGGR_ID = @period
			and B.RGST_MDE = 0
 group by A.MDSE_STRC_NBR, A.DESCR
where		A.MDSE_STRC_LVL = 2
		AND @DeptID = 'ALL' OR A.MDSE_STRC_NBR = @DeptID
group by A.MDSE_STRC_NBR, A.DESCR

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 11:23:07

GROUP BY is written below WHERE.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources