June 1, 2004 at 9:40 am
Hi, I have created a stored procedure to insert rows into a table, however whenever I execute the stored procedure I get the following error:
Warning: Null Value is eliminated by an aggregate or other SET operation.
My stored procedure reads:
CREATE PROCEDURE dbo.[360_InsertBlankRows]
(
@ParticipantId VarChar(31)
)
AS
INSERT INTO [360_QuestionResults] (Question, Role, GroupId)
SELECT QuestionNumber, Role, GroupNumber
FROM [360_Reference]
WHERE ((CAST(QuestionNumber AS VarChar) + CAST(Role AS VarChar) + CAST(GroupNumber AS VarChar)) NOT IN
(SELECT CAST(Question AS VarChar) + CAST(Role AS VarChar) + CAST(GroupId AS VarChar) AS Expr1
FROM [360_QuestionResults]))
I know that the SELECT/FROM/WHERE clases of the statment are correct because they select the rows that I want them to. My problem is that I cannot identify where I am making use of an aggregate, and hense why I am getting this problem.
I have searched through solutions to similar problems, and have tried setting: SET ANSI_WARNINGS OFF but this doesn't seem to change anything. I observe that many solutions use the IsNull function, but I don't know what to apply this to.
Any assistance would be more than helpful,
regards,
Rob
June 2, 2004 at 6:26 am
Try this:
WHERE ((CAST(ISNULL(QuestionNumber,'') AS VarChar) + CAST(ISNULL(Role,'') AS VarChar) + CAST(ISNULL(GroupNumber,'') AS VarChar)) NOT IN
(SELECT CAST(ISNULL(Question,'') AS VarChar) + CAST(ISNULL(Role,'') AS VarChar) + CAST(ISNULL(GroupId,'') AS VarChar) AS Expr1
FROM [360_QuestionResults]))
June 2, 2004 at 7:04 am
Would this not be better
INSERT INTO [360_QuestionResults] (Question, Role, GroupId)
SELECT r.QuestionNumber, r.Role, r.GroupNumber
FROM [360_Reference] r
LEFT OUTER JOIN [360_QuestionResults] q
ON q.Question = r.QuestionNumber
AND q.Role = r.Role
AND q.GroupId = r.GroupNumber
WHERE q.Question IS NULL
Far away is close at hand in the images of elsewhere.
Anon.
June 2, 2004 at 9:59 am
Lynnette, David,
sorry it has taken me so long, but I have tried both of these two approaches and they both result in the same error message as before. I still can't get this stored procedure to work, but thank you for your help anyway,
Robert Bath,
Southampton, UK
June 2, 2004 at 10:49 am
Did you include SET ANSI_Warnings OFF within the procedure itself?
IHTH,
b
June 2, 2004 at 12:18 pm
Are any of your inputs being CASTed longer than 30 characters? Default length for a VARCHAR when CAST is used is 30 characters.
-SQLBill
June 3, 2004 at 1:42 am
Is there a trigger on 360_QuestionResults?
Did you post the complete procedure?
If not, can you do so.
What is the parameter @ParticipantId used for?
Far away is close at hand in the images of elsewhere.
Anon.
May 14, 2008 at 3:51 pm
hi, i had same problem but using SUM.
i fixed changing places, first i had:
isnull(sum(column))
and warning appeared
but changed for
sum(isnull(column))
and warning's gone away.
you might need to change to cast((column1+column2+column3) as varchar), and doing this, you might need -cast((inull(column1,'')+....)as varchar)-'isnull'
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy