Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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
0
Kongta
Asked:
Kongta
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
Which database you are working - Access database or SQL Server database ?
0
 
KongtaAuthor Commented:
sorry, have SQL2008 backend & Access10 front
0
 
Rajkumar GsSoftware EngineerCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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

Anzeige      nvarchar(200)      Checked

not?
0
 
KongtaAuthor Commented:
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
 
SharathData EngineerCommented:
Do you have aggregate functions in your query? Can you post the complete query?
0
 
KongtaAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hmmm, that is a INSERT, not a UPDATE?
0
 
KongtaAuthor Commented:
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
 
KongtaAuthor Commented:
Do I have to put a break in my query, something like a virtual split to get it work?
0
 
LowfatspreadCommented:
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
 
KongtaAuthor Commented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now