Error syntax error at or near group

Hi,

what is «alq.assetStatus » and » alq.asset»? Is it a table name?

syntax for referring column is «tablename.columnname» .

We need to use column names in the group by clause.

I think you need to check query syntax and you have not used assetStatus
table in the query.

On Mon, 2009-12-28 at 16:40 +0800, Premila Devi wrote:
> select sum(alq.quantity + alq.locked), category.id,
> alq.assetStatus.name
>
> from AssetLocationQuantity alq inner join alq.asset asset inner join
> asset.assetModel model inner join model.category category
>
> group by category, alq.assetStatus.name;
>
>
>
> Error :
>
>
>
> ERROR: syntax error at or near «group»
>
> LINE 3: group by category, alq.assetStatus.name;
>
> ^
>
>
>
> ********** Error **********
>
>
>
> ERROR: syntax error at or near «group»
>
> SQL state: 42601
>
> Character: 201
>
>
>
>
>
> http://cs.agbnmr.com/ci/autocomposition/logo_agb.jpg
>
> Warmest Regards,
>
> Premila Devi Dayalan Ramasamy
> Programmer Analyst
> MSAP Production Development
>
> AGB Nielsen Media Research
> Level 7,Block B,Menara PKNS-PJ,Jalan Yong Shook Lin , 46050 Petaling
> Jaya , Selangor
> ( Malaysia )
>
> t: 603-7620 2320
> f: 603-7620 2316
>
> Please note:
> This email transmission including its attachments, are intended only
> for the person or entity to which it is addressed and may contain
> confidential and/or privileged material. Any views or opinions are
> solely those of the author and do not necessarily represent those of
> AGB Nielsen Media Research, unless specifically stated. Any review,
> retransmission, dissemination or other use of, or taking of any action
> in reliance upon, this information by persons or entities other than
> the intended recipient is prohibited. If you received this email in
> error, please contact the sender and delete the email transmission
> immediately.
>
>

Thanks & Regards,

Suresh Borse
(DBA)
_________________________________________________________________________________________
DIRECTION SOFTWARE SOLUTIONS
5, Brady Gladys Plaza, 1/447, Senapati Bapat Marg, Lower Parel, Mumbai —
400 013
Tel. : 91 22 66615000 (Ext: 270) Fax : 91 22
24911046
Cell :
Site : www.direction.biz
_________________________________________________________________________________________

  Last tested: Feb 2021

Overview

This SQL error generally means that somewhere in the query, there is invalid syntax.
Some common examples:

• Using a database-specific SQL for the wrong database (eg BigQuery supports DATE_ADD, but Redshift supports DATEADD)
• Typo in the SQL (missing comma, misspelled word, etc)
• Missing a sql clause (missed from, join, select, etc)
• An object does not exist in the database or is not accessible from the current query (eg referencing orders.id when there is no orders table joined in the current query, etc)

In some circumstances, the database error message may display extra detail about where the error was raised, which can be helpful in narrowing down where to look.

Error Message

SQL ERROR: syntax error at or near

Troubleshooting

This should generally be the first step to troubleshoot any SQL syntax error in a large query: iteratively comment out blocks of SQL to narrow down where the problem is.

TIP: To make this process easier, change the group by clause to use position references
eg: group by 1,2,3,4,5 instead of group by orders.status, orders.date, to_char(...)...
as well as separate the where and having clauses onto multiple lines.

So for example, say we have the following query:

play_arrow

WITH cte AS (
select id, status, sales_amountfrom orders
)
select status, foo.date, sum(cte.sales_amount), count(*) from cte
join foo on cte.date = foo.date
group by status, foo.date
order by 3 desc

We could start by running just the portion in the CTE:

play_arrow

-- WITH cte AS (
select id, status, sales_amountfrom orders
-- )
-- select status, foo.date, sum(cte.sales_amount), count(*)
-- from cte
-- join foo on cte.date = foo.date
-- group by 1, 2
-- order by 3 desc

Then strip out the aggregates and portions related to them

play_arrow

WITH cte AS (
    select id, status, sales_amountfrom orders
)
select status, foo.date, -- sum(cte.sales_amount), count(*)
from cte
join foo on cte.date = foo.date
-- group by 1, 2
-- order by 3 desc

Iteratively stripping out / adding back in portions of the query until you find the minimum query to trigger the error.

• Lookup functions and syntax If the query is small enough, or if we’ve narrowed the scope enough with 1, google all the functions used in the query and verify that they exist and are being used correctly.

• Verify all objects exist Verify that you’ve joined all tables used in the select, where, and having clause, and that those tables exist in the db. Once we’ve narrowed things down from 1, also check that each column exists in the table specified.