Solved

aggregate error

Posted on 2011-02-11
12
259 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 250 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

623 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