asked on

Sql Query

I'm having a problem getting this query to work correctly. What I really want to do is to be able to concat the OIDValue colum where Traps.TrapID = TrapVarbinds.TrapID Is there a better way to write this query? What I'm currently getting is a time from creation entry returned for my OIDValue AS Message column

Any help would greatly be appreciated.

SET NOCOUNT ON
Declare @OIDValue varchar(8000)
Select DISTINCT @OIDValue = ISNULL(@OIDValue + ' ','') + OIDValue from trapvarbinds,traps where trapvarbinds.trapid = traps.trapid 
 
set ROWCOUNT 25
SELECT DISTINCT 
Traps.DateTime AS Date_Time,
Traps.Hostname AS HostName,
@OIDValue AS Message,
Traps.Tag AS SNMP_Tag,
Traps.TrapID AS Trap_ID
 
FROM 
Traps, Trapvarbinds  
WHERE 
 
(
  (Traps.Tag = 'Alert') 
)
AND
(
   (Traps.TrapID=TrapVarbinds.TrapID)
)
ORDER BY DateTime DESC, Message DESC

Open in new window

reb73

I suppose you could create a scalar UDF (User defined function) and invoke it in the SELECT list as indicated below -

/*
CREATE FUNCTION [dbo].[fn_GetMessageByTrapId] (@TrapID int, @Tag varchar(100))
RETURNS varchar(8000)
AS
BEGIN
	DECLARE @OIDValue varchar(8000)
	SELECT DISTINCT @OIDValue = ISNULL(@OIDValue + ' ','') + OIDValue
	FROM Traps T
	INNER JOIN Trapvarbinds TV ON TV.TrapID = T.TrapID
	WHERE	T.TrapID= @TrapId
	AND	T.Tag	= @Tag
	
	RETURN @OIDValue
END
*/
 
DECLARE @Tag varchar(100)
SET     @Tag = 'Alert'
 
SELECT DISTINCT TOP 25 
	Traps.DateTime AS Date_Time,
	Traps.Hostname AS HostName,
	[dbo].[fn_GetMessageByTrapId] (Traps.TrapID, @Tag) AS Message,
	Traps.Tag AS SNMP_Tag,
	Traps.TrapID AS Trap_ID
FROM 
	Traps
INNER JOIN
	Trapvarbinds ON TrapVarbinds.TrapID = Traps.TrapID
WHERE 
	Traps.Tag = @Tag
ORDER BY
	DateTime DESC, Message DESC

Open in new window

cybersapp

ASKER

My original problem - explained
The problem with this query is that it returns multiple rows for each row it concatenates. Example (there are up to 6 entries in the TrapVarbinds table for each alerts. I want to concatenate the OIDValue into one row then present that information as one row as opposed to 6 rows of the same data matched on the TrapID.). How can I get the query to only return one row for each trapID and concatenated OIDValue?

-- problem with current query -
The results I am getting, runs correctly once and then returns just the system uptime not sure why the message (OIDVALUE) is not returning the concatenated rows.

-- reb73 suggestion
The function works but it is also returning the same results as my query where it works once but doesn't work on sequential executions

reb73

Can you provide sample output from Traps and Trapvarbinds tables for a particular tag and trapid?

I've changed the function and the query slightly below which may work, but looking at sample data may help clarify your requirements better..

/*
CREATE FUNCTION [dbo].[fn_GetMessageByTrapId] (@TrapID int, @Tag varchar(100))
RETURNS varchar(8000)
AS
BEGIN
        DECLARE @OIDValue varchar(8000)
        SELECT DISTINCT @OIDValue = ISNULL(@OIDValue + ' ','') + OIDValue
        FROM Trapvarbinds TV 
        WHERE   TV.TrapID = @TrapId
        AND EXISTS (SELECT NULL FROM Traps T WHERE T.Tag = @Tag AND T.TrapID = TV.TrapID)
 
        RETURN @OIDValue
END
*/
 
DECLARE @Tag varchar(100)
SET     @Tag = 'Alert'
 
SELECT DISTINCT TOP 25 
        Traps.DateTime AS Date_Time,
        Traps.Hostname AS HostName,
        [dbo].[fn_GetMessageByTrapId] (Traps.TrapID, @Tag) AS Message,
        Traps.Tag AS SNMP_Tag,
        Traps.TrapID AS Trap_ID
FROM 
        Traps
WHERE 
        Traps.Tag = @Tag
ORDER BY
        DateTime DESC, Message DESC

Open in new window

cybersapp

ASKER

I've attached a sample table entry from TrapVarBinds and Traps

and I've attached sample results from your suggested solution query
TRAPVARBINDS-TABLE-FOR-ONE-ALERT.doc

ASKER CERTIFIED SOLUTION

reb73

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cybersapp

ASKER

The function approach really solved my problem. I didn't quite understand how the function worked in ms sql, but after this answer, I can understand the syntax.