February 27, 2009 at 7:30 am
Can someone help me removing cursor from this TSQL if its possible
-----------------------------------------------------------------------------
USE [MRG_M002]
GO
/****** Object: StoredProcedure [dbo].[PS_TAG_RRF_EVENT] Script Date: 02/27/2009 09:24:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PS_TAG_RRF_EVENT] AS
DECLARE @@Cur_FICH_MAR_BRUT cursor
DECLARE @@Cur_FICH_EVE_BRUT cursor
DECLARE @MAR_ID int
DECLARE @EVE_DIST float
DECLARE @EVE_DESC varchar(50)
DECLARE @EVE_DEBUT float
DECLARE @EVE_FIN float
Begin
SET @@Cur_FICH_MAR_BRUT = cursor LOCAL FAST_FORWARD for SELECT MAR_ID FROM RRF_FICH_MAR_BRUT WHERE NOT MAR_ID IN (SELECT DISTINCT MAR_ID FROM RRF_SEGMN_RETRO) ORDER BY MAR_ID
OPEN @@Cur_FICH_MAR_BRUT
FETCH NEXT FROM @@Cur_FICH_MAR_BRUT INTO @MAR_ID
WHILE (@@FETCH_STATUS <> -1) Begin
SET @@Cur_FICH_EVE_BRUT = cursor LOCAL FAST_FORWARD for SELECT EVE_DIST, EVE_DESC FROM RRF_FICH_EVE_BRUT WHERE MAR_ID = @MAR_ID ORDER BY EVE_DIST
OPEN @@Cur_FICH_EVE_BRUT
FETCH NEXT FROM @@Cur_FICH_EVE_BRUT INTO @EVE_DIST, @EVE_DESC
set @EVE_DEBUT = -1
set @EVE_FIN = -1
WHILE (@@FETCH_STATUS <> -1) Begin
IF(@EVE_DEBUT = -1)
Begin
IF (LEN(rtrim(@EVE_DESC)) >= 5)
Begin
IF(RIGHT(rtrim(@EVE_DESC), 5) = 'DEBUT')
Begin
set @EVE_DEBUT = @EVE_DIST
End
End
End
ELSE
Begin
IF(@EVE_FIN = -1)
Begin
If(Len(rtrim(@EVE_DESC)) >= 3)
Begin
IF(RIGHT(rtrim(@EVE_DESC), 3) = 'FIN')
Begin
set @EVE_FIN = @EVE_DIST
End
End
End
ELSE
Begin
IF(Len(rtrim(@EVE_DESC)) >= 3)
Begin
IF(RIGHT(rtrim(@EVE_DESC), 3) = 'FIN')
Begin
set @EVE_FIN = @EVE_DIST
End
ELSE
Begin
IF(LEN(rtrim(@EVE_DESC)) >= 5)
Begin
IF(RIGHT(rtrim(@EVE_DESC), 5) = 'DEBUT')
Begin
UPDATE RRF_FICH_GPS_BRUT SET VALID_POINT = 0 WHERE VAL_DISTN >= @EVE_DEBUT AND VAL_DISTN <= @EVE_FIN AND MAR_ID = @MAR_ID
set @EVE_DEBUT = @EVE_DIST
set @EVE_FIN = -1
END
ELSE
Begin
UPDATE RRF_FICH_GPS_BRUT SET VALID_POINT = 0 WHERE VAL_DISTN >= @EVE_DEBUT AND VAL_DISTN <= @EVE_FIN AND MAR_ID = @MAR_ID
set @EVE_DEBUT = -1
set @EVE_FIN = -1
END
End
ELSE
Begin
UPDATE RRF_FICH_GPS_BRUT SET VALID_POINT = 0 WHERE VAL_DISTN >= @EVE_DEBUT AND VAL_DISTN <= @EVE_FIN AND MAR_ID = @MAR_ID
set @EVE_DEBUT = -1
set @EVE_FIN = -1
End
End
End
End
End
FETCH NEXT FROM @@Cur_FICH_EVE_BRUT INTO @EVE_DIST, @EVE_DESC
END
IF(@EVE_DEBUT <> -1 AND @EVE_FIN <> -1)
UPDATE RRF_FICH_GPS_BRUT SET VALID_POINT = 0 WHERE VAL_DISTN >= @EVE_DEBUT AND VAL_DISTN <= @EVE_FIN AND MAR_ID = @MAR_ID
FETCH NEXT FROM @@Cur_FICH_MAR_BRUT INTO @MAR_ID
END
END
-----------------------------------------------------------------------------
Thanks
February 27, 2009 at 8:06 am
Can you post the table defs and some sample data, with a outline of what the procedure is meant to do?
February 27, 2009 at 8:48 am
The procedure is use to set a flag in the table RRF_FICH_GPS_BRUT
Here is the table def and sample data :
USE [MRG_M002]
GO
/****** Object: Table [dbo].[RRF_SEGMN_RETRO] Script Date: 02/27/2009 10:13:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RRF_SEGMN_RETRO](
[IDE_SEGMN_RETRO] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[MAR_ID] [int] NOT NULL,
[NOM_FICHR] [varchar](15) NOT NULL,
[DATE_CONTRL] [datetime] NOT NULL,
[VAL_RL_G] [float] NULL,
[VAL_RL_D] [float] NULL,
[VAL_RL_MOY] [float] NULL,
[VAL_CJ_G] [float] NULL,
[VAL_CJ_D] [float] NULL,
[VAL_CJ_MOY] [float] NULL,
[VAL_CN_G] [float] NULL,
[VAL_CN_D] [float] NULL,
[VAL_CN_MOY] [float] NULL,
[VAL_PCENT_MES_G] [float] NULL,
[VAL_PCENT_MES_D] [float] NULL,
[VAL_PCENT_MES_MOY] [float] NULL,
[NO_BANDE] [int] NULL,
[VAL_RETRO_MAX] [int] NULL,
[GPS_XD] [float] NULL,
[GPS_YD] [float] NULL,
[GPS_XF] [float] NULL,
[GPS_YF] [float] NULL,
[GEO_SEGMN] [image] NULL,
[GEO_SEGMN_XLO] [float] NULL,
[GEO_SEGMN_YLO] [float] NULL,
[GEO_SEGMN_XHI] [float] NULL,
[GEO_SEGMN_YHI] [float] NULL,
[COD_CS_DEBUT] [varchar](4) NULL,
[RTSS_DEBUT] [varchar](14) NULL,
[VAL_CHANG_DEBUT] [int] NULL,
[COD_CS_FIN] [varchar](4) NULL,
[RTSS_FIN] [varchar](14) NULL,
[VAL_CHANG_FIN] [int] NULL,
[VAL_LONGR_GOFT] [float] NULL,
[VAL_LONGR_ECODYN] [float] NOT NULL,
[Type_seg] [int] NOT NULL,
CONSTRAINT [PK_RRF_SEGMN_RETRO] PRIMARY KEY CLUSTERED
(
[IDE_SEGMN_RETRO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MRG_M001_Group]
) ON [MRG_M001_Group] TEXTIMAGE_ON [MRG_M001_Group]
GO
SET ANSI_PADDING OFF
sample Data :
IDE_SEGMN_RETRO MAR_ID NOM_FICHR DATE_CONTRL VAL_RL_G VAL_RL_D VAL_RL_MOY VAL_CJ_G VAL_CJ_D VAL_CJ_MOY VAL_CN_G VAL_CN_D VAL_CN_MOY VAL_PCENT_MES_G VAL_PCENT_MES_D VAL_PCENT_MES_MOY NO_BANDE VAL_RETRO_MAX GPS_XD GPS_YD GPS_XF GPS_YF GEO_SEGMN GEO_SEGMN_XLO GEO_SEGMN_YLO GEO_SEGMN_XHI GEO_SEGMN_YHI COD_CS_DEBUT RTSS_DEBUT VAL_CHANG_DEBUT COD_CS_FIN RTSS_FIN VAL_CHANG_FIN VAL_LONGR_GOFT VAL_LONGR_ECODYN Type_seg
--------------- ----------- --------------- ----------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------- ------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- ---------------------- ---------------------- ------------ -------------- --------------- ---------- -------------- ------------- ---------------------- ---------------------- -----------
36455 19 020EA601_2006 2006-09-25 00:00:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -68,686094967 48,3775768 -68,4004741 48,518307583 NULL NULL NULL NULL NULL 3371 0002009060000C 20 3371 0002009100B1A0 549 NULL 31,656 1
36456 19 020EA601_2006 2006-09-25 00:00:00.000 0 0 0 0 0 0 0 0 0 0 0 0 1 0 -68,4004741 48,518307583 -68,4004741 48,518307583 0xC2FFD20FBC8CCF11ABDE08003601B769020000008510247DA01951C099AB4EA95642484000000000000000008510247DA01951C099AB4EA9564248400000000000000000 -68,4004204609101 48,5182696947224 -68,4004204609101 48,5182696947224 3371 0002009100B1A0 549 3371 0002009100B1A0 549 NULL 0 2
36457 19 020EA601_2006 2006-09-25 00:00:00.000 0 0 0 0 0 0 0 0 0 0 0 0 1 0 -68,4004741 48,518307583 -68,4004741 48,518307583 0xC2FFD20FBC8CCF11ABDE08003601B769020000008510247DA01951C099AB4EA95642484000000000000000008510247DA01951C099AB4EA9564248400000000000000000 -68,4004204609101 48,5182696947224 -68,4004204609101 48,5182696947224 3371 0002009100B1A0 549 3371 0002009100B1A0 549 NULL 0 3
36458 19 020EA601_2006 2006-09-25 00:00:00.000 0 0 0 0 0 0 0 0 0 0 0 0 1 0 -68,4004741 48,518307583 -68,4004741 48,518307583 0xC2FFD20FBC8CCF11ABDE08003601B769020000008510247DA01951C099AB4EA95642484000000000000000008510247DA01951C099AB4EA9564248400000000000000000 -68,4004204609101 48,5182696947224 -68,4004204609101 48,5182696947224 3371 0002009100B1A0 549 3371 0002009100B1A0 549 NULL 0 4
36459 19 020EA601_2006 2006-09-25 00:00:00.000 0 0 0 0 0 0 0 0 0 0 0 0 1 0 -68,4004741 48,518307583 -68,4004741 48,518307583 0xC2FFD20FBC8CCF11ABDE08003601B769020000008510247DA01951C099AB4EA95642484000000000000000008510247DA01951C099AB4EA9564248400000000000000000 -68,4004204609101 48,5182696947224 -68,4004204609101 48,5182696947224 3371 0002009100B1A0 549 3371 0002009100B1A0 549 NULL 0 5
36460 20 132EA601_2006 2006-09-25 00:00:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -69,432680317 47,956438817 -69,430847367 47,956851233 0xC2FFD20FBC8CCF11ABDE08003601B76904000000356733F0B05B51C0A5315EA96CFA47400000000000000000135A2347B05B51C0CB4E9AF56CFA47400000000000000000EA56AE9F965B51C0B8E6ED8678FA4740000000000000000012B27820935B51C0EE9325437AFA47400000000000000000 -69,4326744558319 47,9564410886699 -69,4308549097698 47,9568561490522 3373 0013212083000C 372 3373 0013212083000C 516 NULL 0,15 1
36461 20 132EA601_2006 2006-09-25 00:00:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -69,315807933 48,0363264 -69,315477017 48,036620417 0xC2FFD20FBC8CCF11ABDE08003601B7690200000006D6001D365451C07550B383A60448400000000000000000EEB01EA5305451C038961520B00448400000000000000000 -69,3158028133549 48,0363316178854 -69,3154690551489 48,0366249184994 3373 0013212110000C 1777 3373 0013212110000C 1818 NULL 0,0500000000000007 1
36462 20 132EA601_2006 2006-09-25 00:00:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -69,164973633 48,119474717 -69,164310217 48,119965933 0xC2FFD20FBC8CCF11ABDE08003601B769020000009BC18D3F8E4A51C0ADEF743C4A0F48400000000000000000E21A9F32834A51C03066A3455A0F48400000000000000000 -69,1649321445788 48,1194530077913 -69,1642576745758 48,1199423835609 3373 0013212142000D 39 3373 0013212142000D 113 NULL 0,0499999999999972 1
36463 20 132EA601_2006 2006-09-25 00:00:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -69,029329933 48,210539917 -69,028711633 48,210806367 0xC2FFD20FBC8CCF11ABDE08003601B769020000009A383481E04151C0E72C530DF31A484000000000000000001EC5DA79D64151C0DD5FC0A4FB1A48400000000000000000 -69,0293276796314 48,2105423599367 -69,028715575831 48,2108045520142 3373 0013213020000C 421 3373 0013213020000C 475 NULL 0,0489999999999995 1
36464 20 132EA601_2006 2006-09-25 00:00:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -69,017119633 48,215774217 -69,015831967 48,216323217 0xC2FFD20FBC8CCF11ABDE08003601B7690300000074091C6F184151C09ACD066A9E1B48400000000000000000199FB9A10C4151C0745F3F85A81B484000000000000000003FC5C374034151C04F3F9F60B01B48400000000000000000 -69,0171163343237 48,2157719166964 -69,0158359443494 48,2163201119971 3373 0013213020000C 1499 3373 0013213020000C 1612 NULL 0,100999999999999 1
------------------------------------------------------------------------------------------------------------------
USE [MRG_M002]
GO
/****** Object: Table [dbo].[RRF_FICH_EVE_BRUT] Script Date: 02/27/2009 10:15:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RRF_FICH_EVE_BRUT](
[MAR_ID] [int] NOT NULL,
[EVE_DIST] [float] NOT NULL,
[EVE_LONG] [float] NOT NULL,
[EVE_LAT] [float] NOT NULL,
[EVE_DESC] [varchar](50) NOT NULL,
[Identifier_EVE] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_RRF_FICH_EVE_BRUT_Identifier_EVE] DEFAULT (newid()),
CONSTRAINT [PK_RRF_FICH_EVE_BRUT] PRIMARY KEY CLUSTERED
(
[MAR_ID] ASC,
[EVE_DIST] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MRG_M001_Group]
) ON [MRG_M001_Group]
GO
SET ANSI_PADDING OFF
sample Data :
MAR_ID EVE_DIST EVE_LONG EVE_LAT EVE_DESC Identifier_EVE
----------- ---------------------- ---------------------- ---------------------- -------------------------------------------------- ------------------------------------
19 0,014 -68,6863024333333 48,3776012666667 AG DEBUT 2F4CD099-F1F9-4B66-8737-47A526E72070
19 0,051 -68,68579825 48,3775884166667 AG FIN 04865A45-9808-4FAA-BF82-F11A55E045E6
19 75,954 -68,6863724166667 48,3776909833333 AG FIN 162BEA9D-FCC6-46CF-92AC-5C386A28D78D
20 0,407 -69,4326803166667 47,9564388166667 ZS DEBUT 1D3CE240-EA63-472C-A57B-8947319EBF9F
20 0,508 -69,4313293333333 47,9567397 ZS FIN 5490779F-D99A-4D69-A11A-1DDBA29DE23C
20 12,467 -69,3222782666667 48,0301797333333 REP 56934DB7-5017-4B8E-9205-4B684796121A
20 12,886 -69,3191886166667 48,03336905 TR DEBUT 3518C6EF-2A0F-4396-B75E-4EB9AA37A07E
20 13,183 -69,3167625666667 48,03553205 TR FIN 0AF40D40-A9E4-43DA-8B6B-D9FABDC89E5A
20 14,012 -69,3098778333333 48,04112755 REP 07CBA886-FC6E-4075-BA1B-4061C14ECD76
20 26,209 -69,2005399333333 48,1127254333333 AG DEBUT 4132C43E-B7FE-469A-BF5C-C77E8C4B6534
-----------------------------------------------------------------------------------------------------------------
USE [MRG_M002]
GO
/****** Object: Table [dbo].[RRF_FICH_GPS_BRUT] Script Date: 02/27/2009 10:14:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RRF_FICH_GPS_BRUT](
[MAR_ID] [int] NOT NULL,
[VAL_DISTN] [float] NOT NULL,
[GPS_X] [float] NULL,
[GPS_Y] [float] NULL,
[HEURE] [char](8) NULL,
[COD_CS] [varchar](4) NULL,
[NOM_RTSS] [varchar](14) NULL,
[VAL_CHANG] [int] NULL,
[VAL_DECLG] [float] NULL,
[VALID_POINT] [bit] NOT NULL CONSTRAINT [DF_RRF_FICH_GPS_BRUT_VALID_POINT] DEFAULT ((1)),
[Identifier_GPS] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_RRF_FICH_GPS_BRUT_Identifier_GPS] DEFAULT (newid()),
CONSTRAINT [PK_RRF_FICH_GPS_BRUT] PRIMARY KEY CLUSTERED
(
[MAR_ID] ASC,
[VAL_DISTN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MRG_M001_Group]
) ON [MRG_M001_Group]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[RRF_FICH_GPS_BRUT] WITH CHECK ADD CONSTRAINT [FK_RRF_FICH_GPS_BRUT_RRF_FICH_MAR_BRUT] FOREIGN KEY([MAR_ID])
REFERENCES [dbo].[RRF_FICH_MAR_BRUT] ([MAR_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RRF_FICH_GPS_BRUT] CHECK CONSTRAINT [FK_RRF_FICH_GPS_BRUT_RRF_FICH_MAR_BRUT]
MAR_ID VAL_DISTN GPS_X GPS_Y HEURE COD_CS NOM_RTSS VAL_CHANG VAL_DECLG VALID_POINT Identifier_GPS
----------- ---------------------- ---------------------- ---------------------- -------- ------ -------------- ----------- ---------------------- ----------- ------------------------------------
20 0 -69,439679267 47,9520357 150012 3373 0002008100000D 6800 4 0 B7A5F691-6D47-4412-B26F-50781BEC5883
20 0,05 -69,436869683 47,955347933 150228 3373 000200810032A0 114 1 0 D80BBFE3-6AF5-4EB5-B0A0-7F2E5CEE47B4
20 0,1 -69,436593233 47,95549975 150230 3373 000200810032A0 140 -1 1 209B77D8-B331-464D-AF0E-17A793190D40
20 0,15 -69,435847883 47,955716433 150234 3373 0013212083000C 122 1 1 F29EEBFA-9373-43DD-B3A2-046F7AFC8AFE
20 0,201 -69,435424583 47,955818083 150236 3373 0013212083000C 155 0 1 18449843-4014-4DF2-8259-FB2B7AC85C62
20 0,25 -69,434566767 47,956010833 150240 3373 0013212083000C 223 0 1 51281E87-62E0-445E-B774-94AF5942695C
20 0,3 -69,433813917 47,956180133 150244 3373 0013212083000C 282 0 1 8525A20B-CBE2-4C78-87D7-3EBE1A94A431
20 0,35 -69,433460867 47,95625865 150246 3373 0013212083000C 310 1 1 99656F86-BD0E-43CA-9794-F30A7814FC7F
20 0,4 -69,432680317 47,956438817 150250 3373 0013212083000C 372 0 0 C101BFBF-1768-4280-B9A4-6C26D774FBC9
20 0,451 -69,432250067 47,956535367 150252 3373 0013212083000C 406 0 0 61C43D83-3746-43DA-BE39-4FEB1B053563
Thanks
February 27, 2009 at 9:01 pm
Stanley Pagenel (2/27/2009)
The procedure is use to set a flag in the table RRF_FICH_GPS_BRUT
Based on what? What are the business rules?
Also, please see how to post sample data in the article at the link in my signature below.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2009 at 1:50 pm
Thanks Dugi that help me fix my problem.
Have a nice day,
Stan
March 1, 2009 at 9:25 am
Stanley Pagenel (2/28/2009)
Thanks Dugi that help me fix my problem.Have a nice day,
Stan
Nope... if all you've done is replace a Cursor with a Temp table and While loop, all you've done is replace one procedural problem with another. If you make the Cursors FORWARD ONLY, READ ONLY, they'll be just as "fast" (just as slow, I should say).
The only way to fix the slowness of a Cursor (which contains a While loop, by the way), it to figure out what the code does and write some good ol' SET BASED code to replace it. Unless you do that, your code will still be plagued with resource and performance problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2009 at 11:49 pm
Stanley Pagenel (2/28/2009)
Thanks Dugi that help me fix my problem.Have a nice day,
Stan
You are welcome, the credits goes to Kamran Ali!
😎
March 2, 2009 at 6:03 am
Heh... you mean the pork chops go to Kamran Ali. 😛
If someone would post the "example data" in a reasonable format according to the link in my signature below, maybe someone else could show someone how to do this without a loop at all. 😉
Stanley... run your cursor code against your "new improved cursorless" code and see that there's no difference in performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2009 at 6:24 am
Hi Jeff !
I know your rules about T-SQL and special techniques that you can use for any kind of the problem till today! I just make a little search couz I knew that some articles are posted before about cursor and avoiding techniques and I just find the Kamran Ali's Article... so I'm going to post the other post here about Telly Table ( your special table )
http://www.sqlservercentral.com/articles/TSQL/62867/[/url]
Stan:
Sure that you will find much better technique how to use the Telly for your problem with cursors or with Loop! Check the live link above!
:hehe:
Dugi
March 2, 2009 at 9:24 am
There's no special technique for this... it's simply the choice to figure it out using regular ol' set based code. It doesn't require a Tally table or any of the other "tricks" I know... just some straight forward common sense programming. I'm just not gonna try it because of the condition of the data posted.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2009 at 9:32 am
Here's the same code with the outer loop removed and the update statements
replaced by PRINT statements, to try to identify what this thing does.
I made the assumption that this would be something like
"identify valid start and end points, flag everything else as an update" but it isn't,
in fact I'm bu88ered if I can see any logic in it at all, even with some data added to the sample.
It might be more clear with a larger sample of actual data.
The rows that would get marked in the target table are shown like this: '-- marked'
set nocount on
DECLARE @@Cur_FICH_EVE_BRUT cursor
DECLARE @MAR_ID int
DECLARE @EVE_DIST float
DECLARE @EVE_DESC varchar(50)
DECLARE @EVE_DEBUT float
DECLARE @EVE_FIN float
---------------------------------------
DROP TABLE #RRF_FICH_EVE_BRUT
CREATE TABLE #RRF_FICH_EVE_BRUT (
[MAR_ID] [int] NOT NULL,
[EVE_DIST] [float] NOT NULL,
[EVE_LONG] [float] NOT NULL,
[EVE_LAT] [float] NOT NULL,
[EVE_DESC] [varchar](50) NOT NULL)
INSERT INTO #RRF_FICH_EVE_BRUT (MAR_ID, EVE_DIST, EVE_LONG, EVE_LAT, EVE_DESC)
SELECT 14, 0.014,-68.6863024333333,48.3776012666667,'AG DEBUT' UNION ALL
SELECT 14, 0.051,-68.68579825,48.3775884166667,'REP' UNION ALL
SELECT 14, 70.954,-68.6863724166667,48.3776909833333,'AG FIN' UNION ALL -- marked
SELECT 15, 0.014,-68.6863024333333,48.3776012666667,'AG FIN' UNION ALL
SELECT 15, 0.051,-68.68579825,48.3775884166667,'REP' UNION ALL
SELECT 15, 71.954,-68.6863724166667,48.3776909833333,'AG DEBUT' UNION ALL
SELECT 16, 0.014,-68.6863024333333,48.3776012666667,'AG DEBUT' UNION ALL
SELECT 16, 0.051,-68.68579825,48.3775884166667,'AG FIN' UNION ALL
SELECT 16, 72.954,-68.6863724166667,48.3776909833333,'AG DEBUT' UNION ALL -- marked
SELECT 17, 0.014,-68.6863024333333,48.3776012666667,'AG DEBUT' UNION ALL
SELECT 17, 0.051,-68.68579825,48.3775884166667,'REP' UNION ALL
SELECT 17, 73.954,-68.6863724166667,48.3776909833333,'AG FIN' UNION ALL -- marked
SELECT 18, 0.014,-68.6863024333333,48.3776012666667,'AG DEBUT' UNION ALL
SELECT 18, 0.051,-68.68579825,48.3775884166667,'AG DEBUT' UNION ALL
SELECT 18, 74.954,-68.6863724166667,48.3776909833333,'AG FIN' UNION ALL -- marked
SELECT 19, 0.014,-68.6863024333333,48.3776012666667,'AG DEBUT' UNION ALL
SELECT 19, 0.051,-68.68579825,48.3775884166667,'AG FIN' UNION ALL
SELECT 19, 75.954,-68.6863724166667,48.3776909833333,'AG FIN' UNION ALL -- marked
SELECT 20, 0.407,-69.4326803166667,47.9564388166667,'ZS DEBUT' UNION ALL
SELECT 20, 0.508,-69.4313293333333,47.9567397,'ZS FIN' UNION ALL
SELECT 20, 12.467,-69.3222782666667,48.0301797333333,'REP' UNION ALL -- marked
SELECT 20, 12.886,-69.3191886166667,48.03336905,'TR DEBUT' UNION ALL
SELECT 20, 13.183,-69.3167625666667,48.03553205,'TR FIN' UNION ALL
SELECT 20, 14.012,-69.3098778333333,48.04112755,'REP' UNION ALL -- marked
SELECT 20, 26.209,-69.2005399333333,48.1127254333333,'AG DEBUT'
-------------------------------------
SET @MAR_ID = 20
SET @@Cur_FICH_EVE_BRUT = cursor LOCAL FAST_FORWARD for
SELECT EVE_DIST, EVE_DESC FROM #RRF_FICH_EVE_BRUT WHERE MAR_ID = @MAR_ID ORDER BY EVE_DIST
OPEN @@Cur_FICH_EVE_BRUT
FETCH NEXT FROM @@Cur_FICH_EVE_BRUT INTO @EVE_DIST, @EVE_DESC
set @EVE_DEBUT = -1
set @EVE_FIN = -1
WHILE (@@FETCH_STATUS <> -1) Begin
IF(@EVE_DEBUT = -1)
Begin
IF (LEN(rtrim(@EVE_DESC)) >= 5)
Begin
IF(RIGHT(rtrim(@EVE_DESC), 5) = 'DEBUT')
Begin
set @EVE_DEBUT = @EVE_DIST
End
End
End
ELSE
Begin
IF(@EVE_FIN = -1)
Begin
If(Len(rtrim(@EVE_DESC)) >= 3)
Begin
IF(RIGHT(rtrim(@EVE_DESC), 3) = 'FIN')
Begin
set @EVE_FIN = @EVE_DIST
End
End
End
ELSE
Begin
IF(Len(rtrim(@EVE_DESC)) >= 3)
Begin
IF(RIGHT(rtrim(@EVE_DESC), 3) = 'FIN')
Begin
set @EVE_FIN = @EVE_DIST
End
ELSE
Begin
IF(LEN(rtrim(@EVE_DESC)) >= 5)
Begin
IF(RIGHT(rtrim(@EVE_DESC), 5) = 'DEBUT')
Begin
PRINT CAST(ISNULL(@EVE_DIST, '_') AS VARCHAR(6)) + ' ' + ISNULL(@EVE_DESC, '_') -- ###################
set @EVE_DEBUT = @EVE_DIST
set @EVE_FIN = -1
END
ELSE
Begin
PRINT CAST(ISNULL(@EVE_DIST, '_') AS VARCHAR(6)) + ' ' + ISNULL(@EVE_DESC, '_') -- ###################
set @EVE_DEBUT = -1
set @EVE_FIN = -1
END
End
ELSE
Begin
PRINT CAST(ISNULL(@EVE_DIST, '_') AS VARCHAR(6)) + ' ' + ISNULL(@EVE_DESC, '_') -- ###################
set @EVE_DEBUT = -1
set @EVE_FIN = -1
End
End
End
End
End
FETCH NEXT FROM @@Cur_FICH_EVE_BRUT INTO @EVE_DIST, @EVE_DESC
END
IF(@EVE_DEBUT <> -1 AND @EVE_FIN <> -1)
PRINT CAST(ISNULL(@EVE_DIST, '_') AS VARCHAR(6)) + ' ' + ISNULL(@EVE_DESC, '_') -- ###################
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 2, 2009 at 9:34 am
Jeff Moden (3/2/2009)
There's no special technique for this... it's simply the choice to figure it out using regular ol' set based code. It doesn't require a Tally table or any of the other "tricks" I know... just some straight forward common sense programming. I'm just not gonna try it because of the condition of the data posted.
Good choice.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 3, 2009 at 12:52 am
Chris Morris (3/2/2009)
Jeff Moden (3/2/2009)
There's no special technique for this... it's simply the choice to figure it out using regular ol' set based code. It doesn't require a Tally table or any of the other "tricks" I know... just some straight forward common sense programming. I'm just not gonna try it because of the condition of the data posted.Good choice.
(Chris Morris) - Yes I agree with that good choice...
(Jeff Moden) - I used the "special technique" because the your posts are understandable in general way not just related to the problem, you can make an explanation also for other details and every time with alternative solution ("special technique") ... and I like it for that reason I said "special technique"!
On the other way I can see that we are making the rule "...not gonna try it because of the condition of the data posted" and sure that I agree to enforce it!
Have a nice day both of you my friends!
Regards,
Dugi
March 3, 2009 at 4:26 am
[font="Arial Black"]Dugi... [/font]if I may suggest, it's better to give someone no answer than to give them what might be one of the worst answers for replacing cursors. Temp Tables and While loops are not the answer to replacing cursors. the reason why people want/need to replace cursors is mostly because of performance problems... Temp Table/While loop combinations do not solve that problem.
[font="Arial Black"]Stanley... [/font]if you'd take the time to read the article at the link in my signature line below and post data (you have a lot of it) in the correct format as recommended in that article, you'd get much better help much quicker. We spend a lot of time helping others and we could use the simple help of people formatting data in such a way (correctly formatted INSERT statements) that we don't have to take the 5 or 10 minutes to do it for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply