b18d8b55   
   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)   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)