469,097 Members | 1,600 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,097 developers. It's quick & easy.

How to concat multiple fields in query?

This returns only txt1. Any thoughts?

Columns are (varchar (250),Null).


Expand|Select|Wrap|Line Numbers
  1. select distinct 
  2. employee_name, 
  3. tran_date, 
  4. billed_hrs, 
  5. (txt1 + txt2 + txt3 + txt4 +txt5 + txt6) as Narrative  
  6.  
  7. from tat_time 
  8. left outer join hbm_matter on
  9. tat_time.matter_uno = hbm_matter.matter_uno
  10. left outer join hbm_persnl on
  11. tat_time.tk_empl_uno = hbm_persnl.empl_uno
  12. left outer join tat_text on
  13. tat_time.time_uno = tat_text.source_uno
  14.  
  15. where clnt_matt_code = '013187.0000041'
  16. and wip_status = 'B'
  17. and billed_hrs != 0
  18. order by tran_date
Nov 6 '10 #1
6 3276
gpl
152 100+
What does your data look like ?
Nov 7 '10 #2
try this
......

(isnull(txt1,'') +isnull(txt2,'') + isnull(txt3,'') + isnull(txt4,'')+isnull(txt5,'') + isnull(txt6,'')) as Narrative
Nov 8 '10 #3
The date is narrative in a time and billing system. Once 250 characters are used in txt1, txt2 is used, and so on. Narrative like "Conference with so and so regarding such and such."
Nov 9 '10 #4
ck9663
2,878 Expert 2GB
First, you can actually increase your varchar length more than 250...

In your case, try to isolate the problem first...are all those txt<x> columns coming from a single table? Maybe one or some of those are NULL. Try doing with Sandeep recommended. If you want to isolate your problem, replace the "+" sign with comma, remove the "as Narrative" and the parenthesis, that way you'll know what the value of each of those fields are. If the other txt<x> fields are empty, then it will not be concatenated. You have to check if these columns are properly populated...

Good Luck!!!

~~ CK
Nov 9 '10 #5
Thank you both. But Isnull does not help. With or without isnull, it picks up the first 6 character in txt2 then drops the remainder of txt2 and nothing beyond that. Yes, I can get the full text in each column using commas and concatenate them in Excel. Yes, the columns are all in one table. Can't change the properties of the tables or columns. In some cases, txt2, txt3, txt4, etc may or may not be populated.
Nov 9 '10 #6
It seems the problem is my version of SQL Analyser 8.0 is the culprit. Tried a more elaborate script written by one of our prgrammers:

Expand|Select|Wrap|Line Numbers
  1. 'DESC_COMMENTS'=ISNULL(CONVERT(TEXT,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  2.  
  3.       (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT1)),
  4.  
  5.       CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
  6.  
  7.       Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1)) + 
  8.  
  9.       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT2)),
  10.  
  11.       CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
  12.  
  13.       Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1)) + 
  14.  
  15.       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT3)),
  16.  
  17.       CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
  18.  
  19.       Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1)) + 
  20.  
  21.       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT4)),
  22.  
  23.       CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
  24.  
  25.       Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1)) + 
  26.  
  27.       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT5)),
  28.  
  29.       CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
  30.  
  31.       Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1)) + 
  32.  
  33.       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(TXT6)),
  34.  
  35.       CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
  36.  
  37.       Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1))),
  38.  
  39.       CHAR(10),Space(1)),CHAR(13),Space(1)),CHAR(9),Space(1)),CHAR(10)+CHAR(13),Space(1)),CHAR(13)+CHAR(10),SPACE(1)),
  40.  
  41.       Space(5),Space(1)),Space(4),Space(1)),Space(3),Space(1)),Space(2),Space(1))),'No narrative.')
  42.  
which works on 2005 but still comes up short on 8.0. I'm going to upgrade. Thanks for all your help.
Nov 9 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Ben Willcox | last post: by
2 posts views Thread by PPT33R | last post: by
7 posts views Thread by spamsickle | last post: by
2 posts views Thread by Nathan Sokalski | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.