Link to home
Create AccountLog in
Avatar of Kongta
KongtaFlag for Switzerland

asked on

aggregate error

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
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image

Which database you are working - Access database or SQL Server database ?
Avatar of Kongta

ASKER

sorry, have SQL2008 backend & Access10 front
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

Avatar of Kongta

ASKER

same error, I guess it has to be somethin with the table field

Anzeige      nvarchar(200)      Checked

not?
Avatar of Kongta

ASKER

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?!
Avatar of Sharath S
Do you have aggregate functions in your query? Can you post the complete query?
Avatar of Kongta

ASKER

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
hmmm, that is a INSERT, not a UPDATE?
Avatar of Kongta

ASKER

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
Avatar of Kongta

ASKER

Do I have to put a break in my query, something like a virtual split to get it work?
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Kongta

ASKER

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