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
mahmood66Asked:
Who is Participating?
 
Om PrakashConnect With a Mentor Commented:
Try using the following query:

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

If you simply want to supress the warning then set the following before script
SET ANSI_WARNINGS OFF

and reset at the end.
SET ANSI_WARNINGS ON
0
 
Bhavesh ShahLead AnalysistCommented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
ajisasaggiCommented:
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)
0
 
havj123Commented:
Try handle null on this Query : select OrderNum from Items

like

select isnull(OrderNum , 0) from Items
0
All Courses

From novice to tech pro — start learning today.