COALESCE and select statements

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 and select statements

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-05-11 : 10:45:51

Hi there,

I found the link http://www.sqlteam.com/item.asp?ItemID=2368 on how to build CSV lists and it works perfect until I put it in a select statment .

This is what works perfect :


DECLARE @PONumber varchar(100)
SELECT @PONumber = COALESCE(@PoNumber + ', ', '') + CAST(PONumber AS varchar(8))
FROM vwBackHaulLoads
WHERE NewTrailerID = '11008245'

SELECT @PONumber

This does not. What I'm stuck on is how to combine them:


DECLARE @PONumber varchar(100)
SELECT @PONumber = COALESCE(@PoNumber + ', ', '') + CAST(PONumber AS varchar(8))
FROM dbo.Yardcheck INNER JOIN
dbo.vwBackHaulLoads ON dbo.Yardcheck.TrailerID = dbo.vwBackHaulLoads.NewTrailerID

SELECT  vwBackHaulLoads.NewTrailerID,@PoNumber,
Yardcheck.TrailerType,Yardcheck.Classification
FROM dbo.Yardcheck INNER JOIN
dbo.vwBackHaulLoads ON dbo.Yardcheck.TrailerID = dbo.vwBackHaulLoads.NewTrailerID
WHERE dbo.Yardcheck.UpLoadDate = CONVERT(varchar, GETDATE(), 101)
AND dbo.Yardcheck.TrailerType = 'C' 
AND dbo.Yardcheck.Classification = 'BH'
and yardcheck.source ='aberdeen'
AND dbo.Yardcheck.TrailerID = '11008245'
order by yardcheck.source

Any thoughts or suggestions would be greatly appreicated.

Thanks

Laura

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-11 : 11:38:56

put the code for building a csv in a User defined function and call a function in each row.

Go with the flow & have fun! Else fight the flow

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-05-11 : 12:54:02

Wow that's advanced for me. How would you go about doing that? I saw the function here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647 and tried to fool around with it but I don;t know how to call it.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-11 : 13:18:03

create function dbo.MyFunction ( @NewTrailerID varchar(20))
returns varchar(8000)
as
begin
DECLARE @PONumber varchar(100)
SELECT @PONumber = COALESCE(@PoNumber + ', ', '') + CAST(PONumber AS varchar(8))
FROM vwBackHaulLoads
WHERE NewTrailerID = @NewTrailerID
SELECT @PONumber
end
go

SELECT dbo.MyFunction(vwBackHaulLoads.NewTrailerID) as PONumbers,
Yardcheck.TrailerType,Yardcheck.Classification
FROM ...

Go with the flow & have fun! Else fight the flow

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-05-11 : 13:28:21

That works beautifully. Thanks so much.

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources