[code=sql]
Declare @String as nVarChar(1000)
Declare @Ind as Char(1),@Com as Char(1),@BAC3 as Char(1)
Declare @BAC1 as Char(1),@BAC2 as Char(1),@BAC4 as Char(1)
Declare @current as varChar(7),@sav ing as varChar(6)
Declare @move as varChar(12),@Ot her as varChar(5)
Declare @new as varChar(3),@old as varChar(3),@bac k as varChar(10)
set @Ind = 'I'
set @Com = 'E'
set @BAC1 = 'C'
set @BAC2 = 'S'
set @BAC3 = 'M'
set @BAC4 = 'O'
set @current = 'Current'
Set @saving = 'Saving'
Set @move = 'Money Market'
SET @Other = 'Other'
Set @new = 'NEW'
Set @old = 'OLD'
Set @back = 'BACKORDER'
SET @String = 'SELECT D.TransactionTy pe,TransactionI d,T.Transaction Date,T.Transact ionAmount As GrandTotal,
TransactionQuan tity As AmtDtl,
TransactionConv ersionRate As Convrate,AC.Cou ntry As CustCtry,AP.Par ish As CustPsh,AD.Dist rict AS CustDst,AM.Comm unity As CustCom,CL.Stre et As CustSrt,
TransactionNote s,CL.Occupation ,CL.TaxRegistra tionNumber As Cust_Trn,AG.Fir stname As AgentFname,
AG.LastName As AgentLname,SUBS TRING(AG.Middle name,1,1) As AgentMI,IT.Empl oymentJobTitle As Title,AG.TaxReg istrationNumber As AgentTrn,
AG.TelephoneNum bers As AgentPhone,CL.T elephoneNumbers As CustPhone,T.Ben eficiaryCustome rCode As Cust_no,Transac tionNotes As SourceOfFunds,
AC2.Country As AgntCtry,AP2.Pa rish As AgntPsh,AD2.Dis trict AS AgntDst,AM2.Com munity As AgntCom,AG.Stre et As AgntSrt,CL.Cust omerCode As AgentCode,
Cust_Org_Name =
CASE WHEN C.CustomerType ='+'I'+'THEN RTRIM(CL.Lastna me)
WHEN C.CustomerType = '+'E'+'THEN RTRIM(CL.Custom ername)
END,CL.Firstnam e,C2.CustomerId entificationTyp eDescription As AgentID,AG.Docu mentNumber As AgentDocNum,
T.DocumentNumbe r,C.CustomerIde ntificationType Description As ClientID,RTRIM( CurrencyAbbrevi ation) As CurDesc,RTRIM(I nstrumentDescri ption) As InsDesc,Transac tionTradeRate As ExRate
,CustKnown =
CASE WHEN CL.DateTimeCrea ted > (SELECT TOP 1 GenDateTime FROM TradeThresholdR eport WHERE CompanyCode = @Company AND LocationCode = @Location AND BranchCode = @Branch ORDER BY GenDateTime DESC ) THEN'+ 'NEW'+'
ELSE '+'OLD'+'
END,BankAccount Number
,AgentKnown =
CASE WHEN AG.DateTimeCrea ted > (SELECT TOP 1 GenDateTime FROM TradeThresholdR eport WHERE CompanyCode = @Company AND LocationCode = @Location AND BranchCode = @Branch ORDER BY GenDateTime DESC ) THEN '+ 'NEW'+'
ELSE '+ 'OLD'+'
END,
AccountType =
CASE WHEN IB.BankAccountT ype = '+'C'+' THEN '+'Current'+'
WHEN IB.BankAccountT ype = '+'S'+' THEN '+'Saving'+'
WHEN IB.BankAccountT ype = '+'M'+' THEN '+'Money Market'+'
WHEN IB.BankAccountT ype = '+'O'+' THEN '+'Other'+'
END,Substring(C ast(T.DateTimeC reated as Char(24)),12,8) As tranTime,CL.Dat eOfBirth As Dob
FROM Customer AG RIGHT OUTER JOIN TradeTransactio ns T ON AG.CustomerCode = T.BeneficiaryCu stomerCode
LEFT OUTER JOIN BankTransaction Summary BT
ON BT.OriginatingT ransactionNumbe r = T.TransactionId
LEFT OUTER JOIN InventoryBank IB
ON IB.InventoryBan kCode = BT.InventoryBan kCode
,TradeTransacti onsDetails D,CustomerIdent ificationType C,CustomerIdent ificationType C2,Customer CL,Currency CR,Instruments I,
SysAddressCount ry AC,SysAddressCo untry AC2,SysAddressP arish AP,SysAddressPa rish AP2,SysAddressD istrict AD,SysAddressDi strict AD2,
SysAddressCommu nity AM,SysAddressCo mmunity AM2,InventoryTr aders IT,CustomerCate gory CC
WHERE C.CustomerIdent ificationTypeCo de = IdentificationU sed AND CR. CurrencyCode = D.CurrencyCode AND Liquidity = 1
AND I.InstrumentCod e = D.InstrumentCod e AND D.CurrencyCode <> 1 AND VoidedByUser IS NULL
AND CL.CustomerCode = T.Customer AND T.TransactionId = TransactionNumb er
AND I.CompanyCode = @Company And I.LocationCode = @Location AND I.BranchCode = @Branch
AND CR.CompanyCode = @Company And CR.LocationCode = @Location AND CR.BranchCode = @Branch
AND D.TransactionTy pe <> '+'BackOrder'+' AND D.TransactionTy pe = T.TransactionTy pe
AND AC.CountryCode = CL.Country AND AP.ParishCode = CL.ParishOrStat e AND AD.DistrictCode = CL.DistrictOrCi ty AND CC.Include_In_M oney_Laudering_ Report = 1
AND AM.CommunityCod e = CL.CommunityOrT ownShip AND IT.TradersCode = T.TraderCode
AND AC2.CountryCode = AG.Country AND AP2.ParishCode = AG.ParishOrStat e AND AD2.DistrictCod e = AG.DistrictOrCi ty
AND AM2.CommunityCo de = AG.CommunityOrT ownShip AND C2.CustomerIden tificationTypeC ode = AG.CustomerIden tificationType AND CC.CompanyCode = @Company
AND T.CompanyCode = @Company AND T.LocationCode = @Location AND T.BranchCode = @Branch AND CC.CustomerCate goryCode = AG.CustomerCate gory
AND D.CompanyCode = @Company AND D.LocationCode = @Location AND D.BranchCode = @Branch
order by Cust_no, trantime ASC,CurDesc'
--print @String
--set @String = 'Select * from TradeTransactio nsDetails'
Exec(@String)
[/code]
I am getting a syntax error at RTRIM(CL.FirstN ame) can some one tell me why
Declare @String as nVarChar(1000)
Declare @Ind as Char(1),@Com as Char(1),@BAC3 as Char(1)
Declare @BAC1 as Char(1),@BAC2 as Char(1),@BAC4 as Char(1)
Declare @current as varChar(7),@sav ing as varChar(6)
Declare @move as varChar(12),@Ot her as varChar(5)
Declare @new as varChar(3),@old as varChar(3),@bac k as varChar(10)
set @Ind = 'I'
set @Com = 'E'
set @BAC1 = 'C'
set @BAC2 = 'S'
set @BAC3 = 'M'
set @BAC4 = 'O'
set @current = 'Current'
Set @saving = 'Saving'
Set @move = 'Money Market'
SET @Other = 'Other'
Set @new = 'NEW'
Set @old = 'OLD'
Set @back = 'BACKORDER'
SET @String = 'SELECT D.TransactionTy pe,TransactionI d,T.Transaction Date,T.Transact ionAmount As GrandTotal,
TransactionQuan tity As AmtDtl,
TransactionConv ersionRate As Convrate,AC.Cou ntry As CustCtry,AP.Par ish As CustPsh,AD.Dist rict AS CustDst,AM.Comm unity As CustCom,CL.Stre et As CustSrt,
TransactionNote s,CL.Occupation ,CL.TaxRegistra tionNumber As Cust_Trn,AG.Fir stname As AgentFname,
AG.LastName As AgentLname,SUBS TRING(AG.Middle name,1,1) As AgentMI,IT.Empl oymentJobTitle As Title,AG.TaxReg istrationNumber As AgentTrn,
AG.TelephoneNum bers As AgentPhone,CL.T elephoneNumbers As CustPhone,T.Ben eficiaryCustome rCode As Cust_no,Transac tionNotes As SourceOfFunds,
AC2.Country As AgntCtry,AP2.Pa rish As AgntPsh,AD2.Dis trict AS AgntDst,AM2.Com munity As AgntCom,AG.Stre et As AgntSrt,CL.Cust omerCode As AgentCode,
Cust_Org_Name =
CASE WHEN C.CustomerType ='+'I'+'THEN RTRIM(CL.Lastna me)
WHEN C.CustomerType = '+'E'+'THEN RTRIM(CL.Custom ername)
END,CL.Firstnam e,C2.CustomerId entificationTyp eDescription As AgentID,AG.Docu mentNumber As AgentDocNum,
T.DocumentNumbe r,C.CustomerIde ntificationType Description As ClientID,RTRIM( CurrencyAbbrevi ation) As CurDesc,RTRIM(I nstrumentDescri ption) As InsDesc,Transac tionTradeRate As ExRate
,CustKnown =
CASE WHEN CL.DateTimeCrea ted > (SELECT TOP 1 GenDateTime FROM TradeThresholdR eport WHERE CompanyCode = @Company AND LocationCode = @Location AND BranchCode = @Branch ORDER BY GenDateTime DESC ) THEN'+ 'NEW'+'
ELSE '+'OLD'+'
END,BankAccount Number
,AgentKnown =
CASE WHEN AG.DateTimeCrea ted > (SELECT TOP 1 GenDateTime FROM TradeThresholdR eport WHERE CompanyCode = @Company AND LocationCode = @Location AND BranchCode = @Branch ORDER BY GenDateTime DESC ) THEN '+ 'NEW'+'
ELSE '+ 'OLD'+'
END,
AccountType =
CASE WHEN IB.BankAccountT ype = '+'C'+' THEN '+'Current'+'
WHEN IB.BankAccountT ype = '+'S'+' THEN '+'Saving'+'
WHEN IB.BankAccountT ype = '+'M'+' THEN '+'Money Market'+'
WHEN IB.BankAccountT ype = '+'O'+' THEN '+'Other'+'
END,Substring(C ast(T.DateTimeC reated as Char(24)),12,8) As tranTime,CL.Dat eOfBirth As Dob
FROM Customer AG RIGHT OUTER JOIN TradeTransactio ns T ON AG.CustomerCode = T.BeneficiaryCu stomerCode
LEFT OUTER JOIN BankTransaction Summary BT
ON BT.OriginatingT ransactionNumbe r = T.TransactionId
LEFT OUTER JOIN InventoryBank IB
ON IB.InventoryBan kCode = BT.InventoryBan kCode
,TradeTransacti onsDetails D,CustomerIdent ificationType C,CustomerIdent ificationType C2,Customer CL,Currency CR,Instruments I,
SysAddressCount ry AC,SysAddressCo untry AC2,SysAddressP arish AP,SysAddressPa rish AP2,SysAddressD istrict AD,SysAddressDi strict AD2,
SysAddressCommu nity AM,SysAddressCo mmunity AM2,InventoryTr aders IT,CustomerCate gory CC
WHERE C.CustomerIdent ificationTypeCo de = IdentificationU sed AND CR. CurrencyCode = D.CurrencyCode AND Liquidity = 1
AND I.InstrumentCod e = D.InstrumentCod e AND D.CurrencyCode <> 1 AND VoidedByUser IS NULL
AND CL.CustomerCode = T.Customer AND T.TransactionId = TransactionNumb er
AND I.CompanyCode = @Company And I.LocationCode = @Location AND I.BranchCode = @Branch
AND CR.CompanyCode = @Company And CR.LocationCode = @Location AND CR.BranchCode = @Branch
AND D.TransactionTy pe <> '+'BackOrder'+' AND D.TransactionTy pe = T.TransactionTy pe
AND AC.CountryCode = CL.Country AND AP.ParishCode = CL.ParishOrStat e AND AD.DistrictCode = CL.DistrictOrCi ty AND CC.Include_In_M oney_Laudering_ Report = 1
AND AM.CommunityCod e = CL.CommunityOrT ownShip AND IT.TradersCode = T.TraderCode
AND AC2.CountryCode = AG.Country AND AP2.ParishCode = AG.ParishOrStat e AND AD2.DistrictCod e = AG.DistrictOrCi ty
AND AM2.CommunityCo de = AG.CommunityOrT ownShip AND C2.CustomerIden tificationTypeC ode = AG.CustomerIden tificationType AND CC.CompanyCode = @Company
AND T.CompanyCode = @Company AND T.LocationCode = @Location AND T.BranchCode = @Branch AND CC.CustomerCate goryCode = AG.CustomerCate gory
AND D.CompanyCode = @Company AND D.LocationCode = @Location AND D.BranchCode = @Branch
order by Cust_no, trantime ASC,CurDesc'
--print @String
--set @String = 'Select * from TradeTransactio nsDetails'
Exec(@String)
[/code]
I am getting a syntax error at RTRIM(CL.FirstN ame) can some one tell me why
Comment