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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply