sql Group by Error

Question

Post reply

sql Group by Error

Nishan Ratnayake

SSC Veteran

Points: 242
More actions
June 26, 2008 at 10:08 am

#187634

I'm using exisiting SQL code for one of my reports.
It seems like existing code was written in SQL 2000.
Now I'm using SLQ 2005.
When I try to run the code, i get the following error message.
SELECT
ACCT.CUST_ACCT_ID,
('SHAW') AS SOURCE_SYS ,
(CASE WHEN (SUBSTRING(ACCT.OPR_ACCT_NR,14,3) = '100') THEN 'LINE'
WHEN (SUBSTRING(ACCT.LOAN_TYPE_CD,1,1) IN('7','8')) THEN 'LINE'
ELSE 'LOAN'
END) AS PROD
FROM DLY_ACCOUNT ACCT
RIGHT JOIN DLY_ACCOUNT_MSR MESR ON
(ACCT.CUST_ACCT_ID = MESR.CUST_ACCT_ID)
RIGHT JOIN FLASH_DELQ_MAXINT] DELQ ON
(ACCT.CUST_ACCT_ID = DELQ.CUST_ACCT_ID)
RIGHT JOIN FLASH_MAXINT FMAX ON
(DELQ.OPR_ACCT_NR = FMAX.OPR_ACCT_NR)
WHERE (MESR.TIME_DAY_GEN_ID = '2008-06-25') AND
((SUBSTRING(ACCT.OPR_ACCT_NR,1,3) IN('451','456')) OR
(MESR.LEDG_CD = '02') OR
(MESR.LEDG_CD BETWEEN '21' AND '31') OR
(MESR.LEDG_CD BETWEEN '71' AND '99')) AND
((MESR.CHARGE_OFF_IN IS NULL) OR
(MESR.CHARGE_OFF_IN = 'N')) AND
(MESR.ACCT_STATUS_CD = 'OPEN')
GROUP BY ACCT.CUST_ACCT_ID,
'SHAW' ,
CASE WHEN (SUBSTRING(ACCT.OPR_ACCT_NR,14,3) = '100') THEN 'LINE'
WHEN (SUBSTRING(ACCT.LOAN_TYPE_CD,1,1) IN('7','8')) THEN 'LINE'
ELSE 'LOAN'
END
Error message is;
Each GROUP BY expression must contain at least one column reference.
I dont know why I get this. can anyone help me?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]

SELECT

ACCT.CUST_ACCT_ID,

('SHAW') AS SOURCE_SYS ,

(CASE WHEN (SUBSTRING(ACCT.OPR_ACCT_NR,14,3) = '100') THEN 'LINE'

WHEN (SUBSTRING(ACCT.LOAN_TYPE_CD,1,1) IN('7','8')) THEN 'LINE'

ELSE 'LOAN'

END) AS PROD

FROM DLY_ACCOUNT ACCT

RIGHT JOIN DLY_ACCOUNT_MSR MESR ON

(ACCT.CUST_ACCT_ID = MESR.CUST_ACCT_ID)

RIGHT JOIN FLASH_DELQ_MAXINT DELQ ON

(ACCT.CUST_ACCT_ID = DELQ.CUST_ACCT_ID)

RIGHT JOIN FLASH_MAXINT FMAX ON

(DELQ.OPR_ACCT_NR = FMAX.OPR_ACCT_NR)

WHERE (MESR.TIME_DAY_GEN_ID = '2008-06-25') AND

((SUBSTRING(ACCT.OPR_ACCT_NR,1,3) IN('451','456')) OR

(MESR.LEDG_CD = '02') OR

(MESR.LEDG_CD BETWEEN '21' AND '31') OR

(MESR.LEDG_CD BETWEEN '71' AND '99')) AND

((MESR.CHARGE_OFF_IN IS NULL) OR

(MESR.CHARGE_OFF_IN = 'N')) AND

(MESR.ACCT_STATUS_CD = 'OPEN')

GROUP BY ACCT.CUST_ACCT_ID,

SOURCE_SYS ,

CASE WHEN (SUBSTRING(ACCT.OPR_ACCT_NR,14,3) = '100') THEN 'LINE'

WHEN (SUBSTRING(ACCT.LOAN_TYPE_CD,1,1) IN('7','8')) THEN 'LINE'

ELSE 'LOAN'

END

Mahesh

[/font]

MH-09-AM-8694

Nishan Ratnayake SSC Veteran Points: 242 More actions · Answer 2

Hi Mahesh,

Thanks for reply.

I tried with that.

Now it gives me this errro.

Invalid column name 'SOURCE_SYS'.

If I remove whole GROUP BY expression, then It works fine. But I can not see any data.

GSquared SSC Guru Points: 260824 More actions · Answer 3

I don't see any aggregate functions in this query. If I'm missing one, I'd try removing each piece of the Group By till you find the one that's the problem. I think it's the Case statement.

On the other hand, if you're just using Group By to get rid of duplicates, try using Select Distinct instead. It's usually more efficient at that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Nishan Ratnayake SSC Veteran Points: 242 More actions · Answer 4

Thanks..yes I removed group by and it works fine now.

Thanks again