Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Need to do a Select on a Dynamic Result Set

Author  Topic 

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2003-03-05 : 11:26:39
I have this rather lenghty SPROC that creates a dynamic SQL-Select statement and EXECUTE()'s it. This works fine and ends up creating a "ResultSet" (or Cursor) that contains multiple records for multiple ClientCodes. After this I need to select the last record for each client from that ResultSet returning the NewResultSet...

 
SELECT * FROM ResultSet AS TA WHERE TrackDataIDKEY = 
  (Select MAX(TrackDataIDKEY) FROM ResultSet AS TB
   WHERE TB.ClientCode = TA.ClientCode)


How do I do this? Can I somehow use DECLARE CURSOR on the dynamic SQL-Select? Here is the SPROC code...

CREATE PROCEDURE Get_TrackData_By_Company
@ClientCode nchar(8),  -- Return records related to a certain unique Client Code only. Blank = All
@SerialNumber nchar(20), -- Return records related to a certain unique Machine Serial Number only. Blank = All
@Start_DateTime nchar(22), -- Beginning Date/Time Range. Required Parameter
@End_DateTime nchar(22), -- Ending Date/Time Range. Required Parameter.
@Region nchar(2),  -- Return records related to a specific Region (N,E,S,W,NE,SE,NW,NE,F (Foreign)). Blank = All
@ErrorLevel nchar, -- Return Records for selected Error Level (0=Green, 1=Yellow, 2=Red)
@HighestError nchar, -- Return Highest ErrorLevel (Logical True/False)
@LastError nchar, -- Return Last Error Only (Logical True/False)
@ClearedErrors nchar -- 0 = Ignore "Cleared" setting, 1 = Return "Uncleared" Errors only, 2 = Return "Cleared" Errors Only
AS

DECLARE @SQLString nvarchar(4000)

SET @SqlString =
	'Select 
	TrackData.TrackDataIDKey,	
	Clients.ClientCode,Clients.Company,Clients.Addr1,Clients.Addr2,Clients.City,Clients.State,Clients.Zip,
	Clients.Region,Clients.Contact,Clients.Contact_Phone,Clients.ServiceMgr,Clients.ServicePhone,	Machines.SerialNumber,	Machines.Machine_Type,MachineTypes.Description AS Machine_Type_Desc,
	Versions.Version as FirmWare_Version,
	TrackData.ErrorCode,
	ErrorCodes.Description AS Error_Description,
	ErrorCodes.ErrorLevel,	
	TrackData.Time_Stamp,
	TrackData.Text AS Client_Text,
	TrackData.Cleared_By,
	TrackData.Cleared_On,
	TrackData.ErrorLevel AS Current_Error_Level'

SET @SQLString = @SQLString + 
	' FROM TRACKDATA 
	LEFT JOIN CLIENTS ON Clients.ClientCode = TrackData.ClientCode 
	LEFT JOIN MACHINES ON Machines.SerialNumber = TrackData.SerialNumber 
	LEFT JOIN MACHINETYPES ON Machines.Machine_Type = MachineTypes.Machine_Type 
	LEFT JOIN ERRORCODES ON ErrorCodes.Machine_Type = MachineTypes.Machine_Type 
		AND ErrorCodes.ErrorCode = TrackData.ErrorCode 
	LEFT JOIN VERSIONS ON Versions.VersionIDKey = Machines.Firmware_Code '
	
SET @SQLString = @SQLString + '
	WHERE Machines.Active = 1 AND Time_Stamp BETWEEN '+CHAR(39)+@Start_DateTime+CHAR(39)
	+' AND '+CHAR(39)+@End_DateTime+CHAR(39)

IF LEN(@ClientCode) > 0
	SET @SQLString = @SQLString + '
	AND TrackData.ClientCode = '+CHAR(39)+@ClientCode+CHAR(39)

IF LEN(@SerialNumber) > 0
	SET @SQLString = @SQLString + '
	AND TrackData.SerialNumber = '+CHAR(39)+@SerialNumber+CHAR(39)

IF LEN(@Region) > 0
	SET @SQLString = @SQLString + '
	AND Clients.Region = '+CHAR(39)+@Region+CHAR(39)

IF @ErrorLevel  > '0'
	SET @SQLString = @SQLString + '
	AND TrackData.ErrorLevel >= '+@ErrorLevel

IF @ClearedErrors > '0'
	BEGIN
	SET @SQLString = @SQLString + '
		AND LEN(LTRIM(TrackData.Cleared_By)) '
	IF @ClearedErrors = '1'
		SET @SQLString = @SQLString + ' = 0 OR TrackData.Cleared_By IS NULL'
	ELSE
		SET @SQLString = @SQLString + ' > 0'
	END


