Solved

What's wrong with the below query?

Posted on 2010-08-23
5
241 Views
Last Modified: 2012-06-27

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
0
Comment
Question by:mahmood66
5 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 22

Accepted Solution

by:
Om Prakash earned 500 total points
Comment Utility
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
 
LVL 2

Expert Comment

by:ajisasaggi
Comment Utility
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
 
LVL 6

Expert Comment

by:havj123
Comment Utility
Try handle null on this Query : select OrderNum from Items

like

select isnull(OrderNum , 0) from Items
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now