none
Data Loading Cursors or While loop for better Performance, Please reply RRS feed

  • Question

  • The steps involved in the current data loading

    • The data comes from external sources gets cleaned and held in temporary tables now to load it into an holding database.
    • Scripts like stored procedures in which a cursor is being used to
    • Fetch all the data from the temporary table into the cursor
    • Then fetch each row from the cursor and check for consistency of the data like is the column null...
    • Then sending the data into holding database.
    • This is taking a lot of hours to load the data currently.
    • It is resource hungry.
    My idea was to place a clustered index on the primary key of the importing table and to replace the cursor with a while loop where something like this happens
    Create
    
     Procedure
    
     test
    as
    
    
    
    -- 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)
                             @customer_no      = customernumber
                             @customer_name = customername
                 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)
                                          @customer_no=customer_number,
                                          @customer_name=customer_name,
                              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
                                 FROM
    
     table_test
                               WHERE
    
     row_id=@next_row_id
                       END
    
    
                 RETURN
    
    
    

    Is this an effective solution.

    Kindly suggest ways which you may think are better for improving performance

    Thank you for your valuable time
    • Edited by robin_blue Friday, April 17, 2009 10:19 AM
    Thursday, April 16, 2009 10:02 PM

Answers

  • You just need second LEFT JOIN

    SELECT LOAD_ID,
    		CASE
    			WHEN T.RETA1_RETAILER_ID is null THEN 1
    			WHEN CUSTOMER_NAME IS NULL and COMPANY_NAME IS NULL THEN 2
    			ELSE 3 -- there is no matching combination of MODEL_ID and MAKE_ID in the VELO_VEHICLE_LOOKUP with COUNTRY_CODE = 'SE'
    		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
    		LEFT JOIN
    			VELO_VEHICLE_LOOKUP V
    				ON V.MODEL_ID = LDT.MODEL_ID AND V.MAKE_ID = LDT.MAKE_ID AND V.COUNTRY_CODE = 'SE'
    	WHERE T.RETA1_RETAILER_ID IS NULL
    		OR (CUSTOMER_NAME IS NULL and COMPANY_NAME IS NULL)
    		OR V.VELO_SK IS NULL
     

    As I said before only one type of error will be recorded, since CASE gives only result for the first positive comparing per row. If you want to change order of errors' detection, just reorder comparings in CASE construction. If you want to record all error types for each row let me know.
    • Marked as answer by robin_blue Thursday, April 30, 2009 4:06 PM
    Thursday, April 23, 2009 4:08 PM

