Incorrect syntax near '*=' SQL Server-CodePudding

While moving from SQL Server 2016 to 2019 on DbaaS, I am getting this error:

Msg 102, Level 15, State 1, Server p2fm1sql414\SQL01, Procedure usp_sales_record_1020, Line 75
Incorrect syntax near '*='

See query:

  select @sql_1 = "
  --BEGIN TRANSACTION
     insert into SalesFactor.dbo.tbl_cmfsales
     select a.asof_yyyymm ,
            a.bllg_doc_id ,
            a.bllg_doc_ref_id ,
            a.bllg_yyyymm ,
            a.cfm_opr_cd  ,
            a.cfm_opr_dsc ,
            a.dlv_doc_id    ,
            a.dstrb_chnl_cd ,
            a.dstrb_chnl_dsc ,
            a.indus_ky_cd ,
            a.indus_ky_dsc ,
            mtd_bllg_amt    = (1 - abs(sign(a.bllg_yyyymm - a.asof_yyyymm))) * a.mtd_bllg_amt,
            mtd_bllg_qty    = (1 - abs(sign(a.bllg_yyyymm - a.asof_yyyymm))) * a.mtd_bllg_qty,
            a.ord_rsn_cd  ,
            a.pay_cst_id ,
            a.plt_cd  ,
            a.sld_cst_id ,
            a.shp_cst_id ,
            a.sll_co_cd ,
            a.sls_org_cd ,
            a.sls_ord_doc_ty_cd ,
            a.sls_ord_doc_ty_dsc ,
            a.sls_ord_id ,
            a.sls_org_dsc,
            a.shp_cst_ctry_dsc,
              returns = case when a.sls_ord_doc_ty_cd not in('or','zdpa') then ((1 - abs(sign(a.bllg_yyyymm - a.asof_yyyymm))) * a.mtd_bllg_amt)
                             else 0 end,
            a.shp_cst_ctry_cd ,
            b.cst_nm ,
            b.addr_2_nm ,
            b.addr_3_nm ,
            b.addr_4_nm ,
            b.addr_cty ,
            rgn_cd = isnull(b.rgn_cd,' '),
            b.post_cd ,
            b.cst_srch_nm ,
            b.cst_acct_grp_cd ,
            b.cst_acct_grp_dsc ,
            UsAmt =    ((1 - abs(sign(a.bllg_yyyymm - a.asof_yyyymm))) * a.mtd_bllg_amt) * charindex('US',a.shp_cst_ctry_cd),
            OtherAmt = ((1 - abs(sign(a.bllg_yyyymm - a.asof_yyyymm))) * a.mtd_bllg_amt) * (1-abs(sign((charindex('US',a.shp_cst_ctry_cd)))-0)),
            RnaDpa =   ((1 - abs(sign(a.bllg_yyyymm - a.asof_yyyymm))) * a.mtd_bllg_amt) * charindex('zdpa',a.sls_ord_doc_ty_cd) ,
            DpaSales = ((1 - abs(sign(a.bllg_yyyymm - a.asof_yyyymm))) * a.mtd_bllg_amt) * charindex('or',a.sls_ord_doc_ty_cd) ,
            quarter = 'Q'   '"   @pass   "',
            year    = '"   @yyyy   "',
            status =    case when shp_cst_ctry_cd = 'US' then 'U.S.'
                               else'Foreign' end,
            warehouse = case  when substring(a.plt_cd,2,2) = 'ch' Then 'AZ'
                              when substring(a.plt_cd,2,2) = 'wa' Then 'WA'
                              when substring(a.plt_cd,2,2) = 'or' Then 'OR'
                              when substring(a.plt_cd,2,2) = 'sc' Then 'CA'
                              when substring(a.plt_cd,2,2) = 'fm' Then 'CA'
                              when substring(a.plt_cd,2,2) = 'ca' Then 'CA'
                              else 'Foreign' end,
            type =      case when a.shp_cst_ctry_cd = 'US' then '1'
                              else '2' end,
            pay_cst_nm = ' ',
            pay_state = ' '
       from data_store.dbo.tbl_billed_orders_"   @yyyy   @mm   " a, tpdb.dbo.tbl_cst_mstr b
       "   @str_where   " and  a.shp_cst_id *= b.cst_id and b.cst_acct_grp_cd not in('ZISH','ZISP','ZPRC')

--COMMIT TRANSACTION
"

I am able to execute this query when I am running on SQL Server 2016 but during migration it fails with above error?

Any suggestions on any changes in this query?

CodePudding user response：

You are using an ancient join style *=
Change this to a left join and it will work again.

Please stop using old join styles, this has been deprecated ages ago.

from data_store.dbo.tbl_billed_orders_"   @yyyy   @mm   " a, tpdb.dbo.tbl_cst_mstr b
       "   @str_where   " and  a.shp_cst_id *= b.cst_id and b.cst_acct_grp_cd not in('ZISH','ZISP','ZPRC')

It should be more something like this

from data_store.dbo.tbl_billed_orders_"   @yyyy   @mm   " a
  left join tpdb.dbo.tbl_cst_mstr b
    ON a.shp_cst_id = b.cst_id 
where b.cst_acct_grp_cd not in('ZISH','ZISP','ZPRC')