asked on

What's wrong with the below query?

Select DueDate, DeliveryDate,OrderNum
From Invnum Where OrderNum in (select OrderNum from Items) and DocState in (1,3)
and OrderNum in
(
SELECT isnull(SOno,'') FROM vuForInvNumTrig WHERE (isnull(NoOfCntAllc,0) > isnull(Recd,0)) AND (POno = 'PO7943')
)

I am using the above query in a trigger its giving me an error like
'Null value is eliminated by an aggregate or other SET operation.'

When i execute the same query in a stored procedure window, it is returning results but with warning,

Warning: Null value is eliminated by an aggregate or other SET operation.
DueDate DeliveryDate OrderNum
----------------------- ----------------------- --------------------
8/30/2010 7/31/2010 SO4876
No rows affected.
(1 row(s) returned)

Why this happens, please suggest a solution

Bhavesh Shah

this is not your full query.

problem is u doing some aggregate function and null value coming in that column because of warning msg is coming.

can u post ur trigger query?

Guy Hengel [angelIII / a3]

I agree.

to start with, make sure your IN ( subquery) does not return NULLS:

and OrderNum in
(
SELECT     SOno FROM vuForInvNumTrig WHERE    (isnull(NoOfCntAllc,0) >                 isnull(Recd,0)) AND (POno = 'PO7943')
AND SOno IS NOT NULL
)

Open in new window

ASKER CERTIFIED SOLUTION

Om Prakash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ajisasaggi

Does the items table have rows with NULL OrderNum value?
If so, add a NOT NULL check for OrderNum and try.

(select OrderNum from Items where OrderNum is not null)

havj123

Try handle null on this Query : select OrderNum from Items

like

select isnull(OrderNum , 0) from Items