SQL Query help - Concatenate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query help - Concatenate

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-12-14 : 09:08:32

I'm trying to display new results like this Completed(4)

This first query will display Completed(), but I can't seem to get the total in the ()
SELECT
A.Status, CASE
WHEN B.TOTAl = 4 THEN A.Status + '()'
ELSE 'Unknown' END,
A.StatusID, B.Total
FROM ITClass A
LEFT OUTER JOIN
(
SELECT COUNT(ITStatus) As Total, ITStatus
FROM ITReq
WHERE ITReq.SubDate > '12/1/2005'
GROUP BY ITStatus
) AS B
ON A.StatusID = B.ITStatus

When I try this...I get an error message "error converting the varchar value 'Unknown' to column of data type int

SELECT
A.Status, CASE
WHEN B.TOTAl = 4 THEN A.Status + '(' + B.TOTAl + ')'
ELSE 'Unknown' END,
A.StatusID, B.Total
FROM ITClass A
LEFT OUTER JOIN
(
SELECT COUNT(ITStatus) As Total, ITStatus
FROM ITReq
WHERE ITReq.SubDate > '12/1/2005'
GROUP BY ITStatus
) AS B
ON A.StatusID = B.ITStatus

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 09:11:42

Where do you want to show these data?

You need to cast it to varchar

SELECT
A.Status, CASE
WHEN B.TOTAl = 4 THEN A.Status + '(' + cast(B.TOTAl as varchar)+ ')'
ELSE 'Unknown' END,
A.StatusID, B.Total
FROM ITClass A
LEFT OUTER JOIN
(
SELECT COUNT(ITStatus) As Total, ITStatus
FROM ITReq
WHERE ITReq.SubDate > '12/1/2005'
GROUP BY ITStatus
) AS B
ON A.StatusID = B.ITStatus

Madhivanan

Failing to plan is Planning to fail

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-12-14 : 09:16:37

It's going to be on the web.
Ok, I see what you did. SQL Server thinks I want to try and add a varchar and int together, so you converted the int to varchar.

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-12-14 : 09:17:16

Thank you for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources