Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

aggregate error

Posted on 2011-02-11
12
Medium Priority
?
261 Views
Last Modified: 2012-05-11
can somebody tell me why I get an error when executing following code in Access

SET Anzeige = (CASE      WHEN Land = 'GR' THEN 'Germany ['+ ISNULL(UpTrend_Anzahl,0) + '¦' + Technik_Anzahl + ']'  

the error says: a NULL value will be deleted by an aggregat or SET

If I do
SET Anzeige = (CASE      WHEN Land = 'GR' THEN 'Germany'

it works!

any idea how to solve?
thx
Kongta
0
Comment
Question by:Kongta
[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
12 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34873834
Which database you are working - Access database or SQL Server database ?
0
 

Author Comment

by:Kongta
ID: 34873854
sorry, have SQL2008 backend & Access10 front
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34873973
I cannot see anything wrong with this piece of code.
Anyway try put single quotes over 0
SET Anzeige = (CASE      WHEN Land = 'GR' THEN 'Germany ['+ ISNULL(UpTrend_Anzahl,'0') + '¦' + Technik_Anzahl + ']'  END)

Try the above

If Again error - What is the exact error message you are getting?

Raj

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Kongta
ID: 34874004
same error, I guess it has to be somethin with the table field

Anzeige      nvarchar(200)      Checked

not?
0
 

Author Comment

by:Kongta
ID: 34874069
If I do

SET Anzeige = (CASE      WHEN Land = 'GR' THEN UpTrend_Anzahl

I get the error as well, seems it can't place it in the table?!
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34874090
Do you have aggregate functions in your query? Can you post the complete query?
0
 

Author Comment

by:Kongta
ID: 34874173
it is a big query, here's the part where I fill the specific data in the tbl

INSERT INTO tblStatistikTmp_sb_Land ( Technik_Anzahl, UpTrend_Anzahl,IQ,Land,Gwth_nxt_GJ, Gwth_GJ )

SELECT a.Tech, b.UpTrend, cast([UpTrend] as decimal(20,3)) /[Tech] AS IQ, a.Land,a.Gwth_nxt_GJ,a.Gwth_GJ
FROM
(SELECT            RIGHT(wb.Symbol, 2) AS Land,
COUNT(wf.Symbol) AS Tech,
AVG(CASE WHEN wf.EPS_lfd_Jahr = 0 THEN 0
WHEN EPS_lfd_Jahr < 0 THEN - 1 * ((wf.EPS_nxt_GJ + ABS(wf.EPS_lfd_Jahr)) / wf.EPS_lfd_Jahr)
ELSE (wf.EPS_nxt_GJ / wf.EPS_lfd_Jahr) - 1 END) AS Gwth_nxt_GJ,
AVG(CASE WHEN wf.EPS = 0 THEN 0
WHEN EPS < 0 THEN - 1 * ((wf.EPS_lfd_Jahr + ABS(wf.EPS))/ wf.EPS)
ELSE (wf.EPS_lfd_Jahr / wf.EPS) - 1 END) AS Gwth_GJ

FROM            tblWertschriftenbasisinfo AS wb
INNER JOIN
tblWertschriftenfundamentaldaten AS wf
ON                  wb.Symbol = wf.Symbol
INNER JOIN
tblForex AS f
ON                  wf.Ccy_Aktie = f.Waehrung
--!!-- Kapitalisierung > 50 MioCHF und mindestens 1 Schaetzung---------------------------------------------
WHERE            (wb.Aktiv = 1) AND
(wf.Kapitalisierung * f.CHFKurs / 1000000 >= 50) AND
(wf.EPS_nxt_GJ_Anzahl >= 1) AND
(wf.MOV_AVG200 > 0) AND
(wf.MainIndex IS NOT NULL)
------------------------------------------------------------------------------------------------------
GROUP BY      RIGHT(wb.Symbol, 2))
AS                  a

INNER JOIN

(SELECT     RIGHT(wb.Symbol, 2) AS Land,
COUNT(RIGHT(wb.Symbol, 2)) AS UpTrend
FROM            tblWertschriftenbasisinfo AS wb
INNER JOIN
tblWertschriftenfundamentaldaten AS wf
ON                  wb.Symbol = wf.Symbol AND
wb.Kurs > wf.MOV_AVG200
INNER JOIN
tblForex AS f
ON                  wf.Ccy_Aktie = f.Waehrung
--!!-- Kapitalisierung > 50 MioCHF und mindestens 1 Schaetzung---------------------------------------------
WHERE            (wb.Aktiv = 1) AND
(wf.Kapitalisierung * f.CHFKurs / 1000000 >= 50) AND
(wf.EPS_nxt_GJ_Anzahl >= 1) AND
(wf.MOV_AVG200 > 0) AND
(wf.MainIndex IS NOT NULL)
------------------------------------------------------------------------------------------------------
GROUP BY      RIGHT(wb.Symbol, 2))
AS                  b

ON                              a.Land = b.Land
ORDER BY                  cast([UpTrend] as decimal(20,3)) /[Tech] DESC
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34874859
hmmm, that is a INSERT, not a UPDATE?
0
 

Author Comment

by:Kongta
ID: 34874956
This is the first part where I fill the tbl with the relevant data. The full query worked well till I wanted to add this additional info as a remark in the text-field. No idea how I can implement? A workaround may be to implement on the access-form but I cant belive this can't be done in a query
0
 

Author Comment

by:Kongta
ID: 34875104
Do I have to put a break in my query, something like a virtual split to get it work?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1000 total points
ID: 34876758
its not an error its a warning....

it basically means that Technik_Anzahl  contains a null...

and there for some rows the whole Germany +.... string becomes null,,.,

and nulls are ignored by aggregate processing , which it warns you about..


so put a coalesce(Technik_Anzahl ,'?') instead of Technik_Anzahl  and it should go away!
0
 

Author Comment

by:Kongta
ID: 34877468
didn't work Lowfatspread but brought me on the right track which worked:

WHEN Land = 'GR' THEN Germany ['+ COALESCE(Cast(Technik_Anzahl AS nvarchar(200)) ,'0')

thx to anybody
rgds & nice weekend
Kongta
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 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