Syntax error in the case statment

[allik7]

I am have a problem with this section of code. When I place the select statemnet in a string variable i get an syntax error at RTRIM. Can the Case section be used in a string variable i am wondering

Cust_Org_Name =
CASE WHEN C.CustomerType ='+'I'+'THEN RTRIM(CL.Lastname)
WHEN C.CustomerType = '+'E'+'THEN RTRIM(CL.Customername)
END,

Thanks for your assistance

[Delerna]

One thing that stands out.
I think this ...'THEN... should be this ...' THEN... ie you need a space between ' and THEN because the above code comes out as

CASE WHEN C.CustomerType =ITHEN RTRIM(CL.Lastname) WHEN C.CustomerType = ETHEN RTRIM(CL.Customername) END,

There is no such command as ITHEN or ETHEN and therefore you get a syntax error

[Delerna]

Oh, and there is no reason you can't do what you are trying to do.
The info you provide leaves a bit puzzled at what you are doing but there is enough info to show that you are not trying to do something that can't be done.

[Delerna]

Just found your other post relating to this so I put an answer there also.
Hope it helps

[allik7]

I did what u said i should do and the error went somewhere else the error now look like this
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "D.TransactionType" could not be bound.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TransactionId'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "T.TransactionDate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "T.TransactionAmount" could not be bound.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'TransactionQuantity'.
Msg 207, Level 16, State 1, Line 3

and the fields are valid. What could be the problem

[Delerna]

that means we fixed the first problem and now there are more.
Ill go to your other post and have a look at the full code there

[Delerna]

Can I ask why you are using a dynamic query.
At first glance I can't see any reason why you shouldnt be using a regular query.

Another problem I see is that you can't referece a variable in a dynamic SQL string that is declared outside of the string.

Its also quite a large query, maybe youve gone over the 1000 characters you declared the string variable as.

also there are quite a lot of instances where you will need to add the + char(38) + in order to get the ' into the string.

I suggest you try copying the query into another query analyser window and change it into a regular query. It will be much easier to find the errors.

Unless of course there is a valid reason for it being dynamic.

[Delerna]

also this isn't correct in the field selection section

Cust_Org_Name = CASE WHEN C.CustomerType ='I' THEN RTRIM(CL.Lastname)WHEN C.CustomerType = 'E' THEN RTRIM(CL.Customername)END
 

it should like this

CASE WHEN C.CustomerType ='I' THEN RTRIM(CL.Lastname)WHEN C.CustomerType = 'E' THEN RTRIM(CL.Customername)END as Cust_Org_Name
 

[Delerna]

