I get this error:
Msg 144, Level 15, State 1, Line 68
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
when running this code:
SELECT
CASE
WHEN O.Id IS NULL
THEN 0
ELSE O.Id
END AS TabellaOrdiniId,
OM.ID AS IdOrdineMexal,
OM.csg_doc + RIGHT('00' + CONVERT(VARCHAR(2), OM.NGB_SR_DOC), 2) + '-' + RIGHT('000000' + CONVERT(VARCHAR(6), OM.NGL_DOC), 6) AS OrdineMadre,
CASE
WHEN O.Id IS NULL
THEN ( -- >>>look here: *** <<<
SELECT TOP 1 [dbo].[vAP_StatiOrdine].Id
FROM [dbo].[vAP_StatiOrdine]
WHERE [dbo].[vAP_StatiOrdine].Attivo = 1
AND [dbo].[vAP_StatiOrdine].Id = CONVERT(INT, ISNULL((SELECT TOP 1 [dbo].[tParametri].[ValoreParametro]
FROM [dbo].[tParametri]
WHERE [dbo].[tParametri].ChiaveParametro = 'IdStatoOrdineDiDefaultOrdini'), 0))
)
ELSE O.IdStatoOrdine
END AS IdStatoOrdine,
SUM(OD.[PrezzoDiVenditaTotaleNonIvato]) AS TotalePrezzoDiVenditaNonIvatoOrdine
FROM
dgw_rp..DGW_ORDC_M AS OM
LEFT JOIN
dgw_rp..DGW_RUDT AS C ON C.CKY_CNT = OM.CKY_CNT_CLFR
LEFT JOIN
dgw_rp..dgw_ISPED AS ISPED ON ISPED.CKY_INDIR_ALT = OM.CKY_SPED_COD
LEFT JOIN
tOrdini AS O ON O.IdOrdineMexal = OM.ID
LEFT JOIN
tStatiOrdine AS SO ON SO.Id = O.IdStatoOrdine
LEFT JOIN
tTipiIncasso AS TI ON TI.Id = O.IdTipoIncasso
LEFT JOIN
tTipiServiziCorrieri AS TS ON TS.Id = O.IdTipoServizio
LEFT JOIN
tTipiBolla AS TB ON TB.Id = O.IdTipoBolla
LEFT JOIN
vAP_OrdiniDettagli AS OD ON OD.IdOrdineMexal = OM.ID
LEFT JOIN
tAnomalieOrdiniByCorriere AS AOC ON AOC.IdTabellaOrdini = O.Id
GROUP BY
CASE
WHEN O.Id IS NULL
THEN 0
ELSE O.Id
END, OM.ID,
OM.csg_doc + RIGHT('00' + CONVERT(VARCHAR(2), OM.NGB_SR_DOC), 2) + '-' + RIGHT('000000' + CONVERT(VARCHAR(6), OM.NGL_DOC), 6),
CASE
WHEN O.Id IS NULL
THEN (
-- >>>look here: *** <<<
SELECT TOP 1 [dbo].[vAP_StatiOrdine].Id
FROM [dbo].[vAP_StatiOrdine]
WHERE [dbo].[vAP_StatiOrdine].Attivo = 1
AND [dbo].[vAP_StatiOrdine].Id = CONVERT(INT, isnull((
SELECT TOP 1 [dbo].[tParametri].[ValoreParametro]
FROM [dbo].[tParametri]
WHERE [dbo].[tParametri].ChiaveParametro = 'IdStatoOrdineDiDefaultOrdini'
), 0))
)
ELSE O.IdStatoOrdine
END
The problem seems to be the subquery. Also in field set and group by clause.
But I don't understand why: maybe because the query engine can't recognize TOP 1 and "believes" that the subquery returns more than one value.
If I replace the subquery with a scalar function, or an integer constant, everything works.
Can anyone explain me why I can't use subquery in group by clause?