Here is my Stored procedure written in SQLServer 2005. Now we are moving to SQL2012 and getting bunch of errors where I used *= . I know I have to use LEFT OUTER JOIN syntax, but somehow I am stumped. Can someone please help me ? Also getting error on where I used ABS in where clause.
These 2 lines in question in WHERE CLAUSE BELOW
currhold.current_hold__001 *= #tmp_transac.current_hold__001 and
((abs(t_quantity_c)> 0.01 and #tmp_transac.current_hold__001 is null) or
Full SP is here
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_getEPMData_GFS]
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#factortemp','u') IS NOT NULL
BEGIN
DROP TABLE #factortemp
END
IF OBJECT_ID('tempdb..#transactemp','u') IS NOT NULL
BEGIN
DROP TABLE #transactemp
END
select * into #transactemp from transac where tran_type in ('BUY','SHORT')
select curr_factor,cusip into #factortemp
from mbs_fact A
where A.factor_date =
(select max(B.factor_date) from mbs_fact B
where B.cusip = A.cusip --and B.factor_date <= '10/30/2008'
group by B.cusip)
IF OBJECT_ID('tempdb..#pricetemp','u') IS NOT NULL
BEGIN
DROP TABLE #pricetemp
END
Create TABLE #pricetemp
(price numeric(19,8),cusip varchar(20),price_source varchar(20),Max_Price_Date smalldatetime)
IF OBJECT_ID('tempdb..#pricetemp1','u') IS NOT NULL
BEGIN
DROP TABLE #pricetemp1
END
IF OBJECT_ID('tempdb..#pricetemp2','u') IS NOT NULL
BEGIN
DROP TABLE #pricetemp2
END
select cusip, max(price_date) as MX_pxdate
INTO #pricetemp1
from pricing
group by cusip
order by 1
SELECT A.cusip,B.Mx_pxdate, count(*) as dupcount
INTO #pricetemp2
FROM pricing A, #pricetemp1 B
WHERE A.cusip = B.cusip
AND A.price_date = B.MX_pxdate
group by A.cusip,B.Mx_pxdate
insert into #pricetemp
select A.price,A.cusip,A.price_source,B.MX_pxdate
from pricing A, #pricetemp2 B
where A.price_date = B.MX_pxdate
AND A.cusip = B.cusip
AND B.dupcount = 1
insert into #pricetemp
select A.price,A.cusip,A.price_source,B.MX_pxdate
from pricing A, #pricetemp2 B
where A.price_date = B.MX_pxdate
AND A.cusip = B.cusip
AND A.price_source='MANUAL'
AND B.dupcount = 2
IF OBJECT_ID('tempdb..#tmp_transac','u') IS NOT NULL
BEGIN
DROP TABLE #tmp_transac
END
select distinct current_hold__001, sum(abs(transac.total_amount)) as total_amount,broker
into #tmp_transac
from transac
where setl_y_n = 'N'
and replace(tran_type, ' ', '') in
(select distinct replace(tran_type, ' ', '') as tran_type from ut_transac_types where amount_sign = 'POSITIVE')
group by current_hold__001,broker
declare @System_Date smalldatetime
Select @System_Date = Convert(varchar(12),dbo.uf_getGLDate(),112)
--ISB
IF OBJECT_ID('tempdb..#tmpISB','u') IS NOT NULL
BEGIN
DROP TABLE #tmpISB
END
select distinct
iss.user_desc1 as Issuer_ISB,
Guarantor.user_desc1 as Guarantor_ISB,
Security.Cusip
into #tmpISB
from Security
left outer join Issuer Iss ON (Security.issuer = Iss.issuer )
left outer join Issuer Guarantor ON (Security.Guarantor = Guarantor.issuer )
--
-----Existing EPM on Top
SELECT case
when #factortemp.curr_factor = 0.00 then 0.00
when #factortemp.curr_factor is null then 0.00
else #factortemp.curr_factor end as curr_factor,
case
when #pricetemp.price = 0.00 then 100.000000000
when #pricetemp.price is null then 100.000000000
else #pricetemp.price end as price,
replace(security.description1,',',' ') as description1,security.isin,portfoli.MGR1_TITLE as branch,
currhold.portfolio,replace(currhold.sec_type,' ','') as sec_type,
Convert(float,currhold.CURRENT_HOLD__001)*10000 as Trade_num,
currhold.cusip,
securtyp.prn_curr,
Chartacc.securities,
securitiesamt=case When SECURTYP.SEC_TYPE='RESIDCERT' Then Convert(decimal(18,2),CURRHOLD.GORIG_COST_C)
else Convert(decimal(18,2),CURRHOLD.T_QUANTITY_C)
End,
chartacc.INT_DUE_ACCRUED,
INT_DUE_ACCRUEDAmt = (CURRHOLD.T_ACCR_INTRST_C + CURRHOLD.T_INT_DIV_DUE_C),
chartacc.Discount,
DiscountAmt = case When SECURTYP.SEC_TYPE='RESIDCERT' Then 0
When (CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C) > 0 THEN 0
ELSE Convert(decimal(18,2),CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C)
End,
chartacc.Premium,
PREMIUMAmt=case When SECURTYP.SEC_TYPE = 'RESIDCERT' Then 0
When (CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C) < 0 THEN 0
ELSE Convert(decimal(18,2),CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C)
End,
chartacc.UNRLZD_GL_ASSET,
UNRLZD_GL_ASSETAmt=isnull(Convert(decimal(18,2),(CURRHOLD.GMARKET_VALUE_C - CURRHOLD.GBOOK_VALUE_C)),0),
chartacc.UNRLZD_GL_INC,
UNRLZD_GL_INCAmt = isnull(Convert(decimal(18,2),(CURRHOLD.GBOOK_VALUE_C - CURRHOLD.GMARKET_VALUE_C)),0),
chartacc.INTEREST_INCOME,
Interest_incomeamt= Convert(decimal(18,2),CURRHOLD.GAMZ_YTD_P+CURRHOLD.T_ID_EARN_YTD_P),
chartacc.RECVABLE_BROKER,
RECVABLE_BROKERAmt=
case when CURRHOLD.STATUS in ('SELL','SELL TBA') and (CURRHOLD.GSETTLE_DATE) >= dbo.uf_getGLDate()
then isnull((CURRHOLD.GORIG_COST_C)-(CURRHOLD.T_DUE_ACCR_CY_C),0)
else isnull(#tmp_transac.total_amount, 0) -- - isnull(seccash.prin_amount, 0)
end,
chartacc.PAYABLE_BROKER,
PAYABLE_BROKERAmt=
case when CURRHOLD.STATUS in ('BUY','BUY TBA') and (CURRHOLD.GSETTLE_DATE) >= dbo.uf_getGLDate()
then isnull(-(CURRHOLD.GORIG_COST_C)-(CURRHOLD.T_DUE_ACCR_CY_C),0)
else 0
end,
CurrStatus=Case when currhold.status in ('BUY','SELL') AND currhold.closed_date > dbo.uf_getGLDate() and currhold.T_QUANTITY_C <> 0 THEN 1
when currhold.status in ('BUY','SELL') AND currhold.gsettle_date > dbo.uf_getGLDate() and currhold.T_QUANTITY_C <> 0 THEN 3
else 2
end,
myPrincBalAL= Case when currhold.T_QUANTITY_C > 0 Then 'A' else 'L' end,
myAccruedAmtAL= Case when currhold.t_accr_intrst_c + currhold.t_int_div_due_c > 0 Then 'A' else 'L' end,
myUnearnedpremAL= Case when (currhold.T_QUANTITY_C - currhold.GBOOK_VALUE_C) < 0 Then 'A' Else 'L' end,
myUnearnedDiscAL = Case when (currhold.T_QUANTITY_C - currhold.GBOOK_VALUE_C) < 0 Then '' Else 'L' end,
myUnrealProfitAL= Case when currhold.Gunrl_gl_ytd_c > 0 Then 'A' Else 'L' end,
myUnrealLossAL = Case when currhold.Gunrl_gl_ytd_c > 0 Then '' Else 'L' end,
myCouponRate = case when ltrim(rtrim(currhold.sec_group)) = 'SHORT TERM'
then Convert(decimal(20,7),currhold.COUPON_RATE/100)
else Convert(decimal(20,7),security.COUPON_RATE/100) end,
myFixRate = Case when [security].Float_Rate = 'Y'
then Convert(decimal(20,7),[security].index_rate_add/100)
else case when ltrim(rtrim(currhold.sec_group)) = 'SHORT TERM'
then Convert(decimal(20,7),currhold.COUPON_RATE/100)
else Convert(decimal(20,7),security.COUPON_RATE/100) end
end,
myFixVarInd=Case when Security.float_rate='Y' then 'V' else 'F' end,
myIntIncomeAL = case when (currhold.t_accr_intrst_c + currhold.Gchange_amz_c) > 0 Then 'I' else '' end,
Convert(varchar(12),currhold.gTrade_Date,112) as RB_VALDATE,
Convert(varchar(12),currhold.maturity_date,112) as Maturity_date,
Convert(varchar(12),currhold.gTrade_Date,112) as RB_TRADEDATE,
Replace([security].sec_type,' ','') as Product,[security].guarantor as CIF_No,
myTradeType = Case when currhold.Portfolio = 'ABST' THEN 'TRADING' else 'SALE' end,
Open_Flag='Y',
Convert(varchar(12),currhold.GSETTLE_DATE,112) as GSETTLE_DATE,
currhold.t_quantity_c as Quantity,
INT_RATE= case when ltrim(rtrim(currhold.sec_group)) = 'SHORT TERM'
then currhold.Coupon_Rate - (security.Index_Rate_Add/100)
else security.Coupon_Rate - (security.Index_Rate_Add/100) end,
security.Index_Rate_Add,
Convert(varchar(12),security.Next_PMT_Date,112) as Next_PMT_Date,
currhold.status,
currhold.account,
SECURITY.FLOAT_FREQ,
'SOURCE' as Source_Code,
Convert(varchar(12),@System_Date,112) as System_Date,
#transactemp.broker,
security.issuer,
Convert(decimal(20,8),currhold.gorig_price) as Orig_Price,
isnull(portfoli.MGR2_TITLE,'') as OBU,
isnull(#tmpISB.Issuer_ISB,'') as Issuer_ISB,
Security.Author,
isnull(Convert(varchar(12),#pricetemp.Max_Price_Date,112),Convert(varchar(12),@System_Date,112)) as Max_Price_Date,
Security.Symb_pool,
isnull(Convert(varchar(12),Currhold.Issue_Date,112),'') as Issue_Date,
isnull(Convert(varchar(12),Security.Last_pmt_date,112),Convert(varchar(12),@System_Date,112)) as Last_pmt_date,
Security.Accrual,
isnull(Convert(varchar(12),Security.Last_float_Date,112),'') as Last_float_date,
isnull(Convert(varchar(12),Security.Next_Float_Date,112),'') as Next_float_date,
isnull([security].guarantor,'') as guarantor,
isnull(Convert(varchar(12),Currhold.Maturity_date,112),'') as LAST_PRIN_PMT,
Security.Country,
isnull(currhold.ANT_HEDGE,'') as ANT_HEDGE,
isnull(Broker.User_desc1,'') as Broker_ISB,
isnull(#tmpISB.Guarantor_ISB,'') as Guarantor_ISB
from securtyp,chartacc,security,portfoli,#factortemp,#pricetemp,currhold,#tmp_transac,issuer,#transactemp,broker,#tmpISB
where
#tmpISB.cusip=security.cusip and
#transactemp.broker=broker.broker and
security.cusip=#transactemp.cusip and
#transactemp.current_hold__001 = currhold.current_hold__001 and
#transactemp.cusip = currhold.cusip and
currhold.current_hold__001 *= #tmp_transac.current_hold__001 and
((abs(t_quantity_c)> 0.01 and #tmp_transac.current_hold__001 is null) or
#tmp_transac.current_hold__001 is not null) and
portfoli.account=currhold.Account and
portfoli.portfolio=currhold.Portfolio and
securtyp.sec_type=currhold.sec_type and
security.cusip=currhold.cusip and
currhold.account=chartacc.account and
currhold.portfolio = chartacc.portfolio and
currhold.sec_group = chartacc.sec_group and
currhold.sec_type=chartacc.sec_type and
currhold.custodian = chartacc.custodian and
currhold.cusip*=#factortemp.cusip and
currhold.cusip*=#pricetemp.cusip and
security.issuer = issuer.issuer and
CURRHOLD.PORTFOLIO not in (select portfolio from ut_portfolio_exception where proc_name='SKY')
and abs(CURRHOLD.T_QUANTITY_C) > 0.01
and currhold.account not in ('SEC PUR','SEC SOLD','RABO SEC','GOSMORE')
Aucun commentaire:
Enregistrer un commentaire