June 15, 2010 at 1:23 pm
Hi, I have a coalesce statement that I put together from samples.
DECLARE @Notes varchar(MAX)
SELECT @Notes = COALESCE(@Notes + ':', '') +
CAST(Memo AS varchar(max))+' '+CAST(MemoDate AS varchar(max))
FROM Notes
Select @Notes as 'All Notes'
and it works like I want, almost. it returns all the memos combined into one column, but I also want the corresponding userID that goes with the memo. I tried putting the userid in several places but it wont let me. any suggestions would be helpful
Thanks
June 15, 2010 at 1:44 pm
I don't think this is doing what you think it is doing..
COALESCE by defiition in SQL BOL:
Returns the first nonnull expression among its arguments.
It is used like COALESCE( Field1, Field2, Field3, Field4, Field5 )
And would return the value from Field3 if Field1 and Field2 were null.
I think what is actually happening is you are getting the effects of CONCAT_NULL_YIELDS_NULL, which is usually on by default, it is defined this way:
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.
I also bet you are losing records..
I really think this should look more like:
DECLARE @Notes varchar(MAX)
SET @Notes = ''
SELECT @Notes = CAST( Memo AS varchar(max) )
+ ' '
+ CAST( UserId AS varchar(128) )
+ ' '
+ CAST( MemoDate AS varchar(max) )
+ ':'
FROM dbo.Notes
WHERE Memo IS NOT NULL
Select @Notes as 'All Notes'
Does that work any better?
CEWII
June 15, 2010 at 2:41 pm
the way it was, it was returning all the notes (for the one client I tried) doing it your way only returned the last entry and put the userid in the notes column. I was looking to have it separate. that's where I was running into the problem.
I tried it in the both select statement and I get this message
"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval
operations."
also the memo field will never be null anyway
June 15, 2010 at 3:30 pm
Alright, all good information, so instead of the IS NOT NULL is is effectively UserId = 12345.
How many rows of notes are there?
If you already know the UserId then:
Select @userid AS 'UserId', @Notes as 'All Notes'
But if you are trying to do all users then it is going to be trickier and will probably need a CTE, doable.. Let me stew on it.
CEWII
June 15, 2010 at 3:38 pm
yeah, it will be all userid's I was just testing with one to see it it was pulling all memos
June 15, 2010 at 11:50 pm
Can you provide the query that gives you the error A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Probably you are doing some operation like the below example
SELECT @Notes = COALESCE(@Notes + ':', '') +
CAST(Memo AS varchar(max))+' '+CAST(MemoDate AS varchar(max)), Memo
FROM Notes
You cannot select a column and assign a value to a variable in the same statement. Hope this helps you.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 16, 2010 at 6:46 am
I tried
DECLARE @Notes varchar(MAX)
SELECT @Notes = COALESCE(@Notes + ':', '') +
CAST(Memo AS varchar(max))+' '+CAST(MemoDate AS varchar(max)),UserID
FROM Notes
Select @Notes as 'All Notes'
and
DECLARE @Notes varchar(MAX)
SELECT @Notes = COALESCE(@Notes + ':', '') +
CAST(Memo AS varchar(max))+' '+CAST(MemoDate AS varchar(max))
FROM Notes
Select @Notes as 'All Notes', UserID
I get the same error on both attempts.
June 16, 2010 at 6:51 am
Try this one
DECLARE @Notes varchar(MAX)
-- Here you are assigning a value to a variable
SELECT @Notes = COALESCE(@Notes + ':', '') +
CAST(Memo AS varchar(max))+' '+CAST(MemoDate AS varchar(max))
FROM Notes
-- Here you are selecting values from a table
Select @Notes as 'All Notes', UserID
FROM Notes
You cannot do both the operations in a single statement. Thats why you were getting an error in the first statement.
As for your second query, UserID is not a variable( it is a column of a table ). So you will need a FROM clause which is what i have added.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 16, 2010 at 8:00 am
_taz_ (6/15/2010)
the way it was, it was returning all the notes (for the one client I tried) doing it your way only returned the last entry and put the userid in the notes column. I was looking to have it separate. that's where I was running into the problem.I tried it in the both select statement and I get this message
"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval
operations."
also the memo field will never be null anyway
It is hard to understand what you are really trying to do.
Could you give us an example of what the data in the table looks like, and what you want the result to look like ?
June 16, 2010 at 8:05 am
Kingston Dhasian (6/16/2010)
Try this one
DECLARE @Notes varchar(MAX)-- Here you are assigning a value to a variable
SELECT @Notes = COALESCE(@Notes + ':', '') +
CAST(Memo AS varchar(max))+' '+CAST(MemoDate AS varchar(max))
FROM Notes
-- Here you are selecting values from a table
Select @Notes as 'All Notes', UserID
FROM Notes
You cannot do both the operations in a single statement. Thats why you were getting an error in the first statement.
As for your second query, UserID is not a variable( it is a column of a table ). So you will need a FROM clause which is what i have added.
hmm... now why didn't I see that.. that worked except for it returned a row with the combined note for every row in the notes table with the userid (i.e. if the user had 10 memos it returned 10 rows with a combined memo. I added a Distinct into the second statement and then it returned only one row per userid.
Thanks!
June 16, 2010 at 8:18 am
_taz_ (6/16/2010)
Kingston Dhasian (6/16/2010)
Try this one
DECLARE @Notes varchar(MAX)-- Here you are assigning a value to a variable
SELECT @Notes = COALESCE(@Notes + ':', '') +
CAST(Memo AS varchar(max))+' '+CAST(MemoDate AS varchar(max))
FROM Notes
-- Here you are selecting values from a table
Select @Notes as 'All Notes', UserID
FROM Notes
You cannot do both the operations in a single statement. Thats why you were getting an error in the first statement.
As for your second query, UserID is not a variable( it is a column of a table ). So you will need a FROM clause which is what i have added.
hmm... now why didn't I see that.. that worked except for it returned a row with the combined note for every row in the notes table with the userid (i.e. if the user had 10 memos it returned 10 rows with a combined memo. I added a Distinct into the second statement and then it returned only one row per userid.
Thanks!
But if you have several different users in the table you will get one line per user but the 'All Notes' column will contain all notes for all users.
Is that really what you want ?
June 16, 2010 at 8:28 am
Stefan_G (6/16/2010)
_taz_ (6/16/2010)
Kingston Dhasian (6/16/2010)
Try this one
DECLARE @Notes varchar(MAX)-- Here you are assigning a value to a variable
SELECT @Notes = COALESCE(@Notes + ':', '') +
CAST(Memo AS varchar(max))+' '+CAST(MemoDate AS varchar(max))
FROM Notes
-- Here you are selecting values from a table
Select @Notes as 'All Notes', UserID
FROM Notes
You cannot do both the operations in a single statement. Thats why you were getting an error in the first statement.
As for your second query, UserID is not a variable( it is a column of a table ). So you will need a FROM clause which is what i have added.
hmm... now why didn't I see that.. that worked except for it returned a row with the combined note for every row in the notes table with the userid (i.e. if the user had 10 memos it returned 10 rows with a combined memo. I added a Distinct into the second statement and then it returned only one row per userid.
Thanks!
But if you have several different users in the table you will get one line per user but the 'All Notes' column will contain all notes for all users.
Is that really what you want ?
hmm..I tried with multiple users and it's not working either.. ok back to scratching my head..
June 16, 2010 at 8:33 am
_taz_ (6/16/2010)
hmm..I tried with multiple users and it's not working either.. ok back to scratching my head..
As I said before: Give us an example of what your data looks like and what you want the result to look like.
Once we understand your requirements I am sure it will be easy to help you.
June 16, 2010 at 9:00 am
this is basic of table
RecordIDUserIDMemo MemoDate
154625533287To clt today.2010-05-25 00:00:00.000
154552033287Approved by AJ2010-05-24 00:00:00.000
154629133287Lm giving clt id# 2010-05-25 00:00:00.000
154640733287Pprwk Recvd2010-05-25 00:00:00.000
this is what I need
UserID Memo
33287 To clt today.,2010-05-025:Approved by AJ,2010-05-24:Lm giving clt id#,2010-05-25:Pprwk Recvd,2010-05-25:
I used comma to separate memo and memo date, colon to separate new memo
June 16, 2010 at 9:03 am
But what should happen if the table contains more than one UserID ?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply