Error 156, problems with a case statement

  • As a disclaimer before I get into the actual problem, I am very new to databasing. This may or may not be an easy fix problem, but I'd rather look stupid than miss a deadline.

    That out of the way, I'm trying to write a procedure that will meld information from various sources, namely three different tables and a flat file. For the most part, I've worked everything out, but I'm having trouble with a case statement which is supposed to essentially update information in this table with information from one of two companies. Here's the code I have for one of the two procedures, the one which is not working.

    The key between the two tables is the customer number, which can be an integer anywhere from 1 to 8 characters long. The account number is that same number, except with a 'C' appended to the front and padded zeros so that the entry is always 9 characters long. Ideally what I want to happen is if there's an account number that matches the customer number, then that entry needs to be updated, otherwise a new entry needs to be added to the table. Just looking at it, I realize right away there would be possible problem with how I actually do the check for updates, but I'm still not entirely sure what to do there. Anyway, any help would be greatly appreciated.

    --------------------------------------

    --Merges the data from the CUSTOMERMST tables into the MASTER_ACCOUNT table

    CASE

     WHEN EXISTS CUSTOMERNUMBER LIKE ''%CUSTOMERNUMBER''

      THEN UPDATE Master_Account SET Name = D_CUSTOMERMST.CUSTOMERNAME AND

      Address1 = D_CUSTOMERMST.ADDRESS1 AND Telephone = D_CUSTOMERMST.PHONE

      Zipcode = F_CUSTOMERMST.ZIPCODE AND Town = F_CUSTOMERMST.CITY

      AND State = F_CUSTOMERMST.STATE AND Branch = F_CUSTOMERMST.PRIMARYBRANCH

      AND EMail = F_CUSTOMERMST.EMAIL1 AND CreditLimit = F_CUSTOMERMST.CREDITLIMIT

      AND BalanceTotal = F_CUSTOMERMST.BALANCE AND Balance30 = F_CUSTOMERMST.BALANCE

      AND NewUpdate = 'U'

      WHERE CUSTOMERNUMBER LIKE ''%CUSTOMERNUMBER'' )

     WHEN NOT EXISTS CUSTOMERNUMBER LIKE ''%CUSTOMERNUMBER''

      THEN INSERT INTO [STG_CRP_RR].[dbo].[Master_Account]

       select

      Company = 1 ,

      NewUpdate = 'N' ,

      Ledger = 'S' ,

      AccountType = 'A' ,

      AccountNumber = NULL ,

      AddressNumber = 0 ,

      Currency = 'USD',

      Status = 'A' ,

      [Name] = d.CustomerName + ' ' + TransformedDWData.dbo.PadNumericZeros(d.CustomerNumber, 6),

      Address1 = case

       when len(d.Address1) > 0 then substring(d.Address1,1,30)

       else 'Address1 Not Available'

       end ,

      Address2 = d.Address2 ,

      Address3 = d.Address3 ,

      Address4 = NULL ,

      Zipcode = d.Zipcode ,

      State = d.State ,

      Town = d.City ,

      ShortName = TransformedDWData.dbo.PadNumericZeros(d.CustomerNumber,8) ,

      Telephone = d.Phone ,

      Fax = NULL ,

      TaxCountryCode = 'USA' ,

      CountryCode = 'USA' ,

      TaxNumber = NULL ,

      Delivery = 'Y' ,

      Invoice = 'Y' ,

      CompReg = NULL ,

      EMail = NULL ,

      URL = NULL ,

      Rep = r.Login,

      Branch = d.PrimaryBranchID ,

      Competitor = NULL ,

      SIC = 9990 ,

      Turnover = NULL ,

      PotentialSpend = NULL ,

      Employees = NULL ,

      Sites = NULL ,

      CallFrequency = 'M' ,

      CallFrequencyUnits = 1 ,

      AccountSet1 = 'ME' ,

      AccountSet2 = NULL ,

      AccountSet3 = NULL ,

      AccountSet4 = NULL ,

      AccountSet5 = NULL ,

      AccountSet6 = NULL ,

      AccountSet7 = NULL ,

      AccountSet8 = NULL ,

      AccountSet9 = NULL ,

      ProspectSet1 = 'CON' ,

      ProspectSet2 = 9990 ,

      ProspectSet3 = 100 ,

      ProspectSet4 = 5000 ,

      ProspectSet5 = NULL ,

      ProspectSet6 = NULL ,

      ProspectSet7 = NULL ,

      ProspectSet8 = NULL ,

      ProspectSet9 = NULL ,

      SalesOperator = NULL ,

      PurchaseOperator = NULL ,

      ManagerOperator = NULL ,

      DirectorOperator = NULL ,

      Priority = NULL ,

      InvoiceDiscount = NULL ,

      VATCode = NULL ,

      Statement = 'Y' ,

      CreditLimit = d.CreditLimit ,

      MainHead = NULL ,

      PaymentTermsSalesOverride = 'N' ,

      PaymentTermsSalesCode = 1 ,

      PaymentTermsSalesType = NULL ,

      PaymentTermsSales1 = NULL ,

      PaymentTermsSalesPercent1 = NULL ,

      PaymentTermsSales2 = NULL ,

      PaymentTermsSalesPercent2 = NULL ,

      PaymentTermsSales3 = NULL ,

      PaymentTermsPercentSales3 = NULL ,

      PaymentTermsHireOverride = 'N' ,

      PaymentTermsHireCode = 1 ,

      PaymentTermsHireType = NULL ,

      PaymentTermsHire1 = NULL ,

      PaymentTermsHirePercent1 = NULL ,

      PaymentTermsHire2 = NULL ,

      PaymentTermsHirePercent2 = NULL ,

      PaymentTermsHire3 = NULL ,

      PaymentTermsHirePercent3 = NULL ,

      PartNumbers = 'N' ,

      RecordItem = 'Y' ,

      SalesPriceList = NULL ,

      DiscountList = NULL ,

      PORequired = 'M' ,

      POUnique = 'N' ,

      PartOrders = 'Y' ,

      PartLines = 'Y' ,

      InvoiceBy = 'MO' ,

      InvoiceExtras = 'M' ,

      InternalExternal = 'E' ,

      StopCriteria = NULL ,

      RaiseCustomerExtras = NULL ,

      CustomInvoice = 'N' ,

      PORentalNotes1 = NULL ,

      PORentalNotes2 = NULL ,

      PORentalNotes3 = NULL ,

      PORentalNotes4 = NULL ,

      PORentalNotes5 = NULL ,

      PORentalNotes6 = NULL ,

      PORentalNotes7 = NULL ,

      PORentalNotes8 = NULL ,

      PORentalNotes9 = NULL ,

      PORentalNotes10 = NULL ,

      PORentalNotes11 = NULL ,

      PORentalNotes12 = NULL ,

      PORentalNotes13 = NULL ,

      PORentalNotes14 = NULL ,

      PORentalNotes15 = NULL ,

      PORentalNotes16 = NULL ,

      CreateDate = getdate() ,

      UpdateDate = getdate() ,

      BalanceTotal = d.Balance ,

      BalanceDue = 0 ,

      Balance30 = d.Balance ,

      Balance60 = 0 ,

      Balance90 = 0 ,

      BalanceXX = 0 ,

      SourceSystem = 'MTA'

      from TransformedDWData..f_CustomerMST d

      LEFT OUTER JOIN stg_crp_rr..RRSalesRep r

      ON cast(d.RentalSalesperson as int) = cast(r.[Sales #] as int)

      and r.Login like 'nes%'

      where d.SaleType <> '5'

      and d.PriceCode <> 'A'

      and d.StatementCycle <> 2

     update interface_account

     set Branch = NULL

     where Branch not in

      (select [branch code]

      from stg_crp_rr..RRBranch)

     

     exec sp_Interface_Account_PrimaryBranchZipCode_Update

     

     delete from Master_Account

     where Branch not in

      (select [Branch Code] from RRBranch

      where RREnabled = 'Y')

     

     update Master_Account

     set Rep = r.Login

     from RRSalesRep r,

      Interface_Account i

     where r.[Sales #] = cast(substring(i.Branch,2,3) as int)

     and i.Rep is NULL

     update Master_Account

     set Rep = 'nes0000'

     where Rep is NULL

     

     update Master_Account

     set Address1 = d.Terms

     from TransformedDWData..d_customermst d,

      interface_account i

     where cast(i.shortname as integer) = d.customernumber

     and len(i.address1) = 0

     and d.terms in ('LGL','CASH')

     

     insert into Archive_Account

     select getdate(), * from Interface_Account

    END

    -----------------------------------
     
  • Looking at your code, not even going to try and fix it.  You are using CASE incorrectly to start.  From your description of the problem, your script (or stored procedure) should probably be using an IF THEN ELSE control structure:

    IF update criteria THEN

    BEGIN

        UPDATE table SET

                 column1 = value (or source column),

                 column2 = ...

        FROM

           ...

    END

    ELSE

    BEGIN

        INSERT ...

    END

  • The problem is I need it to do it on a record by record basis. IF statements do a table-wide check against whatever my criteria is, correct?

     

    EDIT: I've sorted out a reasonable way to make the comparison and limited the problem down to one of the AND's in the initial WHEN statement. Now it's just a matter of rooting through it. If someone wants to actually take the time to find it, I would appreciate it, but I certainly don't expect help with something as mundane as that.

  • SQL Server doesn't use IF...THEN.  Just IF/ELSE IF.  However, THEN is used with CASE.

  • True, I goofed with the THEN, I sometimes put it in as a line comment to keep things straight in my mind.

  • Is this a legal condition?

    WHEN  CUSTOMERNUMBER = CAST(SUBSTRING(Account_Number, 2, 8) AS int)

  • No, you cannot use a CASE statement in this manner.  You mentioned that you need to do this on a row-by-row basis.  Does this mean that you are using a cursor?  What you should do here is to run the UPDATE statement without any if/then/case logic.  If the UPDATE statement finds and updates a row (you can check @@ROWCOUNT to find this), skip the INSERT.  If the UPDATE does not find and update a row (@@ROWCOUNT = 0), run the INSERT statement. 

    If your answer to the cursor question is yes, I would also recommend that you rewind a couple steps and remove the cursor and move away from using row-by-row processing. 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • I try to avoid using cursors when I can, mainly because I lack any reasonable experience using them.

    I see what you're saying and it makes sense. I think what I'm going to try to do is just do the updates and then do a complete insert from the other tables. After this is all done, I'll go through and delete any duplicates, keeping the older ones as those will be the records containing the combined information from all the sources.

    Thanks for the help, it's much appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply