Access query not working in SQL , incorrect syntax near 'group'

Question

0.00/5 (No votes)

See more:

Hi Everyone

I am busy converting access queries for sql server but i dont understand why this one wont work..

SQL

SELECT
	(T1.Debit) As Debit,
	(T1.Credit) As Credit,
	(T1.Debit - T1.Credit) As Balance,
	T1.CustCode
FROM
	(
SELECT
	SUM(AverageDebit) As Debit,
	SUM(AverageCredit) As Credit,
	CustCode
FROM
	(
SELECT
	(IIF(TransactionType IN (0,
	6,
	9,
	10,
	11,
	14,
	16,
	17,
	18,
	19,
	21),
	ABS(Price1) ,
	0)) AS AverageDebit,
	(IIF(TransactionType IN (1,
	2,
	3,
	7,
	15,
	22),
	ABS(Price1),
	0)) AS AverageCredit,
	CustCode,
	TransactionType
FROM
	TransactionHeader
WHERE
	CustCode = 'AA0001'
GROUP BY
	CustCode,
	TransactionType,
	Price1,
	ID)
GROUP BY
	CustCode) As T1

Any suggestions what is wrong with this query ?

Thanks

What I have tried:

Removing Group by,

tried a few other modifications ,

I tried the following and it works , dont know if its the same as the access one tho

SQL

SELECT
	(T1.Debit) As Debit,
	(T1.Credit) As Credit,
	(T1.Debit - T1.Credit) As Balance,
	T1.CustCode
FROM
	(
SELECT
	SUM(AverageDebit) As Debit,
	SUM(AverageCredit) As Credit,
	CustCode
FROM
	(
SELECT
	(IIF(TransactionType IN (0,
	6,
	9,
	10,
	11,
	14,
	16,
	17,
	18,
	19,
	21),
	ABS(Price1) ,
	0)) AS AverageDebit,
	(IIF(TransactionType IN (1,
	2,
	3,
	7,
	15,
	22),
	ABS(Price1),
	0)) AS AverageCredit,
	CustCode,
	TransactionType
FROM
	TransactionHeader
WHERE
	CustCode = 'AA0001'
GROUP BY
	CustCode,TransactionType,Price1
	)AS TS2 GROUP BY TS2.CustCode ) As T1

Posted 13-Feb-18 21:50pm

Member 10146034

Updated 14-Feb-18 10:03am

v2

Add a Solution

Comments

David_Wimbley 14-Feb-18 11:26am

What is the exact error you get? Is it something like (this is sql server) Column '' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Also, if this is a one time query or if you are able to, it looks like you've got 2 deep sub table queries which also may be part of the problem (you didn't post schema and I didn't attempt to recreate) so if you are able, you might consider using a temp table to load data into and massage it there rather than create a huge query. That may make it easier to work with but I don't know anything about your project.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-02-14T10:03:00

Well... I'm not sure you can use IIF together with IN.

You have to use SWITCH[^]

SQL

ABS(Switch([TransactionType]=0,[Price1],[TransactionType]=6, [Price1], ...and so on...))

or IIF[^], but in different way:

SQL

ABS(IIF([TransactionType]=0 OR [TransactionType]=6 OR ..., [Price1], 0))

Finally, i'll simplify your query this way:

SQL

SELECT Src.CustCode, Src.Debit, Src.Credit, Src.Debit - Src.Credit AS Balance
FROM (
    SELECT CustCode, SUM(ABS(IIF([TransactionType]=0 OR [TransactionType]=6 OR ..., [Price1], 0))) AS Debit, 
        SUM(ABS(IIF([TransactionType]=1 OR [TransactionType]=2 OR ..., [Price1], 0))) AS Credit
    FROM TransactionHeader
    WHERE CustCode = "AA0001"
    GROUP BY CustCode
) AS Src;

Try!