October 31, 2011 at 1:53 am
Hi Friends,
The below query fires an Error:-
(Case TEJPGLMA_LEAF.CATEGORY WHEN 3 then
(Select Sum(CASE GLBAL.CCY_CODE WHEN STBRN.BRANCH_LCY THEN
(Select Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN CR_BAL_LCY ELSE 0 END) - Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN 0 ELSE CR_BAL_LCY END)
FROM TEJPGLBL GLBAL1
Where GLBAL.BRANCH_CODE = GLBAL1.BRANCH_CODE
And GLBAL.GL_CODE = GLBAL1.GL_CODE and GLBAL.PERIOD_CODE = GLBAL1.PERIOD_CODE
and GLBAL.FIN_YEAR = GLBAL1.FIN_YEAR)
ELSE GLBAL.CR_BAL_LCY END))
WHEN 4 then
(Select Sum(CASE GLBAL.CCY_CODE WHEN STBRN.BRANCH_LCY
THEN (Select Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN CR_BAL_LCY ELSE 0 END) - Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN 0 ELSE CR_BAL_LCY END)
FROM TEJPGLBL GLBAL1
Where GLBAL.BRANCH_CODE = GLBAL1.BRANCH_CODE And GLBAL.GL_CODE = GLBAL1.GL_CODE and GLBAL.PERIOD_CODE = GLBAL1.PERIOD_CODE and GLBAL.FIN_YEAR = GLBAL1.FIN_YEAR)
ELSE GLBAL.CR_BAL_LCY END))
ELSE GLBAL.CR_BAL_LCY END)LCY_CREDIT,
Kindly help me out in this.
I am facing the below error:-
------------------------------
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
October 31, 2011 at 2:05 am
For me it results in a totally different set of errors:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'case'.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'when'.
Msg 156, Level 15, State 1, Line 49
Incorrect syntax near the keyword 'else'.
I may be totally wrong, but this may have something to do with the fact that you've only supplied us with a snippet of a sql query instead of something that we really can work on. Please read the link in my footer text to see how you can help the people here help you.
October 31, 2011 at 3:10 am
I am facing the below error:-
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
The Complete Query is as follows:-
SELECT
TEJPGLMA_LEAF.GL_CODE,
TEJPGLMA_LEAF.GL_DESC GLDESC,
TEJPGLMA_LEAF.LEAF,
TEJPGLMA_LEAF.PARENT_GL,
TEJPGLMA_LEAF.ULTI_PARENT,
TEJPGLMA_PARENT.GL_DESC PARENTDESC,
TEJPGLMA_ULTPARENT.GL_DESC ULTPDESC,
GLBAL.CCY_CODE AC_CCY,
(Case TEJPGLMA_LEAF.CATEGORY WHEN 3 then
(Select Sum(CASE GLBAL.CCY_CODE WHEN STBRN.BRANCH_LCY THEN
(Select Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN DR_BAL_LCY ELSE 0 END) - Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN 0 ELSE DR_BAL_LCY END)
FROM TEJPGLBL GLBAL1 Where GLBAL.BRANCH_CODE = GLBAL1.BRANCH_CODE And GLBAL.GL_CODE = GLBAL1.GL_CODE and GLBAL.PERIOD_CODE = GLBAL1.PERIOD_CODE and GLBAL.FIN_YEAR = GLBAL1.FIN_YEAR)
ELSE GLBAL.DR_BAL_LCY END))
WHEN 4 then
(Select Sum(CASE GLBAL.CCY_CODE WHEN STBRN.BRANCH_LCY THEN
(Select Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN DR_BAL_LCY ELSE 0 END) - Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN 0 ELSE DR_BAL_LCY END)
FROM TEJPGLBL GLBAL1 Where GLBAL.BRANCH_CODE = GLBAL1.BRANCH_CODE And GLBAL.GL_CODE = GLBAL1.GL_CODE and GLBAL.PERIOD_CODE = GLBAL1.PERIOD_CODE and GLBAL.FIN_YEAR = GLBAL1.FIN_YEAR)
ELSE GLBAL.DR_BAL_LCY END)) ELSE GLBAL.DR_BAL_LCY END)AS LCY_DEBIT,
(Case TEJPGLMA_LEAF.CATEGORY WHEN 3 then
(Select Sum(CASE GLBAL.CCY_CODE WHEN STBRN.BRANCH_LCY THEN
(Select Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN CR_BAL_LCY ELSE 0 END) - Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN 0 ELSE CR_BAL_LCY END)
FROM TEJPGLBL GLBAL1
Where GLBAL.BRANCH_CODE = GLBAL1.BRANCH_CODE
And GLBAL.GL_CODE = GLBAL1.GL_CODE and GLBAL.PERIOD_CODE = GLBAL1.PERIOD_CODE
and GLBAL.FIN_YEAR = GLBAL1.FIN_YEAR)
ELSE GLBAL.CR_BAL_LCY END))
WHEN 4 then
(Select Sum(CASE GLBAL.CCY_CODE WHEN STBRN.BRANCH_LCY
THEN (Select Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN CR_BAL_LCY ELSE 0 END) - Sum(CASE CCY_CODE WHEN STBRN.BRANCH_LCY THEN 0 ELSE CR_BAL_LCY END)
FROM TEJPGLBL GLBAL1
Where GLBAL.BRANCH_CODE = GLBAL1.BRANCH_CODE And GLBAL.GL_CODE = GLBAL1.GL_CODE and GLBAL.PERIOD_CODE = GLBAL1.PERIOD_CODE and GLBAL.FIN_YEAR = GLBAL1.FIN_YEAR)
ELSE GLBAL.CR_BAL_LCY END))
ELSE GLBAL.CR_BAL_LCY END)LCY_CREDIT,
Sum(GLBAL.DR_BAL) FCY_DEBIT,
Sum(GLBAL.CR_BAL) FCY_CREDIT,
TEJPGLMA_LEAF.CATEGORY
FROM
TEJPGLMA TEJPGLMA_LEAF,
TEJPGLMA TEJPGLMA_PARENT,
TEJPGLMA TEJPGLMA_ULTPARENT,
TEJPGLBL GLBAL,
TEJPBRNH STBRN
WHERE TEJPGLMA_LEAF.PARENT_GL = TEJPGLMA_PARENT.GL_CODE
AND TEJPGLMA_LEAF.ULTI_PARENT = TEJPGLMA_ULTPARENT.GL_CODE
AND GLBAL.GL_CODE = TEJPGLMA_LEAF.GL_CODE
AND GLBAL.LEAF = 'Y'
AND GLBAL.BRANCH_CODE = STBRN.BRANCH_CODE
AND GLBAL.PERIOD_CODE = STBRN.CURRENT_PERIOD
GROUP BY
TEJPGLMA_LEAF.GL_CODE, TEJPGLMA_LEAF.GL_DESC ,
TEJPGLMA_LEAF.LEAF,TEJPGLMA_LEAF.PARENT_GL,TEJPGLMA_LEAF.
ULTI_PARENT,TEJPGLMA_PARENT.GL_DESC,
TEJPGLMA_ULTPARENT.GL_DESC,GLBAL.CCY_CODE,
TEJPGLMA_LEAF.CATEGORY
Above is the Complete Query where in I am facing an error.
Kindly check and let me know the solution at th earliest
🙂
October 31, 2011 at 3:35 am
The error means pretty much what it says; you've got multiple instances there where you're trying to SUM an expression that contains a SUM. SQL Server won't let you do that. You'll need to restructure your query to use derived tables or some other technique, instead.
October 31, 2011 at 3:45 am
Hello and welcome to SSC!
It seems that your DDL script has become detached from your post, or perhaps you were unaware of the benefits of providing one.
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
October 31, 2011 at 5:15 pm
I'm sorry but there is too much wrong in your query to make anything out of it. You'll have to describe what you intended to do, because this does not make any sense.
For example this snippet from your query:
Select Sum(
CASE GLBAL.CCY_CODE
WHEN STBRN.BRANCH_LCY
THEN (
Select Sum(
CASE CCY_CODE
WHEN STBRN.BRANCH_LCY
THEN CR_BAL_LCY
ELSE 0
END
) - Sum(
CASE CCY_CODE
WHEN STBRN.BRANCH_LCY
THEN 0
ELSE CR_BAL_LCY
END
)
FROM TEJPGLBL GLBAL1
Where GLBAL.BRANCH_CODE = GLBAL1.BRANCH_CODE
And GLBAL.GL_CODE = GLBAL1.GL_CODE
and GLBAL.PERIOD_CODE = GLBAL1.PERIOD_CODE
and GLBAL.FIN_YEAR = GLBAL1.FIN_YEAR
)
ELSE GLBAL.CR_BAL_LCY
END
)
The error that gives you the error message is simply avoided (as was stated before): don't put a sum() inside another sum() like you did -for example- in this snippet.
But we can't tell you what you should have put there instead because you still didn't provide enough information. Posting the complete query was a step in the right direction, but it's not enough. I'll give you an example of why we can not help you:
CCY_CODE does not have an alias, nor does CR_BAL_LCY. How do you want us to guess from which table these columns are without any table definitions? We need to know at least what your table(s) look like.
Plus, we're doing this in our spare time: if we have to try and understand your over-complicated, badly formatted query, we've spent most of the time available just trying to read it and there's no time left to answer you. So, please provide a properly formatted and reduced to the essence of your problem query, so that more people will want to spend their time for you. Like this you will not get much more response than the ramblings you're currently getting.
November 5, 2011 at 10:42 am
Thanks A Lot.
:-)It Helped me A Lot.:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply