Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

aggregate error

Posted on 2011-02-11
12
Medium Priority
?
262 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

971 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