Simple But Serious - Please Help
-
Hi. I'm moving data from one table to another getting getting only the max dated record from each group of records ... using a Stored Procedure. The problem is that the processing works as data comes in as:
Declare @Field1 char(12)
Declare @Field2 char(7)
Declare @Field3 char(12)
Declare @Field4 varchar(11)
Declare @Field5 char(4)
Declare @Field6 int
Fetch Returns a -1
The problem seems to occur after all Field1, 11 Position, Numeric data comes through (11111111111) and the first 12 Position, Alphanumeric data comes through (11111111111A).
Any idea what is wrong?
-
It's not clear to me what you're trying to achieve. Pls explain or post some code.
Obviously you're using a cursor (there is a FETCH). Why not
INSERT target_table SELECT xxx FROM source_table GROUP BY...
-
I agree you shouldn't be using a cursor as a set based method can easily perform. On top of that if the other table is like a snapshot of the data you probably could just save time and effort by replacing it with a view even. Please post details about what you are doing and what the results is supposed to be along with DDL for the tables.
-
The master table has more than one record for a person so I only want the one with the max date which is why I am using a cursor ... the problem is that the last record and only the last record is not put into the target table and I am getting a -1 in my fetch return code....any idea what is up?
/* Declare variables */
DECLARE @MaximumDate char(8)
DECLARE @age char(7)
DECLARE @Name char(12)
/* Declare Control Break Variables */
DECLARE @Break_MaximumDate char(8)
DECLARE @Break_age char(7)
DECLARE @Break_Name Char(12)
DECLARE INPUTCursor CURSOR FOR
SELECT Name, age, MaximumDate FROM INPUT_Data
ORDER BY Name, age, MaximumDate
OPEN INPUTCursor
FETCH NEXT FROM INPUTCursor
INTO @Name, @age, @MaximumDate
/* Check @@FETCH_STATUS to see if there are any more rows to fetch. */
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Break_Name = @Name
SET @Break_MaximumDate = @MaximumDate
SET @Break_age = @age
/* Concatenate and display the current values in the variables. */
PRINT 'Data '+ @Break_Name + ' '+ @Break_age +' ' + CAST(@Break_MaximumDate AS char(8))
/* This is executed as long as the previous fetch succeeds. */
FETCH NEXT FROM INPUTCursor
INTO @Name, @age, @MaximumDate
END
PRINT @@Fetch_Status
CLOSE INPUTCursor
DEALLOCATE INPUTCursor
GO
-
DECLARE @MaximumDate char(8) ?? Why not use smalldatetime ??
Is MaximumDate in the table/view also defined char ?? if yes,you'll have to take care of it's format regarding max/min-order !
whe perform the unneeded cast in your print-statement.
PRINT 'Data '+ @Break_Name + ' '+ @Break_age +' ' + CAST(@Break_MaximumDate AS char(8))
variable @Break_MaximumDate has been defined char(8) at declare time !!
From what I see in this sql, you would be better off with not using the cursor, but use a simple select. (maybe include cast/convert to alter datatype or length
SELECT 'Data ' + Name + ' ' + age + ' '+ MaximumDate as MyConcatenatedStuff
FROM INPUT_Data
ORDER BY Name, age, MaximumDate
If you want to use max(MaximumDate) grouped on Name, you'll have to alter the query. But this jus as a sidenote.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
What select ? ... we get duplicat records for a name (different ages) with SQL Select which is why we are using a cursor. Can you give me the SELECT you are thinking about.
-
Re: Selecting records without using a cursor.
This query gives you the true maximum date for each name. Working assumption: the MaximumDate field is formatted correctly for processing -- FORMAT: YYYYMMDD. If not, you should format it that way, or convert it to a date-time.
SELECT Name, MAX(MaximumDate) AS MaximumDate
FROM INPUT_Data
ORDER BY Name
GROUP BY Name
Now, if you need the entire record from INPUT_Data:
SELECT * FROM INPUT_Data AS input
, (
SELECT Name, MAX(MaximumDate) AS MaximumDate
FROM INPUT_Data
ORDER BY Name
GROUP BY Name
) AS finder
WHERE input.Name = finder.Name
AND input.MaximumDate = finder.MaximumDate
Again, there's a working assumption: There is only one occurance in the INPUT_Data for each entry in finder.
Regards,Bob Monahon
-
sorry for the delay.... been out for the long weekend
Like Bob Monahon replied : this query will give you only the intersected rows.
SELECT 'Data ' + I.Name + ' ' + I.age + ' '+ I.MaximumDate as MyConcatenatedStuff
FROM INPUT_Data I
inner join (SELECT Name, MAX(MaximumDate) AS Max_MaximumDate
FROM INPUT_Data
GROUP BY Name 
Mon I.Name = M.Name
and I.MaximumDate = M.Max_MaximumDate
ORDER BY MyConcatenatedStuff
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank You!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply