Error CASE in GROUP BY

Question

Post reply

Error CASE in GROUP BY

Lisset

SSCommitted

Points: 1858
More actions
March 2, 2011 at 8:41 am

#235404

Hi everybody, I am executing the follow query but always show the error:
Msg 164, Level 15, State 1, Line 5
Each GROUP BY expression must contain at least one column that is not an outer reference.
I dont know what is bad in my query. Please someone, can help me?
Thanks a lot!
declare @fec char(8)
set @fec='20110222'
DROP TABLE #tmp
SELECT
CONVERT(VARCHAR,DATEADD(DAY,2, @fec),112) AS FECHA,
RIGHT(A.COD_DOC09,8) AS CODDOC,
A.NUM_CTA,
A.PAN AS TARJETA,
A.NOMBRE,
CASE WHEN B.DIRECCION IS NOT NULL AND LEN(B.DIRECCION) > 3 THEN B.DIRECCION ELSE (A.DIR_CALLE+' '+A.DIR_PISO+' '+A.DIR_INT) END AS DIRECCION,
CASE WHEN B.DIRECCION IS NOT NULL AND LEN(B.DIRECCION) > 3 THEN B.DISTRITO ELSE SUBSTRING(A.COD_LOCALIDAD,8,30)END AS DISTRITO
into #tmp
FROM BI_SCREENING_PERU.dbo.IN_FILEFD_SO287D_OLD18 A
LEFT OUTER JOIN BI_SCREENING_PERU.dbo.MAE_TBONUS_CLIENTE_201011 B
ON RIGHT(A.COD_DOC09,8) = B.CODDOC
WHERE A.NUM_CTA IN
(
SELECT DISTINCT NUM_CTA
FROM BI_SCREENING_PERU.dbo.IN_FILEFD_CC120DD
WHERE FEC_CIERRE_CC= CONVERT(VARCHAR,DATEADD(DAY,2, @fec),112)
)
GROUP BY CONVERT(VARCHAR,DATEADD(DAY,2, @fec),112),
RIGHT(A.COD_DOC09,8),
A.NUM_CTA,
A.PAN,
A.NOMBRE,
CASE WHEN B.DIRECCION IS NOT NULL AND LEN(B.DIRECCION) > 3 THEN B.DIRECCION ELSE (A.DIR_CALLE+' '+A.DIR_PISO+' '+A.DIR_INT) END,
CASE WHEN B.DIRECCION IS NOT NULL AND LEN(B.DIRECCION) > 3 THEN B.DISTRITO ELSE SUBSTRING(A.COD_LOCALIDAD,8,30)END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The issue is the

CONVERT(VARCHAR,DATEADD(DAY,2, @fec),112),

part of your GROUP BY statement.

Since there is no column reference, it'll throw the error you see.

You should get the expected result set result by either removing this part from the GROUP BY clause or using DISTINCT instead of GROUP BY.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lisset SSCommitted Points: 1858 More actions · Answer 2

Thanks I has solution the error creating other table with group by.