Here is your query as a standard query as best I could.
I may have missed a few things.
I have a feeling that this query is going to be quite slow due to there being subqueries in the selection fields, for example lines 34 and 35.

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),@saving as varChar(6)
Declare @move as varChar(12),@Other as varChar(5)
Declare @new as varChar(3),@old as varChar(3),@back 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'
 
 
SELECT D.TransactionType,TransactionId,T.TransactionDate, T.TransactionAmount As GrandTotal,TransactionQuantity As AmtDtl,
        TransactionConversionRate As Convrate,AC.Country As CustCtry,AP.Parish As CustPsh,AD.District AS CustDst,AM.Community As CustCom,CL.Street As CustSrt,
        TransactionNotes,CL.Occupation,CL.TaxRegistrationN umber As Cust_Trn,AG.Firstname As AgentFname,
        AG.LastName As AgentLname,SUBSTRING(AG.Middlename,1,1) As AgentMI,IT.EmploymentJobTitle As Title,AG.TaxRegistrationNumber As AgentTrn,
        AG.TelephoneNumbers As AgentPhone,CL.TelephoneNumbers As CustPhone,T.BeneficiaryCustomerCode As Cust_no,TransactionNotes As SourceOfFunds,
        AC2.Country As AgntCtry,AP2.Parish As AgntPsh,AD2.District AS AgntDst,AM2.Community As AgntCom,AG.Street As AgntSrt,CL.CustomerCode As AgentCode,
        Cust_Org_Name = CASE WHEN C.CustomerType ='I' THEN RTRIM(CL.Lastname)WHEN C.CustomerType = 'E' THEN RTRIM(CL.Customername)END,
    CL.Firstname,C2.CustomerIdentificationTypeDesc ription As AgentID,
        AG.DocumentNumber As AgentDocNum,
        T.DocumentNumber,C.CustomerIdentificationTypeDescr iption As ClientID,RTRIM(CurrencyAbbreviation) As CurDesc,RTRIM(InstrumentDescription) As InsDesc,TransactionTradeRate As ExRate
        ,CASE WHEN CL.DateTimeCreated > (SELECT TOP 1 GenDateTime FROM TradeThresholdReport WHERE CompanyCode = @Company AND LocationCode = @Location AND BranchCode = @Branch ORDER BY GenDateTime DESC ) THEN 'NEW' ELSE 'OLD' END as CustKnown,BankAccountNumber
    ,CASE WHEN AG.DateTimeCreated > (SELECT TOP 1 GenDateTime FROM TradeThresholdReport WHERE CompanyCode = @Company AND LocationCode = @Location AND BranchCode = @Branch ORDER BY GenDateTime DESC ) THEN  'NEW' ELSE 'OLD' END as AgentKnown,
    CASE WHEN IB.BankAccountType = 'C' THEN 'Current' WHEN IB.BankAccountType = 'S' THEN 'Saving' WHEN IB.BankAccountType = 'M' THEN 'Money Market' WHEN IB.BankAccountType = 'O' THEN 'Other'END as AccountType,
    Substring(Cast(T.DateTimeCreated as Char(24)),12,8) As tranTime,CL.DateOfBirth As Dob
FROM Customer AG RIGHT OUTER JOIN TradeTransactions T ON AG.CustomerCode = T.BeneficiaryCustomerCode
LEFT OUTER JOIN BankTransactionSummary BT
ON BT.OriginatingTransactionNumber = T.TransactionId
LEFT OUTER JOIN InventoryBank IB
ON IB.InventoryBankCode = BT.InventoryBankCode
,TradeTransactionsDetails D,CustomerIdentificationType C,CustomerIdentificationType C2,Customer CL,Currency CR,Instruments I,
SysAddressCountry AC,SysAddressCountry AC2,SysAddressParish AP,SysAddressParish AP2,SysAddressDistrict AD,SysAddressDistrict AD2,
SysAddressCommunity AM,SysAddressCommunity AM2,InventoryTraders IT,CustomerCategory CC
WHERE C.CustomerIdentificationTypeCode = IdentificationUsed AND CR. CurrencyCode = D.CurrencyCode AND Liquidity = 1
AND I.InstrumentCode = D.InstrumentCode AND D.CurrencyCode <> 1 AND VoidedByUser IS NULL
AND CL.CustomerCode = T.Customer AND T.TransactionId = TransactionNumber
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.TransactionType <> 'BackOrder' AND D.TransactionType = T.TransactionType
AND AC.CountryCode = CL.Country AND AP.ParishCode = CL.ParishOrState AND AD.DistrictCode = CL.DistrictOrCity AND CC.Include_In_Money_Laudering_Report = 1
AND AM.CommunityCode = CL.CommunityOrTownShip AND IT.TradersCode = T.TraderCode
AND AC2.CountryCode = AG.Country AND AP2.ParishCode = AG.ParishOrState AND AD2.DistrictCode = AG.DistrictOrCity
AND AM2.CommunityCode = AG.CommunityOrTownShip AND C2.CustomerIdentificationTypeCode = AG.CustomerIdentificationType AND CC.CompanyCode = @Company
AND T.CompanyCode = @Company AND T.LocationCode = @Location AND T.BranchCode = @Branch AND CC.CustomerCategoryCode = AG.CustomerCategory
AND D.CompanyCode = @Company AND D.LocationCode = @Location AND D.BranchCode = @Branch 
order by Cust_no, trantime ASC,CurDesc
 

[allik7]

Delerna suppose i want to place the slection in a temp table where would i put the INSERT INTO #TEMP

[Delerna]

INSERT INTO #TEMP
SELECT.....the rest of the query

Does that make sense