SET @SQLString = @SQLString + ' 
	ORDER BY Clients.Company, Clients.ClientCode, Trackdata.Time_Stamp DESC'


PRINT @SQLString
--PRINT LEN(@SQLString)

EXECUTE(@SqlString)
GO


Thanks in advance!
Ken

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2003-03-05 : 11:38:50
Take a look at the INSERT...EXEC...syntax.

Jay White
{0}
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-03-05 : 11:41:35
Why is dynamic SQL being used?


Why not:

Select
TrackData.TrackDataIDKey,
Clients.ClientCode,Clients.Company,Clients.Addr1,Clients.Addr2,Clients.City,Clients.State,Clients.Zip,
Clients.Region,Clients.Contact,Clients.Contact_Phone,Clients.ServiceMgr,Clients.ServicePhone, Machines.SerialNumber, Machines.Machine_Type,MachineTypes.Description AS Machine_Type_Desc,
Versions.Version as FirmWare_Version,
TrackData.ErrorCode,
ErrorCodes.Description AS Error_Description,
ErrorCodes.ErrorLevel,
TrackData.Time_Stamp,
TrackData.Text AS Client_Text,
TrackData.Cleared_By,
TrackData.Cleared_On,
TrackData.ErrorLevel AS Current_Error_Level'

FROM TRACKDATA
LEFT JOIN CLIENTS ON Clients.ClientCode = TrackData.ClientCode
LEFT JOIN MACHINES ON Machines.SerialNumber = TrackData.SerialNumber
LEFT JOIN MACHINETYPES ON Machines.Machine_Type = MachineTypes.Machine_Type
LEFT JOIN ERRORCODES ON ErrorCodes.Machine_Type = MachineTypes.Machine_Type
AND ErrorCodes.ErrorCode = TrackData.ErrorCode
LEFT JOIN VERSIONS ON Versions.VersionIDKey = Machines.Firmware_Code

WHERE Machines.Active = 1 AND Time_Stamp BETWEEN @Start_DateTime AND @End_DateTime
AND TrackData.ClientCode Like ISNULL(@ClientCode, '%')
AND TrackData.SerialNumber LIKE ISNULL(@SerialNumber, '%')
AND Clients.Region LIKE ISNULL(@Region,'%')
AND TrackData.ErrorLevel >= @ErrorLevel

-- not sure about your "cleared errors" criteria
-- explain that part in english and I can translate for you

ORDER BY Clients.Company, Clients.ClientCode, Trackdata.Time_Stamp DESC


or something along those lines, with minor changes?

- Jeff
Go to Top of Page

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2003-03-05 : 12:10:12
Thanks Jeff.

I think you are right about dropping dynamic. It never occured to me to use ISNULL like that. I will try it.

The "ClearedErrors" parameter refers to a Cleared_By char field that may contain null, blank, or some text. If ClearedErrors is '0' then it's supposed to ignore the value, if ClearedErrors is '1' then it should only select records that the field value is blank or null, or if ClearedErrors is '2' then it should only select field values that contain text.

Still, how can I do an additional select on this resultset?

Thanks, Ken
Go to Top of Page

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2003-03-05 : 14:53:07
Ok, I got rid of the dynamic. Now, how can I select the top record from each client when @LastError parameter = 1 from the resulting recordset? Here's the new code...


CREATE PROCEDURE Get_TrackData_By_Company
@ClientCode nchar(8),  -- Return records related to a certain unique Client Code only. Blank = All
@SerialNumber nchar(20), -- Return records related to a certain unique Machine Serial Number only. Blank = All
@Start_DateTime nchar(22), -- Beginning Date/Time Range. Required Parameter
@End_DateTime nchar(22), -- Ending Date/Time Range. Required Parameter.
@Region nchar(2),  -- Return records related to a specific Region (N,E,S,W,NE,SE,NW,NE,F (Foreign)). Blank = All
@ErrorLevel tinyint, -- Return Records for selected Error Level (0=Green, 1=Yellow, 2=Red)
@HighestError tinyint, -- Return Highest ErrorLevel (Logical True/False)
@LastError tinyint, -- Return Last Error Only (Logical True/False)
@ClearedErrors tinyint -- 0 = Ignore “Cleared” setting, 1 = Return “Uncleared” Errors only, 2 = Return “Cleared” Errors Only
AS


DECLARE @vClientCode varchar(8),
	@vSerialNumber varchar(20),
	@vRegion varchar(2)

SET @ClientCode = ISNULL(@ClientCode,SPACE(8))
SET @vClientCode = '%'
IF @ClientCode  != SPACE(8)
	SET @vClientCode = ISNULL(@ClientCode,'%')

