Solved

What's wrong with the below query?

Posted on 2010-08-23
5
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33499081
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33499090
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
ID: 33499097
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
ID: 33499106
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
ID: 33499151
Try handle null on this Query : select OrderNum from Items

like

select isnull(OrderNum , 0) from Items
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL + Insert Into Table - If Doesnt Exist 9 58
SQL Database Restore 2008 R2 1 27
Database maintenance 36 101
Assigning Database Principals to Database Roles 3 17
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…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

756 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