All replies

  • I'd recommend scrapping it, and trying to write a query which does it all. Your query can involve CASE statements to test for NULLs, to hopefully produce the logic you need.

    If you really need cursor-style functionality, then consider using SSIS, as you will be able to fetch all the data into the SSIS Buffers and handle it there. This could be a much better solution.

    Rob

    http://msmvps.com/blogs/robfarley - http://www.lobsterpot.com.au - Adelaide SQL UG President - Did this help? Mark it as an answer!
    Friday, April 17, 2009 4:42 AM
  • Am I missing something here?  This procedure does not do anything?
    RBarryYoung
    Friday, April 17, 2009 5:22 AM
  • I'm assuming that he's trying to give a rough impression, and has left out a few INSERT statements...

    Rob
    http://msmvps.com/blogs/robfarley - http://www.lobsterpot.com.au - Adelaide SQL UG President - Did this help? Mark it as an answer!
    Friday, April 17, 2009 6:04 AM
  • Yes It is for giving a rough idea, sorry cant scrap the entire procedure with cursors which is existing now, it would be really helpful if someone can give a answer with more detail.

    Thank you for the suggestions.
    Friday, April 17, 2009 8:41 AM
  • Using a while loop will probably be worse than a cursor. The best way to improve things would be to write a query to do it without any kind of cursor/while loop. Sorry this probably isn't what you want to hear. Rob
    http://msmvps.com/blogs/robfarley - http://www.lobsterpot.com.au - Adelaide SQL UG President - Did this help? Mark it as an answer!
    Friday, April 17, 2009 10:39 AM
  • (Assuming that you really have to use some form of loop)

     

    From what you've shown it's not an effective solution. You basically run through entire table to find every next rowid with statement like:

    SELECT @next_row_id=MIN(rowid) FROM table_test WHERE rowid>@next_row_id

    And what if it's a very big table?

    Obviously a cursor would fit better here.

     

    Also, with this style of programming you _must_ implement indexes for all columns like rowid.

    Friday, April 17, 2009 11:28 AM
  • What i am trying to explain is

    rowid is a primary key column in the temporary table from which data will be loaded into the cursor and then into the holding table after it clears some pre set rules.

    now it wont run through the entire table the

    clustured index actually holds all the primary key values from the source table and when we say min(rowid) it just fetches the 1st record from the index and uses that result to fetch the row from the source. it wont scan the table when your actually specifying the rowid it will straight away go to the selected row where as in a cursor it fetches the entire data from the source into the cursor then it starts fetching the data

    unlike a cursor using a clustured index would make the no of trips to the source as the no of rows but it will be faster as you will be specifying the actual row from which to fetch the data.

    Kindly if any more ideas give me some head start.

    Thanks
    Friday, April 17, 2009 2:34 PM
  • I still doubt you'll get anywhere near the performance you could if you avoid iterative solutions completely. Rob
    http://msmvps.com/blogs/robfarley - http://www.lobsterpot.com.au - Adelaide SQL UG President - Did this help? Mark it as an answer!
    Friday, April 17, 2009 4:00 PM
  • If anyone else can see a solution please reply.
    Saturday, April 18, 2009 5:54 PM
  • now it wont run through the entire table the

    clustured index actually holds all the primary key values from the source table and when we say min(rowid) it just fetches the 1st record from the index and uses that result to fetch the row from the source
    Not just the first row, but the first row satisfying your WHERE clause. And that search will be performed for every rowid. Clustered index or not, it takes time. So I'm quite sure you will not find effective manually-implemented replacement for CURSOR.

    Second, it's not CURSOR itself that takes a lot of processing power. It's SELECTs, INSERTs, UPDATEs and DELETEs inside iteration. That's because SQL Server is designed to "think" before executing statements, not to start execute them as fast as possible. Furthermore, to actually perform INSERT, UPDATE or DELETE, SQL Server makes plenty of calls. Create buffer here, add to transaction log there, split pages, check for constraints, write data to disk. Let alone updating non-clustered indexes and firing triggers if they exist. SQL Server makes so many calls that sometimes number of those calls is only thing considered, no matter did you affect one row or ten.

    I once faced analogous task: load data into temp db, clean it, and insert it into database for reporting system, every hour. First I implemented cursors and importing procedure took about 5 minutes. Then and I got rid of cursors and execution time was reduced to 3 seconds. That is, one hundred times faster.

    So, what you must check out first to be able to remove iterations. You can use:

    1. INSERT from SELECT. From the most complex SELECT imaginable. With GROUP BY, sub queries, whatever
    2. UPDATE in / DELETE from joined tables (helped most in my case, for some reason I learned such simple syntax later than cursors)
    3. Sub queries in UPDATE's SET clause
    4. CASE statement in lots of clauses
    5. Various tricks, like ROW_NUMBER() OVER()

    When nothing else helps you can create function from your special code and use it in your many-rows-affecting statements. I don't know why, but this can work faster than an iteration with special code inside.
    Sunday, April 19, 2009 5:33 AM
  • Thank you very much Mikhail for that detailed answer

    I will explain the steps in the procedure

    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 part 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.

    Mikhail any advice, please.

    Sunday, April 19, 2009 7:18 PM
  • mikhail could u give me any reply.
    Monday, April 20, 2009 8:24 AM
  • From what I see this code can be easily written without iteration.

    First of all, using GOTO is not a good idea in any language, including T-SQL. Also @ERROR_IND is not needed from what I see in this piece of code.

    Instead of

    FETCH_NEXT_CURSOR_VALUE:
    SET @ERROR_IND = 0
    ...
    IF
        BEGIN
            ...
            SET @ERROR_IND = 1;
            GOTO FETCH_NEXT_CURSOR_VALUE;
        END;
    IF
        BEGIN
            ...
            SET @ERROR_IND = 1;
            GOTO FETCH_NEXT_CURSOR_VALUE;
        END;
    IF @ERROR_IND = 0
        BEGIN
            ...
        END;

    you could write

    IF
        BEGIN
            ...
        END
    ELSE IF
        BEGIN
            ...
        END
    ELSE
        BEGIN
            ...
        END;

    Our main task is to call every iterated INSERT and UPDATE only once. Right now we have 2 INSERTs and 3 UPDATEs divided between 3 blocks.

    IF
        BEGIN
            INSERT ERLO_ERROR_LOOKUP
            UPDATE LOAD_DATA_TABLE
        END
    ELSE IF
        BEGIN
            INSERT ERLO_ERROR_LOOKUP
            UPDATE LOAD_DATA_TABLE
        END
    ELSE
        BEGIN
            UPDATE CUST_CUSTOMER
        END;

    All INSERTs and UPDATEs can be written as many-rows-affecting statements with LOAD_ID as a key. We need two temporary tables to hold values satisfying condition of first and second branches.

    The next code prototype is not requiring cursor anymore:

    SELECT LOAD_ID
        INTO #INCORRECT_RETAILER
        FROM LOAD_DATA_TABLE
        WHERE RETAILER_ID not in
        (SELECT 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'
                            )

    SELECT LOAD_ID
        INTO #INCORRECT_CUSTOMER
        FROM LOAD_DATA_TABLE
        WHERE CUSTOMER_NAME IS NULL and COMPANY_NAME  IS NULL

    INSERT INTO ERLO_ERROR_LOOKUP
                             (CUSTOMER_ID,
                              FINANCE_AGREEMENT_NUMBER,
                              REASON,
                              LOADED_DATETIME
                             )
        SELECT CUSTOMER_ID, AGREEMENT_NUMBER,
            'RETAILER ID ('+CONVERT(VARCHAR, RETAILER_ID) + ') DOES NOT EXIST', GETDATE()
        FROM LOAD_DATA_TABLE
        WHERE LOAD_ID in (SELECT LOAD_ID FROM #INCORRECT_RETAILER)

    INSERT INTO ERLO_ERROR_LOOKUP
                             (CUSTOMER_ID,
                              FINANCE_AGR_NUMBER,
                              REASON,
                              LOADED_DATETIME
                             )
        SELECT CUSTOMER_ID, AGREEMENT_NUMBER,
            'NO CUSTOMER NAME OR COMPANY NAME', GETDATE()
        FROM LOAD_DATA_TABLE
        WHERE LOAD_ID in
            (SELECT LOAD_ID FROM #INCORRECT_CUSTOMER
                EXCEPT
            SELECT LOAD_ID FROM #INCORRECT_RETAILER)

    UPDATE LOAD_DATA_TABLE
        SET LOADED_INDICATOR = 'R'
        WHERE LOAD_ID in
            (SELECT LOAD_ID FROM #INCORRECT_RETAILER
                UNION
            SELECT LOAD_ID FROM #INCORRECT_CUSTOMER)

    UPDATE CUST_CUSTOMER
                        SET ADDRESS_LINE_1 = ISNULL(LDT.ADDRESS_LINE_1, ADDRESS_LINE_1),
                            ADDRESS_LINE_2 = ISNULL(LDT.ADDRESS_LINE_2, ADDRESS_LINE_2),
                            ADDRESS_LINE_3 = ISNULL(LDT.ADDRESS_LINE_3, ADDRESS_LINE_3),
                            ADDRESS_LINE_4 = ISNULL(LDT.ADDRESS_LINE_4, ADDRESS_LINE_4),
                            CITY =  ISNULL(LDT.CITY,CITY),
                            POSTCODE =  ISNULL(LDT.POSTCODE,POSTCODE),
                            TELEPHONE_DAY_NUMBER = ISNULL(LDT.DAYTIME_NUMBER, TELEPHONE_DAY_NUMBER),
                            TELEPHONE_EVENING_NUMBER =ISNULL(LDT.EVENING_NUMBER, TELEPHONE_EVENING_NUMBER),
                            TELEPHONE_MOBILE_NUMBER = ISNULL(LDT.MOBILE_NUMBER, TELEPHONE_MOBILE_NUMBER),
                            PREFERRED_TELEPHONE_CONTACT = ISNULL(LDT.PREFERRED_NUMBER, PREFERRED_TELEPHONE_CONTACT),
                            EMAIL = ISNULL(LDT.EMAIL, EMAIL),
                            USER_LAST_UPDATED_BY = 1,
                            LAST_UPDATED_DATE_TIME = GETDATE()
        FROM
            CUST_CUSTOMER
            inner join LOAD_DATA_TABLE LDT
                on LDT.CUSTOMER_ID = CUST_CUSTOMER.CUSTOMER_ID
                and LDT.LOAD_ID not in
                (SELECT LOAD_ID FROM #INCORRECT_RETAILER
                    UNION
                SELECT LOAD_ID FROM #INCORRECT_CUSTOMER)

    I think that you will do also:

    UPDATE LOAD_DATA_TABLE
        SET LOADED_INDICATOR = 'Y'
        WHERE LOAD_ID not in
            (SELECT LOAD_ID FROM #INCORRECT_RETAILER
                UNION
            SELECT LOAD_ID FROM #INCORRECT_CUSTOMER)

    Sure, this code has some errors because I can't check it without database and your other code. But I hope you will get the main idea.

    A few notes:

    1. EXCEPT keyword is for SQL 2005 and later

    instead of

        WHERE LOAD_ID in
            (SELECT LOAD_ID FROM #INCORRECT_CUSTOMER
                EXCEPT
            SELECT LOAD_ID FROM #INCORRECT_RETAILER)

    you can write

        WHERE LOAD_ID in (SELECT LOAD_ID FROM #INCORRECT_CUSTOMER)
            and LOAD_ID not in (SELECT LOAD_ID FROM #INCORRECT_RETAILER)

    2. The code can be even shorter with implementation of CASE statement, but I omitted it to not overcomplicate what you already have to understand.
    Monday, April 20, 2009 9:06 AM
  • Thank you mikhail for this wonderful example, it is very helpful

    Can i ask you how your idea was to use a CASE statement

    r u thinking like

    DECLARE @V_SWITCH INT
        SET @V_SWITCH=8
    IF EXISTS(
                        SELECT LOAD_ID INTO #RETAILER_ERROR
                          FROM RETAILER_TABLE A,LOAD_TABLE B
                         WHERE A.RETAILER_ID <> B.RETAILER_ID
                           AND LOADED_INDICATOR = 'N')
    How to say go to case 1

    ELSIF EXISTS(      SELECT LOAD_ID INTO #CUSTOMER_ERROR
                         FROM LOAD_TABLE
                        WHERE CUSTOMER_ID IS NULL
                          AND COMPANY_NAME IS NULL
                          AND LOADED_INDICATOR='N')
    THEN GOTO CASE 2

    SELECT
      CASE @V_SWITCH
      WHEN 1 THEN                    -------------------------------From this to the other -- line
                                                                                       this is in one case because it all
                                                                                       relates to the retailer id being
                                                                                       invalid. i.e
                                                                                   So in this case the inserting of
                   INSERT INTO ERROR_LOOKUP_TABLE                                  the error_lookup_table and
                               (CUSTOMER_ID,                                       update of the load table so that
                                FINANCE_AGREEMENT_NUMBER,                          all invalid records are being
                                REASON,                                            set to 'R'
                                LOADED_DATE)                                Then since the load id which are invalid
                        VALUES (A.CUSTOMER_ID,                              retailer are being collected into
                                A.AGREEMENT_NUMBER,                         retailer_error table then we can update
                                'RETAILER ID ('+CONVERT(VARCHAR,@RETAILER_ID) + ') DOES NOT EXIST',
                                GETDATE());                                 the customer table so that it contains all
                   UPDATE LOAD_TABLE                                        valid latest values.
                                                       "" DOES this set loaded_indicator='r ' for only the rows which
                                                        did not validate in this instance right.""

                      SET LOADED_INDICATOR = 'R'-------------------------------------

      WHEN 2 THEN

                  INSERT INTO ERROR_LOOKUP_TABLE
                              (CUSTOMER_ID,
                               FINANCE_AGREEMENT_NUMBER,
                               REASON,
                               LOADED_DATE)
                       VALUES (A.CUSTOMER_ID,
                               A.AGREEMENT_NUMBER,
                               'NO CUSTOMER NAME OR COMPANY NAME',
                               GETDATE());
                  UPDATE LOAD_TABLE
                     SET LOADED_INDICATOR='R'

    so on..

    and then at the end update the individual tables
              
                 
    UPDATE CUST_CUSTOMER
                        SET ADDRESS_LINE_1 = ISNULL(LDT.ADDRESS_LINE_1, ADDRESS_LINE_1),
                            ADDRESS_LINE_2 = ISNULL(LDT.ADDRESS_LINE_2, ADDRESS_LINE_2),
                            ADDRESS_LINE_3 = ISNULL(LDT.ADDRESS_LINE_3, ADDRESS_LINE_3),
                            ADDRESS_LINE_4 = ISNULL(LDT.ADDRESS_LINE_4, ADDRESS_LINE_4),
                            CITY =  ISNULL(LDT.CITY,CITY),
                            POSTCODE =  ISNULL(LDT.POSTCODE,POSTCODE),
                            TELEPHONE_DAY_NUMBER = ISNULL(LDT.DAYTIME_NUMBER, TELEPHONE_DAY_NUMBER),
                            TELEPHONE_EVENING_NUMBER =ISNULL(LDT.EVENING_NUMBER, TELEPHONE_EVENING_NUMBER),
                            TELEPHONE_MOBILE_NUMBER = ISNULL(LDT.MOBILE_NUMBER, TELEPHONE_MOBILE_NUMBER),
                            PREFERRED_TELEPHONE_CONTACT = ISNULL(LDT.PREFERRED_NUMBER, PREFERRED_TELEPHONE_CONTACT),
                            EMAIL = ISNULL(LDT.EMAIL, EMAIL),
                            USER_LAST_UPDATED_BY = 1,
                            LAST_UPDATED_DATE_TIME = GETDATE()
        FROM
            CUST_CUSTOMER
            inner join LOAD_DATA_TABLE LDT
                on LDT.CUSTOMER_ID = CUST_CUSTOMER.CUSTOMER_ID
                and LDT.LOAD_ID not in
                (SELECT LOAD_ID FROM #INCORRECT_RETAILER
                    UNION
                SELECT LOAD_ID FROM #INCORRECT_CUSTOMER)

    Is this something what your saying or if it is different could be kind enough to help and correct me.
    Tuesday, April 21, 2009 3:29 PM
  • You can use CASE statement to further decrease number of INSERTs and UPDATEs. In fact, to theoretical minimum. Instead of code prototype in my previous post (including last UPDATE of LOAD_DATA_TABLE with 'Y') you can write:

    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
    
    
    UPDATE CUST_CUSTOMER
                        SET ADDRESS_LINE_1 = ISNULL(LDT.ADDRESS_LINE_1, ADDRESS_LINE_1),
                            ADDRESS_LINE_2 = ISNULL(LDT.ADDRESS_LINE_2, ADDRESS_LINE_2),
                            ADDRESS_LINE_3 = ISNULL(LDT.ADDRESS_LINE_3, ADDRESS_LINE_3),
                            ADDRESS_LINE_4 = ISNULL(LDT.ADDRESS_LINE_4, ADDRESS_LINE_4),
                            CITY =  ISNULL(LDT.CITY,CITY),
                            POSTCODE =  ISNULL(LDT.POSTCODE,POSTCODE),
                            TELEPHONE_DAY_NUMBER = ISNULL(LDT.DAYTIME_NUMBER, TELEPHONE_DAY_NUMBER),
                            TELEPHONE_EVENING_NUMBER =ISNULL(LDT.EVENING_NUMBER, TELEPHONE_EVENING_NUMBER),
                            TELEPHONE_MOBILE_NUMBER = ISNULL(LDT.MOBILE_NUMBER, TELEPHONE_MOBILE_NUMBER),
                            PREFERRED_TELEPHONE_CONTACT = ISNULL(LDT.PREFERRED_NUMBER, PREFERRED_TELEPHONE_CONTACT),
                            EMAIL = ISNULL(LDT.EMAIL, EMAIL),
                            USER_LAST_UPDATED_BY = 1,
                            LAST_UPDATED_DATE_TIME = GETDATE()
        FROM
            CUST_CUSTOMER
            inner join LOAD_DATA_TABLE LDT
                on LDT.CUSTOMER_ID = CUST_CUSTOMER.CUSTOMER_ID
                and LDT.LOAD_ID not in
                (SELECT LOAD_ID FROM #INCORRECT_ROW)
    
    
    DROP TABLE #INCORRECT_ROW
     

    Again, I'm sure this code contains some error, because I have no tables to compile it.
    Tuesday, April 21, 2009 4:29 PM
  • This is awesome man, thanks a lot.
    Thanks really you helped me a lot.


    In

    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.COUN_COUNTRY_CODE
         AND COUN_COUNTRY_CODE='SE'

    This will fetch all the retailer id which have a dealer in the country code = 'SE'

    Then 

    (why are you specifying here T.RETA1_RETAILER_ID is null when from the inner table you obtain only valid retailer id`s)
    This part will fetch all the load_id from load_data_table,different cases where retailer id from reta_retailer_table which do not have a dealer and in country 'SE', "isnt this what its doing the inner part will fetch the valid retailer id then your specifying to get the load_id, and different cases for rows which do not have a retailer id is null this will fetch the retailer_id which are null in the table but there are none because we will be returned with only those rows which have a dealer and country code 'SE')"BUT WHY will you obtain any such rows because the inner table will
    fetch only valid retailer id"

    Then
          
    this will fetch all the load id values from LOAD_DATA_TABLE FOR i am unable to understand
    two things

    1.where WHERE T.RETAILER_ID IS NULL

     will result in the right table having no rows at all because it does not have any retailer_id rows which are null.
    2.why are you specifying

    t.retailer_id is null in case
    because we are intrested in obtaining the retailer id which are in load_data_table but not in retailer table so that we can say that those rows are invalid. because the retailer table contails all valid data and in order to obtain the invalid list of retailer id so that we can insert into the error lookup table saying that these retailer id in load_data_table are not valid.

    so in order to do that the
    SELECT LOAD_ID, CASE WHEN T.RETA1_RETAILER_ID is NOT 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_I
                         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 -- This will fetch all the retailer id from reta_retailer
                ON LDT.RETAILER_ID<>T.RETAILER_ID
     -- This will fetch all the rows from load_data_table irrespective on any match
     WHERE (T.CUSTOMER_ID IS NULL AND T.COMPANY_NAME IS NULL)--(T.RETAILER_ID IS NULL) did not understand
     -- because it will just fetch rows from t table which have null retailer id but there are none such
     -- so it will just end up select everything from load_data_table left join null

    I might have misinterpret you, please dont mind

    Is this correct kindly rectify if i am mistaken

    Thank you mikhail.
    Tuesday, April 21, 2009 10:00 PM
  • First SELECT statement in my previous post is generally correct.
    We must insert into ERLO_ERROR_LOOKUP first type of error message when RETAILER_ID isn't being retrieved with the query your specified, and we must insert second type of error message when CUSTOMER_NAME and COMPANY_NAME are nulls. It is important to note that in the original design detection of the first type of error is suppressing detection of the second type of error, so I've implemented it the same way: only one error type goes to temporary table, with "absent" RETAILER_ID going first. It is all here:
        CASE WHEN T.RETA1_RETAILER_ID is null THEN 1 ELSE 2 END as ErrorType
    In the line above I don't bother to check if CUSTOMER_NAME and COMPANY_NAME are nulls, because I initially limited result rows only to those having errors. I made it here:
        WHERE T.RETA1_RETAILER_ID is null OR (CUSTOMER_NAME IS NULL and COMPANY_NAME IS NULL)
    Now look to the first part of WHERE clause. There is a check for null column in the joined T table (which is made from your special query, just without limiting to specific RETAILER_ID). Please note that I LEFT JOINed this table. It means that when joining conditions are not met (no corresponding RETAILER_ID in T table) null will be pasted instead of T.RETA1_RETAILER_ID selected from joined table (I mean pre-selected, that is, before CASE login takes place).
    So far, I LEFT JOINed T table only to detect the T table rows which weren't actually joined - to detect LOAD_DATA_TABLE rows which aren't conforming to rows in T table and thus aren't conforming to your special query.
    Wednesday, April 22, 2009 1:34 AM
  • Thank you mikhail for that explanation sorry to bug you

    are you trying to explain that your grouping types of errors into one set where say
    retailer_id is null or (customer_id and company_name is null)

    i mean is this testing only for nulls

    and for other testing like for obtaining all the invalid retailers we need to use a different table

    Let me explain

    the retailer table contains a list of all the full/updated/valid retailers it has nothing to do with load_data_table

    the load_data_table contains information on customers,retailers... but it may contain rows such as a row can
    have a customer with a retailer_id which might be invalid so there can be many invalid retailers
    i.e. retailers who are not existing in retailer table

    so
    Your performing a left join between tables i mean it will fetch the information from tyhe left table no matter what
    if the condition checks out

    But we dont want that we want table 1- table 2 i.e.

    load_data_table-reta_retailer which will give us the rows which do not have a valid retailer


    LOAD_DATA_TABLE LDT left join T

    where T contains the list of all the valid retailers

    now when you say where t.retailer_id is null then it will result with null in T

    left join the load_data_table ldt on the condition

    ldt.retailer_id=(null)(This is what it is saying isntit by specifying t.retailer_id is null)
     then it will result in null into being inserted into #incorrect

    that is what the result i am currently getting 0 rows affected.

    but what we actually want is
    list of rows from load_data_table(containing valid and invalid retailer, i.e.
    retailer rows which arent existing in retailer)-
    list of valid rows from retailer

    gives

    list of rows which arent in retailer so they are the invalid rows

    so basically i am confused a little so just want to clarify with you reg the query is
    the below query allright i mean

    SELECT LOAD_ID,case when ldt.retailer_id is not null then 1 else 2 end as errortype
     INTO #INCORRECT
    FROM SCLD_SWEDEN_CUSTOMER_LOAD_DATA LDT
    inner 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 not null OR (LDT.CUSTOMER_NAME IS NULL
                                               and LDT.COMPANY_NAME IS NULL)



    what i am trying to say is

    make a inner join on
    load_data_table , reta_retailer
    where the retailer id in retailer NOT EQUAL to retailer_id in load_data_table


    =================

    where T.RETA1_RETAILER_ID is not null OR (LDT.CUSTOMER_NAME IS NULL
                                               and LDT.COMPANY_NAME IS NULL)

    when ldt.retailer_id is not null then the 1st case

    or else the 2 case.

    I tried executing it and the first query
    fetches liek 1532866 rows in 40 seconds into table
    puts in all the load_id and errortype in #incorrect_row but none of them have error_type 1


    The modified query is mostly wrong i believe because it is still running and more than 4 min no results yet.
    it fetches 0 rows.

    Sorry to bother you so much but i am kindof confused

    i get everything that you wrote but

    ==================
    Doubt 1
    WHERE T.RETA1_RETAILER_ID is null just makes
    ALSO YOUR SAYING YOUR PERFORMING A LEFT JOIN BETWEEN

    LOAD_DATA_TABLE LEFT JOIN (RETA_RETAILER...)T
    T.RETA1_RETAILER_ID = LDT.RETAILER_ID

    BUT THE WHERE CONDITION SHOULDNT IT BE OPPOSITE like

    LDT.RETAILER_ID = T.RETA_RETAILER_ID

    Doubt 2:

    THEN YOUR SPECIFYING
    WHERE T.RETAILER_ID IS NULL

    SO YOUR MAKING LDT.RETAILER_ID = (NULL)T.RETA1_RETAILER_ID
    NULL BECAUSE SPECIFYING WHERE T.RETA1_RETAILER_ID IS NULL


    WHEN PUTTING WHERE LDT.RETA1_RETAILER_ID= NULL
    THIS FETCHES 1532866 ROWS. IN 40 SEC ALL ERROR_TYPE 2

    Sorry to bother you so many times, you may be trying to put forward your point but i am getting stuck at this

    I just am confused are you saying your grouping errors into 1 type i mean your only
    checking whether retailer_id,customer_id,customer_name are null
    but this does not apply to checking whether there are invalid retailer id in the table wrt valid list.

    Also i wanted to get your advise, i currently know pl/sql,t-sql,java,little xml.
    do you think it is better to learn c# which is highly popular right now i mean currently even the starting jobs for c# and t-sql are very highly paid
    or do you think its better to learn SAP, as many are advising me.

    Kindly provide your valuable advise.
    Thank you.
    Wednesday, April 22, 2009 10:15 AM
  • Are you sure #INCORRECT_ROW doesn't contain ErrorType 1 after executing statement from my post?
    Maybe there are no absent retailers? Please try to run this in the same environment

    SELECT LOAD_ID
        FROM LOAD_DATA_TABLE
        WHERE RETAILER_ID not in
        (SELECT 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'
                            )

    SELECT LOAD_ID, CASE WHEN T.RETA1_RETAILER_ID is null THEN 1 ELSE 2 END as ErrorType
        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)
        order by ErrorType

    No, I wasn't trying to check either LOAD_DATA_TABLE.RETAILER_ID is null as such check wasn't in your original code.

    Please don't be sorry for asking. You are welcome. :)

    As for what you should learn. I have only slightest idea of what SAP is, because I live in a third world country. I can't say anything about SAP.
    But if we compare mainstream languages such as C++ and C# I would certainly learn C#. With this language you can write desktop AND web applications. Plus, your knowledge of SQL will be most useful with C#, because for new client-server applications most companies select C#. While C++ now is mostly used with complex math, 3D graphics, system utilities, drivers, etc.
    Wednesday, April 22, 2009 11:58 AM
  • No, this will not result in comparing
    a.retailer_id=null

    It's principle of LEFT JOIN (and RIGHT JOIN): everything that isn't available for being joined is replaced with NULL. Only columns of the table on the LEFT side of LEFT JOIN are populated with data (on the RIGHT side for RIGHT JOIN).

    Imagine two tables: TableComplete and TableIncomplete. Both have only one column: Id.
    TableComplete has 5 rows with values: 1,2,3,4,5.
    TableComplete has 3 rows with values: 1,3,5.


    1.
    SELECT TableComplete.Id as TC_Id, TableIncomplete.Id as TI_Id
        FROM TableComplete
            LEFT JOIN TableIncomplete on TableIncomplete.Id = TableComplete.Id

    will produce

    TC_Id        TI_Id
    -----------------------
    1               1
    2               null
    3               3
    4               null
    5               5


    2.
    SELECT TableComplete.Id as TC_Id, TableIncomplete.Id as TI_Id
        FROM TableComplete
            LEFT JOIN TableIncomplete on TableIncomplete.Id = TableComplete.Id
        WHERE TableComplete.Id is null

    will produce nothing

    TC_Id        TI_Id
    -----------------------

    because no row from "pre-selected result" (see example 1.) has NULL in TC_Id


    3.
    SELECT TableComplete.Id as TC_Id, TableIncomplete.Id as TI_Id
        FROM TableComplete
            LEFT JOIN TableIncomplete on TableIncomplete.Id = TableComplete.Id
        WHERE TableIncomplete.Id is null

    will produce

    TC_Id        TI_Id
    -----------------------
    2               null
    4               null

    because only these rows from "pre-selected result" have NULL in TI_Id

    Figuratively, these are your rows from LOAD_DATA_TABLE with RETAILER_ID values which are not in the (RETA_RETAILER and the company)
    Wednesday, April 22, 2009 2:42 PM
  • Hi Mikhail, hope your having a good day.

    I was having a doubt

    The login you gave applies wonderful when there are 2 cases but when there are more than 2 cases

     there are some more cases i just thought i would be able to tailor it but unable to, kindly help me out

                 --  Fail if Vehicle Make and Model do not exist
      
      
                 IF NOT EXISTS (SELECT VELO_SK
                                  FROM VELO_VEHICLE_LOOKUP VELO1
                                 WHERE VELO1.MAKE_ID = @VEHICLE_MAKE
                                   AND VELO1.MODEL_ID = @VEHICLE_MODEL
                                   AND VELO1.COUNTRY_CODE = 'SE'
                               )
    like this above is one of another check
    below i just modified the  query where there needs to be more than 1 case, is this allright

    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)

    probably not because the query will probably contains all valid and null in place where invalid data from left join with load_data_table but it wont work when it comes to cases.

    My aim was so all the valid retailer id, vehicle_id, make, model, finance_aggr will all be in T

    Then it will be compared againist LOAD_DATA_TABLE DETAILS using YOUR idea i.e left join

    so all the invalid data will be null in the table #incorrect_row and then

    i am unable to figure out how to map them with their respective cases so that the insert for reason column into error_lookup_table can be made to corresponding error

    Or the case cannot be applied for different cases and we need to have different tables like

    SELECT LOAD_ID,CASE WHEN T.RETAILER_ID IS NULL THEN 1 ELSE 2 END AS ERROR_TYPE
        INTO #INCORRECT_ROW
       FROM LOAD_DATA_TABLE LDT
        LEFT JOIN
                (SELECT RETA1.RETA_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)

    SELECT LOAD_ID,CASE WHEN T.RETAILER_ID IS NULL THEN 1 ELSE 2 END AS ERROR_TYPE
        INTO #INCORRECT_ROW
       FROM LOAD_DATA_TABLE LDT
        LEFT JOIN
                (SELECT RETA1.RETA_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)
     
    SELECT LOAD_ID,CASE WHEN A.RETAILER_ID IS NULL THEN 1 ELSE 2 END AS ERROR_TYPE
       INTO #INCORRECT_ROW2
       FROM LOAD_DATA_TABLE LDT
       LEFT JOIN
            (SELECT VELO_SK
               FROM VELO_VEHICLE_LOOKUP)V
         ON (V.MODEL_ID = LDT.MODEL_ID)
             OR
             V.MAKE_ID  = LDT.MAKE_ID)
      WHERE A.VELO_SK is null OR (LDT.MODEL_ID IS NULL OR LDT.MAKE_ID IS NULL)
    This will put the invalid vehicles in incorrect_row2

    KINDLY HELP

    tHANK YOU
    Thursday, April 23, 2009 2:48 PM
  • You just need second LEFT JOIN

    SELECT LOAD_ID,
    		CASE
    			WHEN T.RETA1_RETAILER_ID is null THEN 1
    			WHEN CUSTOMER_NAME IS NULL and COMPANY_NAME IS NULL THEN 2
    			ELSE 3 -- there is no matching combination of MODEL_ID and MAKE_ID in the VELO_VEHICLE_LOOKUP with COUNTRY_CODE = 'SE'
    		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
    		LEFT JOIN
    			VELO_VEHICLE_LOOKUP V
    				ON V.MODEL_ID = LDT.MODEL_ID AND V.MAKE_ID = LDT.MAKE_ID AND V.COUNTRY_CODE = 'SE'
    	WHERE T.RETA1_RETAILER_ID IS NULL
    		OR (CUSTOMER_NAME IS NULL and COMPANY_NAME IS NULL)
    		OR V.VELO_SK IS NULL
     

    As I said before only one type of error will be recorded, since CASE gives only result for the first positive comparing per row. If you want to change order of errors' detection, just reorder comparings in CASE construction. If you want to record all error types for each row let me know.
    • Marked as answer by robin_blue Thursday, April 30, 2009 4:06 PM
    Thursday, April 23, 2009 4:08 PM
  • Thanks a lot for the reply mikhail.
    Thursday, April 30, 2009 4:06 PM
  • I still doubt you'll get anywhere near the performance you could if you avoid iterative solutions completely. Rob
    http://msmvps.com/blogs/robfarley - http://www.lobsterpot.com.au - Adelaide SQL UG President - Did this help? Mark it as an answer!


    Not abusive; removing the "Abusive" report.  Please refrain from reporting someone as "abusive" for this kind of situation.  I don't think Rob should be reported for abuse just because he disagreed with a method.  Also, I think it is fair game to re-iterate a suggestion to emphasize the suggestion.

    Note that in the end you implemented code without using an iterative method which is exactly what Rob was suggesting.

    I would have suggested the same thing.  If you will search this forum for "loathe" "cursors" and "waldrop" you will find that I also have a very low opinion of iterative methods that use cursors.


    Kent Waldrop

    Monday, May 4, 2009 2:26 PM
    Moderator
  • Thanks Kent. I certainly wasn't meaning to come across as abusive.

    http://msmvps.com/blogs/robfarley - http://www.lobsterpot.com.au - Adelaide SQL UG President - Did this help? Mark it as an answer!
    Tuesday, May 5, 2009 12:05 AM
  • Kent,

    I agree with you, the post is really far from being abusive. I know Rob and I can asure he is not that kind of person.

    Thanks,
    Alejandro Mesa
    Tuesday, May 5, 2009 12:22 AM
    Moderator