Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 30th, 2007, 09:30 AM
Registered User
 
Join Date: May 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Gaurav10b
Default ROW BY ROW COMPARISON AND UPDATION

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!!
 
Old May 30th, 2007, 10:59 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old May 31st, 2007, 12:32 AM
Registered User
 
Join Date: May 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Gaurav10b
Default

i know its difficult without cursors. its because there are some performance issues when i use cursors.
 
Old May 31st, 2007, 01:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old May 31st, 2007, 01:12 AM
Registered User
 
Join Date: May 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Gaurav10b
Default

I have found a solution thanks a ton...

i am using while loop for the same
 
Old May 31st, 2007, 01:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Do you mind posting the code here?

_________________________
- Vijay G
Strive for Perfection
 
Old June 2nd, 2007, 04:17 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Scrollbar in row of Datagrid Except Header Row Theone84 ASP.NET 2.0 Professional 0 August 11th, 2008 12:10 AM
To retrieve a row by giving row number prad_a MySQL 1 March 22nd, 2007 11:20 PM
Read a column and Search Row by Row in another col AyatKh Excel VBA 2 January 26th, 2005 12:02 PM
Row blinking or Row Red mateenmohd Classic ASP Basics 4 January 26th, 2005 05:40 AM
Manage data row by row in datagrid Dragonist Classic ASP Databases 5 July 29th, 2004 04:17 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.