August 6, 2007 at 2:31 pm
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
August 6, 2007 at 2:50 pm
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
August 7, 2007 at 1:47 pm
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.
August 7, 2007 at 2:12 pm
SQL Server doesn't use IF...THEN. Just IF/ELSE IF. However, THEN is used with CASE.
August 7, 2007 at 5:00 pm
True, I goofed with the THEN, I sometimes put it in as a line comment to keep things straight in my mind.
August 8, 2007 at 12:04 pm
Is this a legal condition?
WHEN CUSTOMERNUMBER = CAST(SUBSTRING(Account_Number, 2, 8) AS int)
August 8, 2007 at 12:34 pm
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.
August 8, 2007 at 1:49 pm
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