ROW BY ROW COMPARISON AND UPDATION

Gaurav10b · May 30th, 2007, 09:30 AM

SAMPLE DATA
flt_nm Unq.id seq. calc_val
DNT10 3091 3 1
DNT10 3092 6 NULL
DNT10 3093 7 0
DPD27 3122 1 1
DPD27 3123 4 0
DPD54 3124 2 0
DPH40 3132 1 NULL
DPH40 3133 2 1
DPH40 3134 3 0
DPK87 3146 1 1
DPK87 3147 3 0
DPQ43 3179 1 NULL
DPQ43 3180 2 0
DPX07 3209 1 1
DPX07 3210 4 0
DQA59 3227 1 NULL
DQA59 3228 2 0
DQB01 3229 1 1
DQB01 3230 2 0
DQC19 3233 1 1
DQC19 3234 2 0

i want to update every 1 with a 0 for a particular flt_nm . if there is a null above 0 then this should not be converted.the tables cal_val column should be updated with 1 everytime it sees a 1 above 0 for a particular flt_nm. if there are more then one consecutive 1s above 0 then they should be also converted.
the problem is this this should be done without using CURSORS. i am trying to use while loop.But i am not able to do it.unq_id is the primary key.and sequence number keeps on changing for a flt_nm i.e.for every new fault it starts with one.
Please help!!

happygv · May 30th, 2007, 10:59 AM

Quote:

quote:this should be done without using CURSORS

Any reason behind it? I dont think it is going to be easy for you to do without cursors. Still you need to simulate what a cursor does, without using it.

_________________________
- Vijay G
Strive for Perfection

Gaurav10b · May 31st, 2007, 12:32 AM

i know its difficult without cursors. its because there are some performance issues when i use cursors.

happygv · May 31st, 2007, 01:07 AM

Yes, there are but for having said that one cannot avoid cursors completely. It is good as long as cursors are handle properly and used when no better alternatives are available.

And one can't assure that the looping wont have any such performance issues are perfect fit for such conditions. There are chances that looping too could be written badly, trying to simulate the cursor functionality. Poorly managed variables and constructs can be even worse than cursors sometimes.

I would say, avoid cursors to the best possible, but use them when you dont have a better/equal alternative way of doing it.

_________________________
- Vijay G
Strive for Perfection

Gaurav10b · May 31st, 2007, 01:12 AM

I have found a solution thanks a ton...

i am using while loop for the same

happygv · May 31st, 2007, 01:17 AM

Do you mind posting the code here?

_________________________
- Vijay G
Strive for Perfection

Jeff Moden · June 2nd, 2007, 04:17 PM

Quote:

quote:Originally posted by Gaurav10b
I have found a solution thanks a ton...
i am using while loop for the same

That's it? You're going to settle for a WHILE loop? A WHILE loop is almost as slow as a cursor... heck, it's the main part of a cursor! All you did was trade one great evil for another.

I also assume that when you say things like "when there is a NULL above a 0", you mean in the row immediately above according to the order and match of Flt_Nm and SEQ.

If your table looks like this (you should post your sample data this way, as well)...

Code:

  CREATE TABLE yourtable (Flt_Nm VARCHAR(10),UnqID INT,Seq INT,Calc_Val INT)
 INSERT INTO yourtable (Flt_Nm,UnqID,Seq,Calc_Val)
 SELECT 'DNT10',3091,3,1     UNION ALL
 SELECT 'DNT10',3092,6,NULL  UNION ALL
 SELECT 'DNT10',3093,7,0     UNION ALL
 SELECT 'DPD27',3122,1,1     UNION ALL
 SELECT 'DPD27',3123,4,0     UNION ALL  
 SELECT 'DPD54',3124,2,0     UNION ALL  
 SELECT 'DPH40',3132,1,NULL  UNION ALL
 SELECT 'DPH40',3133,2,1     UNION ALL  
 SELECT 'DPH40',3134,3,0     UNION ALL  
 SELECT 'DPK87',3146,1,1     UNION ALL
 SELECT 'DPK87',3147,3,0     UNION ALL  
 SELECT 'DPQ43',3179,1,NULL  UNION ALL
 SELECT 'DPQ43',3180,2,0     UNION ALL
 SELECT 'DPX07',3209,1,1     UNION ALL
 SELECT 'DPX07',3210,4,0     UNION ALL  
 SELECT 'DQA59',3227,1,NULL  UNION ALL
 SELECT 'DQA59',3228,2,0     UNION ALL
 SELECT 'DQB01',3229,1,1     UNION ALL
 SELECT 'DQB01',3230,2,0     UNION ALL  
 SELECT 'DQC19',3233,1,1     UNION ALL
 SELECT 'DQC19',3234,2,0

... add an index that looks like this...

Code:

 CREATE INDEX IX_yourtable_Flt_Nm_Seq
     ON yourtable (Flt_Nm,Seq)
GO

... then use this code instead of that bloody slow WHILE loop...

Code:

DECLARE @PrevCalcVal    INT
DECLARE @Dummy      INT
DECLARE @PrevFltNm  VARCHAR(10)


 UPDATE yourtable
    SET @Dummy   = Calc_Val = CASE 
                                WHEN @PrevCalcVal = 1 
                                 AND Calc_Val = 0 
                                 AND Flt_Nm = ISNULL(@PrevFltNm,Flt_Nm) 
                        THEN 1 
                                ELSE Calc_Val 
                              END,
        @PrevCalcVal = Calc_Val,
        @PrevFltNm   = Flt_Nm
   FROM yourtable WITH (INDEX(IX_yourtable_Flt_Nm_Seq))

 SELECT * FROM yourtable

Please, next time, post both the BEFORE and AFTER listing of the sample data so we can know we're writing the correct code :D, for sure... although your explanation was plain enough, there are some questions about it, so I'm not sure if my code does exactly what you want it to do. Thanks.

--Jeff Moden