SET @SerialNumber = ISNULL(@SerialNumber,SPACE(20))
SET @vSerialNumber = '%'
IF @SerialNumber != SPACE(20)
	SET @vSerialNumber = ISNULL(@SerialNumber,'%')

SET @Region = ISNULL(@Region,SPACE(2))
SET @vRegion = '%'
IF @Region != SPACE(2)
	SET @vRegion = ISNULL(@Region,'%')

Select 
	TrackData.TrackDataIDKey,	
	Clients.ClientCode,Clients.Company,Clients.Addr1,Clients.Addr2,Clients.City,Clients.State,Clients.Zip,
	Clients.Region,Clients.Contact,Clients.Contact_Phone,Clients.ServiceMgr,Clients.ServicePhone,	Machines.SerialNumber,	Machines.Machine_Type,MachineTypes.Description AS Machine_Type_Desc,
	Versions.Version as FirmWare_Version,
	TrackData.ErrorCode,
	ErrorCodes.Description AS Error_Description,
	ErrorCodes.ErrorLevel,	
	TrackData.Time_Stamp,
	TrackData.Text AS Client_Text,
	TrackData.Cleared_By,
	TrackData.Cleared_On,
	TrackData.ErrorLevel AS Current_Error_Level
FROM TRACKDATA 
	LEFT JOIN CLIENTS ON Clients.ClientCode = TrackData.ClientCode 
	LEFT JOIN MACHINES ON Machines.SerialNumber = TrackData.SerialNumber 
	LEFT JOIN MACHINETYPES ON Machines.Machine_Type = MachineTypes.Machine_Type 
	LEFT JOIN ERRORCODES ON ErrorCodes.Machine_Type = MachineTypes.Machine_Type 
		AND ErrorCodes.ErrorCode = TrackData.ErrorCode 
	LEFT JOIN VERSIONS ON Versions.VersionIDKey = Machines.Firmware_Code 
	
WHERE Machines.Active = 1 AND Time_Stamp BETWEEN @Start_DateTime AND @End_DateTime
	AND TrackData.ClientCode LIKE @vClientCode
	AND TrackData.SerialNumber LIKE  @vSerialNumber
	AND Clients.Region LIKE @vRegion
	AND TrackData.ErrorLevel >= @ErrorLevel
	AND (@ClearedErrors = 0 OR (@ClearedErrors = 1 AND TrackData.Cleared_On  IS NULL) OR (@ClearedErrors = 2 AND TrackData.Cleared_On IS NOT NULL))

ORDER BY Clients.Company, Clients.ClientCode, Trackdata.Time_Stamp DESC
GO


Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-03-05 : 15:17:06
So, I think what you are saying if @LastError = 1, then return only records where TrackData.Time_Stamp is the max for each client?

Add this to your WHERE clause:

AND
((@LastError = 0) OR
TrackData.Time_Stamp = (SELECT Max(Time_stamp) FROM TrackData TD WHERE TD.ClientCode = Clients.ClientCode))



- Jeff

Edited by - jsmith8858 on 03/05/2003 15:17:40
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-03-05 : 15:19:55
Also: note that this line in your criteria


AND Clients.Region LIKE @vRegion




is negating the LEFT OUTER JOIN from trackdata to clients because
that criteria does not allow for Clients.Region to be null.

(When I say negating I mean it is making it an INNER JOIN.)

- Jeff

Edited by - jsmith8858 on 03/05/2003 15:23:34
Go to Top of Page

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2003-03-06 : 09:31:11
Excellent! That is exactly what I was looking for.
Thanks so much Jeff!

Ken
Go to Top of Page

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2003-03-06 : 10:01:45
Just one more thing Jeff, what if I want to select the last highest error? Sometimes these will not be the same TrackData record.

I have code like this now...

Select
TrackData.TrackDataIDKey,
Clients.ClientCode,Clients.CorpCode,Clients.Company,Clients.Addr1,Clients.Addr2,Clients.City,Clients.State,Clients.Zip,
Clients.Region,Clients.Contact,Clients.Contact_Phone,Clients.ServiceMgr,Clients.ServicePhone,
Machines.SerialNumber, Machines.Machine_Type,MachineTypes.Description AS Machine_Type_Desc,
Versions.Version as FirmWare_Version,
TrackData.ErrorCode,
ErrorCodes.Description AS Error_Description,
ErrorCodes.ErrorLevel,
TrackData.Time_Stamp,
TrackData.Text AS Client_Text,
TrackData.Cleared_By,
TrackData.Cleared_On,
TrackData.ErrorLevel AS Current_Error_Level

FROM TRACKDATA
LEFT JOIN CLIENTS ON Clients.ClientCode = TrackData.ClientCode
LEFT JOIN MACHINES ON Machines.SerialNumber = TrackData.SerialNumber
LEFT JOIN MACHINETYPES ON Machines.Machine_Type = MachineTypes.Machine_Type
LEFT JOIN ERRORCODES ON ErrorCodes.Machine_Type = MachineTypes.Machine_Type
AND ErrorCodes.ErrorCode = TrackData.ErrorCode
LEFT JOIN VERSIONS ON Versions.VersionIDKey = Machines.Firmware_Code

WHERE Clients.Active = 1 AND Machines.Active = 1
AND Time_Stamp BETWEEN @Start_DateTime AND @End_DateTime
AND TrackData.ClientCode LIKE @vClientCode
AND TrackData.SerialNumber LIKE @vSerialNumber
AND Clients.Region LIKE @vRegion
AND TrackData.ErrorLevel >= @ErrorLevel
AND (@ClearedErrors = 0 OR (@ClearedErrors = 1 AND TrackData.Cleared_On IS NULL) OR (@ClearedErrors = 2 AND TrackData.Cleared_On IS NOT NULL))
AND ((@LastError = 0) OR
TrackData.Time_Stamp = (SELECT MAX(Time_Stamp) FROM TrackData TD WHERE
TD.ClientCode = Clients.ClientCode))
AND ((@HighestError = 0) OR
TrackData.ErrorLevel = (SELECT MAX(ErrorLevel) FROM TrackData TD WHERE
TD.ClientCode = Clients.ClientCode))

ORDER BY Clients.Company, Clients.ClientCode, Trackdata.Time_Stamp DESC
GO
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-03-06 : 11:50:11
Add:

TrackData.TimeStamp = (SELECT MAX(TimeStamp) FROM TrackData)

- Jeff
Go to Top of Page

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2003-03-07 : 11:02:41
I already have that in there. The problem is this..

TrackData
RecordID ClientCode Time_Stamp Error_Level
505 TEST 1 3/1/03 11:00 2
492 TEST 1 3/1/03 10:00 3
480 TEST 1 3/1/03 09:00 3

This would result in nothing selected, because the MAX(Time_Stamp) would be for record 505, and the MAX(Error_Level) would be for record 492.

Also, there is another machines parent table to TrackData. How can I select all of the machines along with their "last highest error"? I need all of the machines listed regardless if they have a child record or not, so I am think Left Outer Join. However when I use a WHERE to select the Last Highest Error that negates my LEFT OUTER JOIN. How do I get around that?

Thanks again...
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-03-07 : 11:19:04
Try adding the error level criteria to that subquery.

TrackData.TimeStamp = (SELECT MAX(TimeStamp) FROM TrackData WHERE TrackData.ErrorLevel >= @ErrorLevel )


As for the other deal with all possible machines, I will get back to you on that one. I think your entire query needs to be restructured and parts moved into a subquery.

- Jeff
Go to Top of Page

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2003-03-07 : 13:11:57
Thanks Jeff. This works as long as the TrackData child table has data...

[code]
CREATE PROCEDURE Get_Machines
@ActiveOnly tinyint
AS
Select
Machines.SerialNumber,Machines.Machine_Type,MachineTypes.Description AS Machine_Type_Desc,
Versions.Version as FirmWare_Version,Machines.ClientCode,
Clients.CorpCode,Clients.Company,Clients.Addr1,Clients.Addr2,Clients.City,Clients.State,Clients.Zip,
Clients.Region,Clients.Contact,Clients.Contact_Phone,Clients.ServiceMgr,Clients.ServicePhone,
TrackData.TrackDataIDKey,TrackData.Time_Stamp,TrackData.ErrorLevel
FROM MACHINES
LEFT OUTER JOIN MACHINETYPES ON MachineTypes.Machine_Type = Machines.Machine_Type
LEFT OUTER JOIN VERSIONS ON Versions.VersionIDKey = Machines.Firmware_Code
LEFT OUTER JOIN CLIENTS ON Clients.ClientCode = Machines.ClientCode
LEFT OUTER JOIN TrackData ON TrackData.SerialNumber = Machines.SerialNumber
WHERE
(@ActiveOnly = 0 OR Machines.Active = 1) AND
-- Get Last Highest Uncleared Error
TrackData.TrackDataIDKey IN(SELECT TOP 1TrackDataIDKey From TrackData TM
WHERE TM.SerialNumber = Machines.SerialNumber
AND TM.Cleared_On IS NULL
ORDER BY TM.ErrorLevel DESC, TM.Time_Stamp DESC)
ORDER BY MACHINES.SerialNumber
GO
[/code
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site