November 13, 2017 at 9:27 pm
Hi
I have below Query and i getting this error
Column 'TCL$CLE.Store No_' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT L.[Store No_], L.[Customer No_], (L.[Amount (LCY)]) AS Amount,
(Select SUM(L.[Amount (LCY)]) - CASE ISNULL
((SELECT ABS(SUM([Amount (LCY)])) AS EXPR1
FROM dbo.[TCL$GLEntry]
WHERE ([Customer No_] = L.[Customer No_]) AND ([Store No_] = L.[Store No_])), 0)
WHEN 0 THEN (ISNULL
((SELECT ABS(SUM([Amount (LCY)])) AS EXPR1
FROM dbo.[TCL$GLEntry]
WHERE ([Customer No_] = L.[Customer No_])), 0)) ELSE ISNULL
((SELECT ABS(SUM([Amount (LCY)])) AS EXPR1
FROM dbo.[TCL$GLEntry]
WHERE ([Customer No_] = L.[Customer No_]) AND ([Store No_] = L.[Store No_])), 0)
END) AS Balance
FROM dbo.[TCL$GLEntry] AS L LEFT OUTER JOIN
dbo.[TCL$Customer] AS C ON L.[Customer No_] = C.No_
WHERE (L.[TType] = 7)
Thanks
November 14, 2017 at 2:21 am
I feel like I'm on a witch hunt now, as I've asked in your questions so many times (with no reply),but Jigjitsigh, DDL and Sample data... please.
You're doing a SUM of your field SUM(L.[Amount (LCY)]) (which is from your base query) at the start of your SUB SELECT. Thus you need to do a GROUP BY in your base query or use an OVER clause and define your partitions in your SUM.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 14, 2017 at 3:25 am
Thom A - Tuesday, November 14, 2017 2:21 AMI feel like I'm on a witch hunt now, as I've asked in your questions so many times (with no reply),but Jigjitsigh, DDL and Sample data... please.You're doing a SUM of your field SUM(L.[Amount (LCY)]) (which is from your base query) at the start of your SUB SELECT. Thus you need to do a GROUP BY in your base query or use an OVER clause and define your partitions in your SUM.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply