SQL Syntax Error

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • allik7
    New Member
    • Feb 2008
    • 10

    SQL Syntax Error

    [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
    Last edited by debasisdas; Feb 5 '08, 04:08 AM. Reason: added code=sql tags
  • ck9663
    Recognized Expert Specialist
    • Jun 2007
    • 2878

    #2
    what error was it?

    -- ck

    Comment

    • allik7
      New Member
      • Feb 2008
      • 10

      #3
      Incorrect Syntax at RTRIM copy and paste it in query and you will see the error
      thanks in advance

      Comment

      • Delerna
        Recognized Expert Top Contributor
        • Jan 2008
        • 1134

        #4
        you need to put a space between the ' and the THEN
        that comes just before RTRIM(CL.FirstN ame)

        Comment

        • Delerna
          Recognized Expert Top Contributor
          • Jan 2008
          • 1134

          #5
          you will also need to add a + char(39) + either side of I and E so that you can get the strin quote into the string

          This bit
          CASE WHEN C.CustomerType ='+'I'+'THEN RTRIM(CL.Lastna me)
          will appear in your string as
          CASE WHEN C.CustomerType =ITHEN RTRIM(CL.Lastna me)

          where as you need it to be
          CASE WHEN C.CustomerType ='I' THEN RTRIM(CL.Lastna me)

          so this will do it
          CASE WHEN C.CustomerType =' + char(39) + 'I' + char(39) + ' THEN RTRIM(CL.Lastna me)

          Comment

          • ck9663
            Recognized Expert Specialist
            • Jun 2007
            • 2878

            #6
            i'd assume delarna is right and will not paste the whole code...

            also try this:

            CASE WHEN C.CustomerType = '+'I'+' THEN RTRIM(CL.Lastna me)

            i just added space between the single-quote and equal sign and another space between single-quote and THEN...

            -- ck

            Comment

            Working...