SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 COALESCE with other field data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Stevan2020
Starting Member

25 Posts

Posted - 12/19/2005 :  17:33:50  Show Profile  Reply with Quote
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
4137 Posts

Posted - 12/19/2005 :  17:52:26  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

Stevan2020
Starting Member

25 Posts

Posted - 12/19/2005 :  18:15:38  Show Profile  Reply with Quote
graz,

That works great, thank you very much.

Edited by - Stevan2020 on 12/20/2005 18:26:51
Go to Top of Page

Stevan2020
Starting Member

25 Posts

Posted - 12/20/2005 :  18:28:44  Show Profile  Reply with Quote
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

Go to Top of Page

shijobaby
Starting Member

India
44 Posts

Posted - 08/21/2009 :  09:30:39  Show Profile  Visit shijobaby's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000