COALESCE with other field data

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

Transact-SQL (2000)

COALESCE with other field data

Author

Topic

Stevan2020
Starting Member

25 Posts

Posted - 2005-12-19 : 17:33:50

I am attemptng to use COASESCE to combine multiple records into a Single field of a record (PlantResponse). The code below works fine as is....

Declare @Comment varChar(8000)
SELECT @Comment = COALESCE(@Comment + ' <hr> ', '') +
CAST(PR.Comment as varChar(500)) + ' Input By: ' + InputBy
FROM tbl_MAQ_Plant_Response PR
JOIN tbl_MiniMaq MM ON MM.ReportDate = PR.ReportDate
Where ((MM.OfficeID = PR.OfficeID) and (MM.ReportDate = '12/6/2003'))
Select @Comment as PlantResponse

... However, I also need to return other fields that do not have multiple entries as part of the same record. As soon as I do that
...

Declare @Comment varChar(8000)
SELECT PR.OfficeID, @Comment = COALESCE(@Comment + ' <hr> ', '') +
CAST(PR.Comment as varChar(500)) + ' Input By: ' + InputBy
FROM tbl_MAQ_Plant_Response PR
JOIN tbl_MiniMaq MM ON MM.ReportDate = PR.ReportDate
Where ((MM.OfficeID = PR.OfficeID) and (MM.ReportDate = '12/6/2003'))
Select @Comment as PlantResponse
...

I get this error ...
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
...

Does anyone know haw I can combine the results of COALESCE with other field data into one record?

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-12-19 : 17:52:26

How about this:

Declare @Comment varChar(8000) 
SELECT @Comment = COALESCE(@Comment + '<br><hr> ', '') + 
CAST(PR.Comment as varChar(500)) + '<br>Input By: ' + InputBy
FROM tbl_MAQ_Plant_Response PR 
JOIN tbl_MiniMaq MM ON MM.ReportDate = PR.ReportDate
Where ((MM.OfficeID = PR.OfficeID) and (MM.ReportDate = '12/6/2003'))

Select DISTINCT PR.OfficeID, @Comment as PlantResponse 
FROM tbl_MAQ_Plant_Response PR 
JOIN tbl_MiniMaq MM ON MM.ReportDate = PR.ReportDate
Where ((MM.OfficeID = PR.OfficeID) and (MM.ReportDate = '12/6/2003'))

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Stevan2020
Starting Member

25 Posts

Posted - 2005-12-19 : 18:15:38

graz,

That works great, thank you very much.

Stevan2020
Starting Member

25 Posts

Posted - 2005-12-20 : 18:28:44

I spoke too soon.

When I put this into the rest of the query, it returns all PlantResponse's combined together. These should be different records for different offices (OfficeID). Also all of the records return the PlantResponse when some should be null.

Declare
@Comment varChar(8000),
@dtDate smalldatetime,
@Plant varchar(5),
@Area varchar(5)

Set @dtDate = '12/6/2003'
Set @Plant = '%'
Set @Area = '%'

SELECT @Comment = COALESCE(@Comment + ' <hr> ', '') +
CAST(PR.Comment as varChar(500)) + ' Input By: ' + InputBy
FROM tbl_MAQ_Plant_Response PR
JOIN tbl_MiniMaq MM ON MM.ReportDate = PR.ReportDate
and MM.OfficeID = PR.OfficeID
Where ((MM.OfficeID = PR.OfficeID) and (Convert(varchar(10),MM.[ReportDate],1) = Convert(varchar(10),@dtDate,1) ))

Select distinct MM.OfficeID, O.MgrCode, O.MAQ, P.Plant, S.MpooSort, Z.Office, Z.Zips, @Comment as PlantResponse
FROM tbl_MAQ_Plant_Response PR
join tbl_MiniMaq MM ON MM.ReportDate = PR.ReportDate
join Portland_ClusterDB.dbo.tbl_Offices O ON MM.officeID = O.OfficeID
join Portland_ClusterDB.dbo.tbl_OfficeZips Z ON MM.officeID = Z.OfficeID
join Portland_ClusterDB.dbo.tbl_Plants P on O.PlantMail = P.PlantID
join Portland_ClusterDB.dbo.tbl_SeniorStaff S on O.MgrCode = S.MgrCode
Where Convert(varchar(10),MM.[ReportDate],1) = Convert(varchar(10),@dtDate,1) and S.MgrCode like Convert(varchar(5),@Area,1) and P.PlantID like Convert(varchar(5),@Plant,1)
Order by S.MpooSort, Office

Results:
OfficeID MgrCode MAQ Plant Sort Office Zip
25 3 minimaq Portland P&DC 2 BEAVERCREEK 97004
PlantResponse
First Response more responses Input By: USA\KT39B3 <hr> real second response Input By: 2nd <hr> Third response Input By: 3rd <hr> UMATILLA first response Input By: KT39B3

149 3 minimaq Pendleton CSMPC 2 STANFIELD 97875
First Response more responses Input By: USA\KT39B3 <hr> real second response Input By: 2nd <hr> Third response Input By: 3rd <hr> UMATILLA first response Input By: KT39B3

174 3 minimaq Pendleton CSMPC 2 UMATILLA 97882
First Response more responses Input By: USA\KT39B3 <hr> real second response Input By: 2nd <hr> Third response Input By: 3rd <hr> UMATILLA first response Input By: KT39B3

206 3 minimaq Portland P&DC 2 YAMHILL 97148 First Response more responses Input By: USA\KT39B3 <hr> real second response Input By: 2nd <hr> Third response Input By: 3rd <hr> UMATILLA first response Input By: KT39B3

179 4 minimaq Eugene P&DF 5 VENETA 97487 First Response more responses Input By: USA\KT39B3 <hr> real second response Input By: 2nd <hr> Third response Input By: 3rd <hr> UMATILLA first response Input By: KT39B3

Stanfield ahould have the first 3 responses
Umatilla should have its first response only
Beavercreek, Yamhill and Veneta should be null

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 09:30:39

Hi

Actually this in the group of small errors consuming time

Just have aook on my blog

http://sqlerrormessages.blogspot.com/2009/08/msg-141-select-statement-that-assigns.html

Happy Programming

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources