Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1473

Re: Query doubt

Show all headers | View raw

On Thursday, 9 May 2013 02:33:26 UTC+1, contracer  wrote:
> 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.

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)

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Query doubt contracer <contracer11@gmail.com> - 2013-05-08 18:33 -0700
  Re: Query doubt Erland Sommarskog <esquel@sommarskog.se> - 2013-05-09 10:31 +0200
  Re: Query doubt rja.carnegie@gmail.com - 2013-05-09 13:56 -0700

csiph-web