Query doubt

[contracer]

Hi,
Please help me solve my doubt.
When I execute this query:

Select n.caption, ccs.errormessage FROM APM_CurrentComponentStatus ccs
WITH (NOLOCK)
INNER JOIN APM_Application a WITH (NOLOCK) ON ccs.ApplicationID = a.ID
INNER JOIN Nodes n WITH (NOLOCK) ON a.NodeID = n.NodeID
where ccs.ErrorMessage LIKE '%atingiu%'
order by 2 asc

I get:

SERVER1 FS /apl/orawpsp atingiu 90% de utilizacao -> 533 MB
disponiveis -
FS /bd1/orawppdata/sys atingiu 90% de utilizacao ->
199 MB disponiveis -


How could I change this query to get output below:

SERVER1 FS /apl/orawpsp atingiu 90% de utilizacao -> 533 MB
disponiveis -
SERVER1 FS /bd1/orawppdata/sys atingiu 90% de utilizacao -> 199 MB
disponiveis -

Thanks.

[Erland Sommarskog]

So does this query only return a single row, and ccs.errormessage has
text with a line feed in it? That would be the only explanation for
the server name only being listed once. But I am not familiar with
your tables, so I might be missing something.


--
Erland Sommarskog, Stockholm, esq...@sommarskog.se

[rja.ca...@gmail.com]

If the first query is correct and the result is correct, n.caption in
the second row is coming from a row in Nodes where caption is blank ''.
In this case, you would want to write a query that finds another
row in Nodes that has the value in caption that you want to see.
Of course, if this is a matter of joining table Nodes to itself,
you can do that. On the other hand, it might be more complicated.
"Nodes" sounds like a complicated network relationship between rows.
One approach that may work, but may run slowly, is to write a
Transact-SQL user-defined function that takes one NodeID as the
parameter and looks up one row and then another in Nodes according
to some appropriate rule until it finds a caption.

If the blank is NULL, and if the distance from your first node
to a node with a caption is limited, then another approach is to use
several table joins of Nodes and the function COALESCE, such as,
COALESCE(n1.caption, n2.caption, n3.caption, n4.caption, n5.caption)
This returns the first term that isn't NULL.

If the blank is an empty space string, you can change it to NULL
each time and /then/ use COALESCE, such as,
COALESCE(NULLIF(n1.caption, ''), NULLIF(n2.caption, ''),
NULLIF(n3.caption, ''))

NULLIF() returns either its first term, or, if the first and second
terms are the same, it returns NULL.

Alternatively, Erland is right and you've got one data row, that
comes out on two lines of text.

Well... recently I've been using a technique to substitute one string
into several places in another string, by putting in a token string
that is to be replaced with the string that I want to see.

PRINT REPLACE('You do @{pronoun} and @{pronoun} comes out like @{pronoun}.',
'@{pronoun}', 'this')
You do this and this comes out like this.
You do that and that comes out like that.

To put such a token in after a line break in your message, so that
n.caption appears on each line, you could use something like,

REPLACE(
'@{token}' + REPLACE(ccs.errormessage, CHAR(13)+CHAR(10),
CHAR(13)+CHAR(10)+'@{token}')
,
'@{token}', n.caption)