Insert into select from two tables values an instert in anot

Question

Some help?

Error in ms sql

Msg 147, Level 15, State 1, Line 2
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

INSERT INTO dbo.trebovnica(redni_broj, datum, radni_nalog, sifra , naziv, proizvodjac ,jedinica_mjere, kolicina, magacin)
Select redni_broj, (select datum_otvaranja from dbo.radni_nalog_lista WHERE id = (select max(id) from dbo.radni_nalog_lista)), (select broj_rn from dbo.radni_nalog_lista WHERE id=MAX(id)),
sifra, naziv, proizvodjac, jedinica_mjere, kolicina, 'Magacin proizvodnja Celinac' from dbo.normativi_roba where id_fakture=(select id from normativi_lista where id=2)

Mario Scheeren · Answer

DECLARE @idfakture int ; DECLARE @maxID int ; SELECT @idfakture = id FROM normativi_lista WHERE id=2; -- makes no sense, just copied SELECT @maxID = MAX (id) FROM dbo.radni_nalog_lista; INSERT INTO dbo.trebovnica(redni_broj, datum, radni_nalog, sifra , naziv, proizvodjac ,jedinica_mjere, kolicina, magacin) Select a.redni_broj, b.datum_otvaranja, b.broj_rn, a.sifra, a.naziv, a.proizvodjac, a.jedinica_mjere, a.kolicina, 'Magacin proizvodnja Celinac' FROM dbo.normativi_roba a JOIN dbo.normativi_lista b ON b.id = @maxID WHERE a.id_fakture=@idfakture;