| 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 |
|
|
|
COALESCE with other field data