Author |
Topic  |
|
Stevan2020
Starting Member
25 Posts |
Posted - 12/19/2005 : 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 + '<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 @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 + '<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 @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
USA
4149 Posts |
Posted - 12/19/2005 : 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 - 12/19/2005 : 18:15:38
|
graz,
That works great, thank you very much.
|
Edited by - Stevan2020 on 12/20/2005 18:26:51 |
 |
|
Stevan2020
Starting Member
25 Posts |
Posted - 12/20/2005 : 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 + '<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 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<br>Input By: USA\KT39B3<br><hr> real second response<br>Input By: 2nd<br><hr> Third response<br>Input By: 3rd<br><hr> UMATILLA first response<br>Input By: KT39B3
149 3 minimaq Pendleton CSMPC 2 STANFIELD 97875 First Response more responses<br>Input By: USA\KT39B3<br><hr> real second response<br>Input By: 2nd<br><hr> Third response<br>Input By: 3rd<br><hr> UMATILLA first response<br>Input By: KT39B3
174 3 minimaq Pendleton CSMPC 2 UMATILLA 97882 First Response more responses<br>Input By: USA\KT39B3<br><hr> real second response<br>Input By: 2nd<br><hr> Third response<br>Input By: 3rd<br><hr> UMATILLA first response<br>Input By: KT39B3
206 3 minimaq Portland P&DC 2 YAMHILL 97148 First Response more responses<br>Input By: USA\KT39B3<br><hr> real second response<br>Input By: 2nd<br><hr> Third response<br>Input By: 3rd<br><hr> UMATILLA first response<br>Input By: KT39B3
179 4 minimaq Eugene P&DF 5 VENETA 97487 First Response more responses<br>Input By: USA\KT39B3<br><hr> real second response<br>Input By: 2nd<br><hr> Third response<br>Input By: 3rd<br><hr> UMATILLA first response<br>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
India
44 Posts |
|
|
Topic  |
|
|
|