Need help with a cursor (as a possible solution) 2

[TudorSmith]

Hi All. I'm pretty new to cursors...in fact I'm a newbie with them. I think I need to run a cursor for the following exercise. If now please advise, otherwise, could some clever person build one for me?

I have a table with rows of data, where one column is identified as "AddOmitFlag". If the value has 0, then it needs to be omitted.

What I have:
Description AddOmitFlag
MP2 0
MP2 0
MP2 -1

Here you see three entries for an MP2. The omit flag indicates that one of the entries has to be remove. The final outcome will not show the omitted record, and one of the other MP2's will be stripped out.

What I need:
Description AddOmitFlag
MP2 0

It's a cancelling out exercise I figure. Would this be handled in a cursor inasmuch as I'd pass each row into the cursor but do some manipulation to only include items where a subsequent item does not have an entry?

Thanks in advance

birklea


birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[druer]

I'm not sure I understand what you are looking to accomplish. Is the goal to remove records from your table? Is the goal to simply show particular rows? You confuse me by saying that 0 indicates that the row should be ommitted, but then showing that what you want to see is 1 row with the 0 value. Happy to help once I understand what you are going for.

 

[TudorSmith]

Yikes. I knew I'd made it to complicated.

Well I don't want to remove/delete records, just want INSERT rows into a temp table (#Results). In essence, a row with a 0 in the AddOmit column inidcates that we need to omit any row with the same description (MP2) as the record to be omited.

I see it like this (trying to use normal English to description functionality:

FETCH NEXT CURSOR
If AddOmit = 0 Then
@dummyVar = @Description

FETCH NEXT
IF @Description = @dummyVar then
'Don't do anything with this record...it is to be omitted
ELSE
INSERT INTO #Result
@Description

FETCH NEXT

I'm usless at Cursors so anything to can provide to lead me into a direction that will loop through 13 rows, and return only instances of a row that has no duplicate entry with an addomit flag.

See original posting for What I have & What I need

Once I have the results in the #Result table, I can source that on my code and be confident I'm only looking at records that do not have a duplicate with an addomit flag.

Sorry to be so long-winded





birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[gmmastros]

What about...

Select Distinct Description
From TableName
Where AddOmitFlag = 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh, you wanted this in a Temp table, right....

So

Select Distinct Description
Into #Result
From TableName
Where AddOmitFlag = 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TudorSmith]

Thanks. that's not what I'm trying to do though. That will only return 3 rows.

here's a link to a page on my website, showing what I have in my data set, and what I really want:

http://www.tudorsmith.co.uk/cursor.htm

I don't think I can get the results just buy doing a SELECT DISTINCT....





birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[druer]

Now I think we are getting somewhere. I'm confused though in your expected results you do show the first row which has a AddOmitFlag = 0.

Is the following a fair description of what you want:
If an AddOmitFlag 0 value is encountered then
If there is another row for this same description then
don't include the row with the 0
don't include the non-0 row that followed it
Else
There was no other row after it, so show the 0 row

If a description has no AddOmitFlag rows with 0 show them all

 

[TudorSmith]

Hi Druer

Yep...that's a fair assesment. I made a mistake with my image...the second image shows the first row (with a 0) and that shouldn't be there (I was just cutting and pasting).

so:

If an AddOmitFlag 0 value is encountered then
If there is another row for this same description then
don't include the row with the 0
don't include the non-0 row that followed it
Else
Show only rows with a NULL in the AddOmit field

*Phew* Getting there huh?

birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[gmmastros]

I think this can be done without a cursor. My solution involves table variables to temporarily store the data.

[green]-- Setup the data[/green]
Declare @Data Table(Description VarChar(100), ID Integer, AddOmitFlag Integer)

Insert Into @Data Values('MP13', 287, 0)
Insert Into @Data Values('MP13', 1290, NULL)
Insert Into @Data Values('MP13', 215, NULL)

Insert Into @Data Values('MP2', 212, NULL)
Insert Into @Data Values('MP2', 1288, NULL)

[green]-- Create a table variable to store temporary data[/green]
Declare @Temp Table(RowId Integer Identity(1,1), Description VarChar(100), Id Integer, AddOmitFlag Integer)

Insert Into @Temp(Description, Id, AddOmitFlag)
Select Description, Id, AddOmitFlag
From   @Data
[green]-- There should really be an order by here[/green]

[green]-- Get the data to remove from the table variable[/green]
Declare @Deletes Table(RowId1 Integer, RowId2 Integer)

Insert Into @Deletes(RowId1, RowId2)
Select 	A.RowId,
		B.RowId
From 	@Temp A 
		inner Join @Temp B 
			On 	A.Description = B.Description
			And A.RowId = B.RowId - 1
			And A.AddOmitFlag = 0
			And B.AddOmitFlag Is NULL

[green]-- Remove the data from the table variable[/green]
Delete	T
From	@Temp T
		Inner join @Deletes D On T.RowId = D.RowId1

Delete	T
From	@Temp T
		Inner join @Deletes D On T.RowId = D.RowId2

[green]-- Show what's left[/green]
Select * from @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TudorSmith]

Hmm...it looks interesting eorge, but the first few lines are showing the dat ayou are storing in the temp table. It would need to be populated dynamically as it will change with every "run". That's why I thought the CURSOR solution would be best...I could loop through my "source" table and populate the TEMP table...if you see what I mean

birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[gmmastros]

I see what you mean. My post was meant as a starting point. For your final solution, you will need to modify this part...

Insert Into @Temp(Description, Id, AddOmitFlag)
Select Description, Id, AddOmitFlag
From @Data

So that the Temp table is populated from your data, not my @Data table variable.

Ex.

Insert Into @Temp(Description, Id, AddOmitFlag)
Select Description, Id, AddOmitFlag
From <TableName>
Order By <SomeColumn>

I put the @Data stuff in there so I could test the code. In my testing (with the limited amount of data I tested with), it appears as though this should return the correct results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[druer]

I think George was just using the @data table as a way that he could test the results, and you would insert your "real" table in its place in your environment.

 

[TudorSmith]

Okay Dokay! Thanks for that. I'm off to give it a try (now I understand what your saying )

Stars on the way for you both if it works *Tee Hee*

birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[TudorSmith]

Thanks guys,

It all works a treat. I never considered running SQL on the same table with an inner join to itself. Ingenious!!!

Tudor

birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[TudorSmith]

Sorry guys, I'm back again! While the Stored Procedure returns all the results I need, the code running it from the VB application is declaring that my recordset (set up from the call to the SP) is closed. My suspicions are that the SP is closing the record before it is executed.

Here's my VB code:

Set rstGet = New ADODB.Recordset
Set cmdGet = New ADODB.Command
Set cmdGet.ActiveConnection = con
cmdGet.CommandType = adCmdStoredProc
cmdGet.CommandText = "spu_FETCH_TC_PREPBuildingBlock"
cmdGet.CommandTimeout = 10
    With cmdGet
        Set rstGet = .Execute
    End With

[red] FALLS OVER ON NEXT LINE[/red]
    If Not rstGet.EOF Then
        Do While Not rstGet.EOF
            Me.cboPower.AddItem rstGet!Description
            Me.cboPower.ItemData(Me.cboPower.NewIndex) = rstGet!ID
            rstGet.MoveNext
        Loop
    End If

The error message states:

Operation is not allowed when the object is closed.

Any thoughts on why the object is closed? I havn't set the object to be closed so I'm assumeing it's somethign in the SP

Thanks

Tudor

Tudor ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[gmmastros]

This is common. When you write a 'simple' stored procedure (one that only has a select), then you don't see this problem. When you involve table variables or temp tables, this is common. Luckily, there's a simple solution to this. At the beginning of the stored procedure, add the line...

SET NOCOUNT ON

This will solve your problem.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom