April 19, 2009 at 3:59 pm
Hi,
Using SQL SERVER , T-SQL Language
There is a table called retailer which is always up to date containing a list of all the valid retailers.
The data comes in text format
It is being loaded into a temporary table initially and it must be transferred to the holding table, all the rows in the temporary table are flagged 'N' i.e. not been loaded
If they are loaded successfully into the table then they are flagged 'Y' else 'R' rejected.
Before that all the 'N' flagged records are to be checked for valid data like any nulls
Then to load the data into a holding table
Using a script containing stored procedure in which
Cursor is being used for row by row fetching of data
After a row is fetched it will be checked for valid,
i.e whether the retailer id is valid or not,customer name is null, or id is null
all these checks are done inside the cursor after the first row is fetched.
an example of the checking
-- Fail if Retailer does not exist
SELECT RETA_SK
FROM RETA_RETAILER RETA1
INNER JOIN DERM_DEALER_RETAIL_MANAGER DERM1
ON RETA1.DERM_SK = DERM1.DERM_SK
WHERE RETA1.RETAILER_ID = @RETAILER_ID
AND COUN1.COUNTRY_CODE = 'SE'
IF NOT EXISTS (SELECT RETA1.RETA_SK
FROM RETA_RETAILER RETA1
INNER JOIN DERM_DEALER_RETAIL_MANAGER DERM1
ON RETA1.DERM_SK = DERM1.DERM_SK
INNER JOIN COUN_COUNTRY COUN1
ON DERM1.COUN_COUNTRY_CODE = COUN1.COUNTRY_CODE
WHERE RETA1.RETAILER_ID = @RETAILER_ID
AND COUN1.COUNTRY_CODE = 'SE'
)
BEGIN
INSERT INTO ERLO_ERROR_LOOKUP
(CUSTOMER_ID,
FINANCE_AGREEMENT_NUMBER,
REASON,
LOADED_DATETIME
)
VALUES (@CUSTOMER_ID,
@AGREEMENT_NUMBER,
'RETAILER ID ('+CONVERT(VARCHAR,@RETAILER_ID) + ') DOES NOT EXIST',
GETDATE()
)
UPDATE LOAD_DATA_TABLE
SET LOADED_INDICATOR = 'R'
WHERE LOAD_ID = @LOAD_ID
SET @ERROR_IND = 1
GOTO FETCH_NEXT_CURSOR_VALUE
END
/*--------------------------------------------------------------------------------------------------------------------------------*/
-- Fail if the customer name and company name are null
IF @CUSTOMER_NAME IS NULL
AND @COMPANY_NAME IS NULL
BEGIN
INSERT INTO ERLO_ERROR_LOOKUP
(CUSTOMER_ID,
FINANCE_AGR_NUMBER,
REASON,
LOADED_DATETIME
)
VALUES (@CUSTOMER_ID,
@AGREEMENT_NUMBER,
'NO CUSTOMER NAME OR COMPANY NAME',
GETDATE()
)
UPDATE LOAD_DATA_TABLE
SET LOADED_INDICATOR = 'R'
WHERE LOAD_ID = @LOAD_ID
SET @ERROR_IND = 1
GOTO FETCH_NEXT_CURSOR_VALUE
END
...
If a row passes all the tests then
Then update the tables to insert the new arrived values.
-- If there hasnt been an error, then load the data
IF @ERROR_IND = 0
BEGIN
-- If the Customer and Agreement already exist, then update the records
IF EXISTS (SELECT CUST1.CUSTOMER_ID,
FIAG1.FIAG_SK
FROM CUST_CUSTOMER CUST1
INNER JOIN CUVE_CUSTOMER_VEHICLE CUVE1
ON CUST1.CUSTOMER_ID = CUVE1.CUSTOMER_ID
INNER JOIN FIAG_FINANCE_AGREEMENT FIAG1
ON CUVE1.CUVE_SK = FIAG1.CUVE_SK
WHERE CUST1.CUSTOMER_ID = @CUSTOMER_ID
AND FIAG1.FIAG_SK = @AGREEMENT_NUMBER
)
BEGIN
-- UPDATE CUST_CUSTOMER
UPDATE CUST_CUSTOMER
SET ADDRESS_LINE_1 = ISNULL(@ADDRESS_LINE_1,ADDRESS_LINE_1),
ADDRESS_LINE_2 = ISNULL(@ADDRESS_LINE_2, ADDRESS_LINE_2),
ADDRESS_LINE_3 = ISNULL(@ADDRESS_LINE_3, ADDRESS_LINE_3),
ADDRESS_LINE_4 = ISNULL(@ADDRESS_LINE_4, ADDRESS_LINE_4),
CITY = ISNULL(@CITY,CITY),
POSTCODE = ISNULL(@POSTCODE,POSTCODE),
TELEPHONE_DAY_NUMBER = ISNULL(@DAYTIME_NUMBER, TELEPHONE_DAY_NUMBER),
TELEPHONE_EVENING_NUMBER =ISNULL( @EVENING_NUMBER, TELEPHONE_EVENING_NUMBER),
TELEPHONE_MOBILE_NUMBER = ISNULL(@MOBILE_NUMBER, TELEPHONE_MOBILE_NUMBER),
PREFERRED_TELEPHONE_CONTACT = ISNULL(@PREFERRED_NUMBER, PREFERRED_TELEPHONE_CONTACT),
EMAIL = ISNULL(@EMAIL, EMAIL),
USER_LAST_UPDATED_BY = 1,
LAST_UPDATED_DATE_TIME = GETDATE()
WHERE CUSTOMER_ID = @CUSTOMER_ID
Now this process is taking many hours
Kindly help me out and suggest if you spot any problems in the code or if you can suggest something which would reduce the data loading time that would be great.
April 19, 2009 at 6:23 pm
Assuming I read your post correctly and more importantly what the Cursor was utilized for, you might want to try the following to eliminate the Row by Row cursor processing
CREATE TABLE #Table1 --table into which data is first imported
(CUSTOMER_ID INT,
FINANCE_AGR_NUMBER INT,
CUSTOMER_NAME VARCHAR(50),
COMPANY_NAME VARCHAR(50),
LOADED CHAR(1))
GO
INSERT INTO #Table1
SELECT 1,1001,'First Cust','Comp 1','N' UNION ALL
SELECT 2,2000,NULL,'Comp 2','N' UNION ALL
SELECT 3,1001,'Next',NULL,'N' UNION ALL
SELECT 4,2000,'Second','Comp 3','N' UNION ALL
SELECT 5,3000,NULL,NULL,'N'
GO
/*Identify invalid data and write to another file possible use to correct entries*/
CREATE TABLE #ERLO_ERROR_LOOKUP
(CUSTOMER_ID INT,
FINANCE_AGR_NUMBER INT,
REASON VARCHAR(50),
LOADED_DATETIME DATETIME)
INSERT INTO #ERLO_ERROR_LOOKUP (CUSTOMER_ID,FINANCE_AGR_NUMBER,REASON,LOADED_DATETIME)
SELECT CUSTOMER_ID,FINANCE_AGR_NUMBER,'NO CUSTOMER NAME OR COMPANY NAME',GETDATE()
FROM #TABLE1 WHERE CUSTOMER_NAME IS NULL OR COMPANY_NAME IS NULL
SELECT * FROM #ERLO_ERROR_LOOKUP --Used for testing purposes only
/* this will have to be expanded to check if valid Finance_agr value and most likely other values not mention in the post*/
CREATE TABLE #GoodData
(CUSTOMER_ID INT,
FINANCE_AGR_NUMBER INT,
CUSTOMER_NAME VARCHAR(50),
COMPANY_NAME VARCHAR(50))
INSERT INTO #GoodData (CUSTOMER_ID,FINANCE_AGR_NUMBER,CUSTOMER_NAME,COMPANY_NAME)
SELECT CUSTOMER_ID,FINANCE_AGR_NUMBER,CUSTOMER_NAME,COMPANY_NAME
FROM #TABLE1 WHERE CUSTOMER_NAME IS NOT NULL AND COMPANY_NAME IS NOT NULL
SELECT * FROM #GoodData
--Temp tables used for testing only
DROP TABLE #TABLE1
DROP TABLE #ERLO_ERROR_LOOKUP
DROP TABLE #GoodData
Hope this get you started on removing the use of a cursor
April 20, 2009 at 2:54 am
I CANT THANK YOU enough for that valuable post,
BUT
kindly clear my doubt
i understand these parts
CREATE TABLE #Table1 --table into which data is first imported
(CUSTOMER_ID INT,
FINANCE_AGR_NUMBER INT,
CUSTOMER_NAME VARCHAR(50),
COMPANY_NAME VARCHAR(50),
LOADED CHAR(1))
GO
INSERT INTO #Table1
SELECT 1,1001,'First Cust','Comp 1','N' UNION ALL
SELECT 2,2000,NULL,'Comp 2','N' UNION ALL
SELECT 3,1001,'Next',NULL,'N' UNION ALL
SELECT 4,2000,'Second','Comp 3','N' UNION ALL
SELECT 5,3000,NULL,NULL,'N'
GO
This your referring to the loading of the data initially in text format into a temporary table.
Cool, then this next part
/*Identify invalid data and write to another file possible use to correct entries*/
This part is where i am using the cursor i mean to fetch single record from the temporary table and check whether all of its values are checking out.
What i am saying is the entire table cannot be checked at once for invalid data it needs to be done row by row right so thats why fetch first row using cursor check for invalid data then if valid then load it into table else load it into error table.
I am pretty sure you were trying to explain me a way around this but i am not getting how
CREATE TABLE #ERLO_ERROR_LOOKUP
(CUSTOMER_ID INT,
FINANCE_AGR_NUMBER INT,
REASON VARCHAR(50),
LOADED_DATETIME DATETIME)
INSERT INTO #ERLO_ERROR_LOOKUP
(CUSTOMER_ID,
FINANCE_AGR_NUMBER,
REASON,
LOADED_DATETIME)
SELECT CUSTOMER_ID,
FINANCE_AGR_NUMBER,
'NO CUSTOMER NAME OR COMPANY NAME',
GETDATE()
FROM #TABLE1
WHERE CUSTOMER_NAME IS NULL
OR COMPANY_NAME IS NULL
Are you saying here to make a bulk loading i mean if i am understanding it correctly then
your explaining instead of using a cursor for checking for errors in each row by fetching each row for which it will probably do a full table scan
you explaining
create a table to hold all the errors
insert into the table all the rows
which are invalid like you said where customer_name is null or company_name is null ....
so your saying to check for all the errors in the temporary table and load all the rows into the error table.
like a bulk loading of data the rest of the data is valid and there will be another table into which it will be put into.
SELECT * FROM #ERLO_ERROR_LOOKUP --Used for testing purposes only
/* this will have to be expanded to check if valid Finance_agr value and most likely other values not mention in the post*/
CREATE TABLE #GoodData
(CUSTOMER_ID INT,
FINANCE_AGR_NUMBER INT,
CUSTOMER_NAME VARCHAR(50),
COMPANY_NAME VARCHAR(50))
INSERT INTO #GoodData (CUSTOMER_ID,FINANCE_AGR_NUMBER,CUSTOMER_NAME,COMPANY_NAME)
SELECT CUSTOMER_ID,FINANCE_AGR_NUMBER,CUSTOMER_NAME,COMPANY_NAME
FROM #TABLE1 WHERE CUSTOMER_NAME IS NOT NULL AND COMPANY_NAME IS NOT NULL
SELECT * FROM #GoodData
I guess i understood now what your trying to say and will implement this beautiful idea thank you, correct me if i got any mis understanding reg the idea.
Thank you again.
April 20, 2009 at 4:47 am
thank you for that but nowe i remember that the cursor is actually used to insert/update data in individual tables.
Like say as i was saying data is currently flagged 'N' all the data and if the data is validated then we are left with all valid data . i.e. the data is now to be inserted or updated in different tables
Ex scenario
customer 1 in customer table has changed address and renwed his financial aggreement
then in the initial part after validating his record in the bulk validation that all the supplied details are fine then we need to fetch his record from the rest of the left data or from the good_table where it holds valid data and then
update customer table,finace_table
set address=@new_address
set finance_agg=@renwed_fin_aggr
where customer_id=@customer_id
for this eithera cursor or a while loop needs to be used to iterate through the good_table and fetch row after row and update them in their respective tables.
Do you think this fashion of while loop might have any performance improvement over cursor.
-- Declare Variables
declare @current_id int;
declare @next_row_id int;
declare @customer_no nvarchar(25);
declare @customer_name nvarchar(50);
declare @err_messg nvarchar(20);
declare @loop_c int;
BEGIN
--Initialize Variables
SELECT @loop_c=1;
SELECT MIN (rowid)
INTO @next_row_id
FROM table_temp
-- Checking if the data is existing
IF ISNULL(@next_row_id,0)=0
THEN
BEGIN
SELECT 'empty_row_id'
RETURN
END
-- Retrieve the first row.
SELECT @current_id = MIN(row_id)
FROM table_test
WHERE rowid=@next_row_id
--Loop
WHILE @loop_c=1
BEGIN
--row by row fetch
SELECT @next_row_id=NULL
-- reset variables
-- Get the next row id
SELECT @next_row_id=MIN(rowid)
from table_test
where rowid>@next_row_id
-- is it valid
IF ISNULL(@next_row_id,0)=0
BEGIN
BREAK
END
-- Get the next row from Table
SELECT @current_row_id=row_id
@customer_no=customer_number,
@customer_name=customer_name,
FROM table_test
WHERE row_id=@next_row_id
END
RETURN
I dont know if this will improve any performance considerably overthe cursor.
Kindly give your valuable feedback.
April 20, 2009 at 5:31 am
john_chrome (4/20/2009)
Do you think this fashion of while loop might have any performance improvement over cursor.
Minimal, if at all.
Why can't you do the identifying of bad records and updating of tables as set-based operations rather than row by row?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2009 at 5:53 am
It is a very common misconception that WHILE ... LOOP over a collection of records is better than a cursor. The costs associated with setting up/managing a cursor or while are negligible compared to the actual iterative processing itself.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2009 at 3:19 pm
john_chrome
Step 1. Import all the data without any additional processing this is what I imitated in creating the temporary table #Table1.
Step 2. Identify all rows in #Table1 that have either a blank customer or company name and write them to the temp table #ERLO_ERROR_LOOKUP -- In your case I will assume that this is a permanent table in your database, if not it will soon be so.
Step 3.
You can and should update various tables, using set based T-SQL you do NOT have to do it row by agonizing row (using a cursor). Here is an example of using the previous code I posted to update the address data in your CUST_CUSTOMER table. (Note that since I had to create all of the tables, load data into them, etc., etc., I included just a few fields, you can expand the code quite easily.
UPDATE CUST_CUSTOMER
SET ADDRESS_LINE_1 = ISNULL(#GoodData.ADDRESS_LINE1,CUST_CUSTOMER.ADDRESS_LINE_1),
ADDRESS_LINE_2 = ISNULL(#GoodData.ADDRESS_LINE2, ADDRESS_LINE_2),
LAST_UPDATED_DATE_TIME = GETDATE()
FROM CUST_CUSTOMER, #GoodData
WHERE CUST_CUSTOMER.CUSTOMER_ID = #GoodData.CUSTOMER_ID
Step 4. I will leave to yourself to identify NEW customers and add their data to the appropriate tables. -- if you have a problem with that please post again, but please, please follow the method of presenting data as stated in the article in my signature block.
April 21, 2009 at 9:55 am
Thank you bit bucket for that valuable post this is what i was thinking
Yes there is a table which takes in all the data like the table 1 in your example.
I clearly understood your first example in which you explained
create a error table which will hold all the invalid data and good table which contain all the valid data
but the data to be validated is such that it isnt just nulls to be checked a lot more needs to be checked
sorry for not posting more information about the code and you needed to imagine but
the order of execution is
check for valid data in the table like checking if a supplied retailer is valid or not
ex
-- Fail if Retailer does not exist
IF NOT EXISTS (SELECT RETA1.RETA_SK
FROM RETA_RETAILER RETA1
INNER JOIN DERM_DEALER_RETAIL_MANAGER DERM1
ON RETA1.DERM_SK = DERM1.DERM_SK
INNER JOIN COUN_COUNTRY COUN1
ON DERM1.COUN_COUNTRY_CODE = COUN1.COUNTRY_CODE
WHERE RETA1.RETAILER_ID = @RETAILER_ID
AND COUN1.COUNTRY_CODE = 'SE'
)
BEGIN
INSERT INTO ERLO_ERROR_LOOKUP
(CUSTOMER_ID,
FINANCE_AGREEMENT_NUMBER,
REASON,
LOADED_DATETIME
)
VALUES (@CUSTOMER_ID,
@AGREEMENT_NUMBER,
'RETAILER ID ('+CONVERT(VARCHAR,@RETAILER_ID) + ') DOES NOT EXIST',
GETDATE()
)
UPDATE SCLD_SWEDEN_CUSTOMER_LOAD_DATA
SET LOADED_INDICATOR = 'R'
WHERE LOAD_ID = @LOAD_ID
SET @ERROR_IND = 1
GOTO FETCH_NEXT_CURSOR_VALUE
END
/*--------------------------------------------------------------------------------------------------------------------------------*/
-- Fail if the customer name and company name are null
IF @CUSTOMER_NAME IS NULL
AND @COMPANY_NAME IS NULL
BEGIN
INSERT INTO ERLO_ERROR_LOOKUP
(CUSTOMER_ID,
FINANCE_AGREEMENT_NUMBER,
REASON,
LOADED_DATETIME
)
VALUES (@CUSTOMER_ID,
@AGREEMENT_NUMBER,
'NO CUSTOMER NAME OR COMPANY NAME',
GETDATE()
)
UPDATE SCLD_SWEDEN_CUSTOMER_LOAD_DATA
SET LOADED_INDICATOR = 'R'
WHERE LOAD_ID = @LOAD_ID
SET @ERROR_IND = 1
GOTO FETCH_NEXT_CURSOR_VALUE
END
and if there are no errors then
-- If there hasnt been an error, then load the data
IF @ERROR_IND = 0
BEGIN
-- If the Customer and Agreement already exist, then update the records
IF EXISTS (SELECT CUST1.CUSTOMER_ID,
FIAG1.FIAG_SK
FROM CUST_CUSTOMER CUST1
INNER JOIN CUVE_CUSTOMER_VEHICLE CUVE1
ON CUST1.CUSTOMER_ID = CUVE1.CUSTOMER_ID
INNER JOIN FIAG_FINANCE_AGREEMENT FIAG1
ON CUVE1.CUVE_SK = FIAG1.CUVE_SK
WHERE CUST1.CUSTOMER_ID = @CUSTOMER_ID
AND FIAG1.FIAG_SK = @AGREEMENT_NUMBER
)
BEGIN
-- UPDATE CUST_CUSTOMER
UPDATE CUST_CUSTOMER
SET ADDRESS_LINE_1 = ISNULL(@ADDRESS_LINE_1,ADDRESS_LINE_1),
ADDRESS_LINE_2 = ISNULL(@ADDRESS_LINE_2, ADDRESS_LINE_2),
ADDRESS_LINE_3 = ISNULL(@ADDRESS_LINE_3, ADDRESS_LINE_3),
ADDRESS_LINE_4 = ISNULL(@ADDRESS_LINE_4, ADDRESS_LINE_4),
CITY = ISNULL(@CITY,CITY),
POSTCODE = ISNULL(@POSTCODE,POSTCODE),
TELEPHONE_DAY_NUMBER = ISNULL(@DAYTIME_NUMBER, TELEPHONE_DAY_NUMBER),
TELEPHONE_EVENING_NUMBER =ISNULL( @EVENING_NUMBER, TELEPHONE_EVENING_NUMBER),
TELEPHONE_MOBILE_NUMBER = ISNULL(@MOBILE_NUMBER, TELEPHONE_MOBILE_NUMBER),
PREFERRED_TELEPHONE_CONTACT = ISNULL(@PREFERRED_NUMBER, PREFERRED_TELEPHONE_CONTACT),
EMAIL = ISNULL(@EMAIL, EMAIL),
USER_LAST_UPDATED_BY = 1,
LAST_UPDATED_DATE_TIME = GETDATE()
WHERE CUSTOMER_ID = @CUSTOMER_ID
-- Possibly came in with incorrect vehicle data, therefore need to check that the velo_sk is correct.
DECLARE @CUVE_SK_FOR_UPDATE INT
SET @CUVE_SK_FOR_UPDATE = (SELECT A.CUVE_SK
FROM CUVE_CUSTOMER_VEHICLE A
INNER JOIN FIAG_FINANCE_AGREEMENT B
ON A.CUVE_SK = B.CUVE_SK
WHERE A.CUSTOMER_ID = @CUSTOMER_ID
AND B.FIAG_SK = @AGREEMENT_NUMBER
)
UPDATE CUVE_CUSTOMER_VEHICLE
SET VIN = ISNULL(@VIN,VIN),
REGISTRATION_NO = ISNULL(@REG_PLATE, REGISTRATION_NO),
NEW_USED_INDICATOR = ISNULL(@NEW_USED_INDICATOR, NEW_USED_INDICATOR),
PURCHASED_DATE = ISNULL(CONVERT(DATETIME,@PURCHASE_DATE), PURCHASED_DATE),
VELO_SK = ISNULL(@CUSTOMER_VELO_SK,VELO_SK),
USER_LAST_UPDATED_BY = 1,
LAST_UPDATED_DATE_TIME = GETDATE(),
USER_SELLING_SK = ISNULL(@SALESPERSON_USER_SK, USER_SELLING_SK)
WHERE CUVE_SK = @CUVE_SK_FOR_UPDATE
Kindly give me your thoughts on this.
April 21, 2009 at 11:26 am
John,
Your performance problem is that you are performing your data load in a row by row method. Your process needs to be converted to be a set-based process.
There are several people here that can assist and guide you through that process. However, many are not helping you because you have not provided adequate sample data and rules.
An earlier poster suggested you check out http://www.sqlservercentral.com/articles/Best+Practices/61537/.Following these guidelines will help these people help you faster.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 21, 2009 at 3:32 pm
John_Chrome
but the data to be validated is such that it isnt just nulls to be checked a lot more needs to be checked
and
check for valid data in the table like checking if a supplied retailer is valid or not
Checking for a valid retailer - can use a set based solution... Changing my approach a little, that is setting the column LOADED in the Bulkin table when a business rule is violated. I increased the size of that column from CHAR(1) to VARCHAR(5) and let me explain why. If I were you I would create codes such as
RN - used when a customer and/or company name is null
RR - used when a retailer_id is not valid
NEW - used when a new customer_id is added via the imported data.
The advantages to this, as I see it is that you can pattern your code such that different business rules can be enforced and the proper text explanation written to the ERLO_ERROR_LOOKUP table. I will modify the steps in the total procedure by modify my original Step 2 and adding Step 2a and 2b.
Step 2. Identify all rows in #Table1 that have either a blank customer or company and set the LOADED value to 'RN'.
Step 2a. Identify all rows which do not have a valid RETAILER_ID value,
update BulkIn column LOADED with the value of 'RR"
Step 2b. Write all rows from table BulkIn where LOADED value is LIKE 'R%' to the ERLO_ERROR_LOOKUP table with the corresponding explanation.
Finally the code to identify an invalid RETAILER_ID
CREATE TABLE #BRetailer
( CUSTOMER_ID INT)
INSERT INTO #BRetailer
SELECT CUSTOMER_ID FROM Dbo.BulkIn
LEFT OUTER JOIN dbo.RETA_RETAILER
ON Bulkin.Retailer_Id = RETA_RETAILER.RETAILER_ID
WHERE RETA_RETAILER.RETAILER_ID IS NULL
UPDATE BulkIn SET LOADED = 'RR'
FROM #BRetailer
WHERE #BRetailer.CUSTOMER_ID = BulkIn.CUSTOMER_ID
DROP TABLE #BRetailer
Finally changing what data is written to the temporary table #GoodData to:
CREATE TABLE #GoodData
(CUSTOMER_ID INT,
FINANCE_AGR_NUMBER INT,
CUSTOMER_NAME VARCHAR(50),
COMPANY_NAME VARCHAR(50))
INSERT INTO #GoodData (CUSTOMER_ID,FINANCE_AGR_NUMBER,CUSTOMER_NAME,COMPANY_NAME)
SELECT CUSTOMER_ID,FINANCE_AGR_NUMBER,CUSTOMER_NAME,COMPANY_NAME
FROM #TABLE1 WHERE LOADED = like 'N%'
Please note that you can add additional business ruled quite easily, or delete a business rule without materially effecting your procedure all without the use of a CURSOR.
April 23, 2009 at 9:39 am
HEY THANKS A LOT
I was helped out finally but i still have a potential problem of implementing it i am using cases but currently i am able to figure out to use cases only for 2 types of errors
This is the script kindly look at it and if possible advise as to how i can modify it so as to allocate more errors
SELECT LOAD_ID, CASE WHEN T.RETA1_RETAILER_ID is null THEN 1 ELSE 2 END as ErrorType
INTO #INCORRECT_ROW
FROM LOAD_DATA_TABLE LDT
left join
(SELECT RETA1.RETAILER_ID as RETA1_RETAILER_ID
FROM RETA_RETAILER RETA1
INNER JOIN DERM_DEALER_RETAIL_MANAGER DERM1
ON RETA1.DERM_SK = DERM1.DERM_SK
INNER JOIN COUN_COUNTRY COUN1
ON DERM1.COUN_COUNTRY_CODE = COUN1.COUNTRY_CODE
WHERE COUN1.COUNTRY_CODE = 'SE'
) T ON T.RETA1_RETAILER_ID = LDT.RETAILER_ID
WHERE T.RETA1_RETAILER_ID is null OR (CUSTOMER_NAME IS NULL and COMPANY_NAME IS NULL)
INSERT INTO ERLO_ERROR_LOOKUP
(CUSTOMER_ID,
FINANCE_AGREEMENT_NUMBER,
REASON,
LOADED_DATETIME
)
SELECT LDT.CUSTOMER_ID, LDT.AGREEMENT_NUMBER,
CASE IR.ErrorType
WHEN 1 THEN 'RETAILER ID ('+CONVERT(VARCHAR, LDT.RETAILER_ID) + ') DOES NOT EXIST'
WHEN 2 THEN 'NO CUSTOMER NAME OR COMPANY NAME'
END,
GETDATE()
FROM LOAD_DATA_TABLE LDT
inner join #INCORRECT_ROW IR on IR.LOAD_ID = LDT.LOAD_ID
UPDATE LOAD_DATA_TABLE
SET LOADED_INDICATOR =
CASE WHEN LOAD_ID in (SELECT LOAD_ID FROM #INCORRECT_ROW) THEN 'R' ELSE 'Y' END
But now i want to accomodate more than the 2 checks currently
retailer_id is null
and
(customer_id, company_id is null)
like
vehicle make and model are null so on
I mean i am confused how to accomodate these into the same check.
ELECT LOAD_ID,
CASE WHEN T.RETAILER_ID IS NULL
THEN 0
-- ELSE 1-- THIS WILL BE FOR CUST_ID,COMP_ID NULL
WHEN T.VELO_SK IS NULL THEN 2
-- ELSE 3--VEH_MODEL,MAKE NULL
WHEN T.FIAG_SK IS NULL THEN 4
ELSE 5
END AS ERROR_TYPE
INTO #INCORRECT_ROW
FROM LOAD_DATA_TABLE LDT
LEFT JOIN
(SELECT R.RETAILER_ID,V.VELO_SK,V.MAKE_ID,V.MODEL_ID,F.FIAG_SK
FROM RETA_RETAILER R,
CUVE_CUSTOMER_VEHICLE C,
FIAG_FINANCE_AGREEMENT F,
VELO_VEHICLE_LOOKUP V
WHERE R.RETA_SK=C.RETA_SELLING_SK
AND C.CUVE_SK=F.CUVE_SK
AND C.VELO_SK=V.VELO_SK)T
ON (T.RETAILER_ID = LDT.RETAILER_ID
OR
T.MAKE_ID=LDT.VEHICLE_MAKE
OR
T.MODEL_ID=LDT.VEHICLE_MODEL
OR
T.FIAG_SK=LDT.FINANCIAL_SCHEME_CODE)
WHERE (T.RETAILER_ID IS NULL
/*OR (T.CUSTOMER_NAME IS NULL
OR
LDT.COMPANY_NAME IS NULL)*/
OR T.VELO_SK IS NULL
OR T.FIAG_SK IS NULL)
Or
SELECT LOAD_ID,
CASE WHEN T.RETAILER_ID IS NULL
THEN 0
-- ELSE 1-- THIS WILL BE FOR CUST_ID,COMP_ID NULL
WHEN T.VELO_SK IS NULL THEN 2
-- ELSE 3--VEH_MODEL,MAKE NULL
WHEN T.FIAG_SK IS NULL THEN 4
ELSE 5
END AS ERROR_TYPE
INTO #INCORRECT_ROW
FROM LOAD_DATA_TABLE LDT
LEFT JOIN
(SELECT R.RETAILER_ID,V.VELO_SK,V.MAKE_ID,V.MODEL_ID,F.FIAG_SK
FROM RETA_RETAILER R,
CUVE_CUSTOMER_VEHICLE C,
FIAG_FINANCE_AGREEMENT F,
VELO_VEHICLE_LOOKUP V
WHERE R.RETA_SK=C.RETA_SELLING_SK
AND C.CUVE_SK=F.CUVE_SK
AND C.VELO_SK=V.VELO_SK)T
ON (T.RETAILER_ID = LDT.RETAILER_ID
OR
T.MAKE_ID=LDT.VEHICLE_MAKE
OR
T.MODEL_ID=LDT.VEHICLE_MODEL
OR
T.FIAG_SK=LDT.FINANCIAL_SCHEME_CODE)
WHERE (T.RETAILER_ID IS NULL
/*OR (T.CUSTOMER_NAME IS NULL
OR
LDT.COMPANY_NAME IS NULL)*/
OR T.VELO_SK IS NULL
OR T.FIAG_SK IS NULL)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply