Parse Field for Primary/Foreign key relational elements

[JKDeveloper0718]

We are running some third party software at our NOC and we need to produce a report of alerts and the specific nodes associated to that alert. Well you would think that the alert and the node table would have a nice relational element that could make that happen. Well I found that there is a field in the Alert table called netobject and it containd a sting with the nodesID's as follows: ,1,14,16,

These are the NodeID's in the Nodes table and they each have a nice caption of the node name which we need to see in a report. My question is can I parse these and then get the relation somehow or should I run a cursor and put the relational elements into a temp table where I can produce the SQL that I need. We have a one to many sinerio here and Im not sure of the best approach but Im very good with cursors and T-SQL so I can produce what I want from this. Any suggestions would be appreciated.

 

[nomadicalloy]

I do not have the exact solution but you can create a user defined function to parse your "node"

-----------------------------------------------

CREATE FUNCTION [dbo].[fn_MVParam]
(
-- Add the parameters for the function here
@RepParam nvarchar(4000),
@Delim char(1)=','
)
RETURNS @Values TABLE (Param nvarchar(4000))
AS
Begin
Declare @chrind INT
Declare @Piece nvarchar(4000)
Select @chrind=1
While @chrind > 0
Begin
Select @chrind=CharIndex(@Delim,@RepParam)
If @chrind > 0
Begin
Select @Piece = Left(@RepParam,@chrind-1)
End
Else
Begin
Select @Piece=@RepParam
End

Insert @Values(Param) Values(@Piece)
Select @RepParam = Right(@RepParam,Len(@RepParam)-@chrind)

If Len(@RepParam) = 0 Break
End
Return
End

-----------------------------------------------

example
select * from dbo.fn_MVParam ((select top 1 node from yournode table),',')






This is Signature

http://www.xpherion.com/

 

[JKDeveloper0718]

I have the function implemented and if I add a string to this function like ',0,12,23, it works fine in parsing the string but when I try to add the select statement like below it will not run at all
select * from dbo.fn_MVParam ((select top 1 node from yournode table),',')

my sample command is select * from dbo.fn_MVParam ((select top 1 NetObjects from Alerts),',')


NetObjects contains the string ',0,12,13,' I just cant get any sql within that. Any thoughts

 

[nomadicalloy]

It worked for me in sql 2005, what error you are getting, can you show me your Alerts table (data)?



This is Signature

http://www.xpherion.com/

 

[JKDeveloper0718]

this works fine

select * from dbo.fn_MVParam ('9,3,4,6',',')

but there is a table called Alerts

select * from dbo.fn_MVParam ((Select NetObjects from Alerts),',')

NetObjects is the field in the alerts table that is a text field. this field contains the string (,12,13,45,46,) the following error is

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

I would like the output to parse the table field and the alert ID to go along with it for example

12 12,13,45,46


AlertID NetObject
12 12
12 13
12 45
12 46

this is the net output and then I can take that statement and link it to the nodes table by the netobject id.

 

[philhege]

Take a look at this code:
--create sample data
create table AlertsObjects
(AlertID int, NetObjects varchar(128))

create table nodes
(ObjectID int, ObjectName varchar(128))

GO

INSERT INTO AlertsObjects
SELECT 12,'12,13,45,46'



INSERT INTO Nodes
SELECT 12,'One'
UNION
SELECT 13,'Two'
Union
SELECT 45,'Three'
UNION
SELECT 46,'Four'


GO
--create function
IF EXISTS (SELECT routine_name from INFORMATION_SCHEMA.routines
WHERE routine_name = 'udf_MVParam')
DROP FUNCTION dbo.udf_MVParam
GO
CREATE FUNCTION [dbo].[udf_MVParam]
(
-- Add the parameters for the function here
@ParentVal int,
@RepParam nvarchar(4000),
@Delim char(1)=','

)
RETURNS @Values TABLE (ParentVal int,Param nvarchar(4000))
AS
Begin
Declare @chrind INT
Declare @Piece nvarchar(4000)
Select @chrind=1
While @chrind > 0
Begin
Select @chrind=CharIndex(@Delim,@RepParam)
If @chrind > 0
Begin
Select @Piece = Left(@RepParam,@chrind-1)
End
Else
Begin
Select @Piece=@RepParam
End

Insert @Values(ParentVal,Param) Values(@ParentVal,@Piece)
Select @RepParam = Right(@RepParam,Len(@RepParam)-@chrind)

If Len(@RepParam) = 0 Break
End
Return
End

GO

--retrieve the info

DECLARE @fieldval varchar(4000), @parentval int

select *, 0 as processed into #what
FROM AlertsObjects

select top 0 0 as alert,0 as alertedobject into #output

WHILE (select count(*) from #what WHERE processed = 0) > 0
BEGIN
SELECT TOP 1 @parentval = alertID, @fieldval = NetObjects
FROM #what
WHERE processed = 0
INSERT INTO #output
select * from dbo.udf_MVParam(@parentval,@fieldval,',')


UPDATE #what SET processed = 1
WHERE alertID = @parentval
AND Netobjects = @fieldval
END
select * from #output
JOIN Nodes on objectID = alertedobject
drop table #what
drop table #output

You can tweak the code to join with the object table and get the info you need.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.