Ошибка синтаксиса примерное положение group - TopOshibok.ru - решение и исправление самых разных ошибок

GROUP BY is its own clause, it’s not part of a WHERE clause.

This is what you have:

WHERE (
    ST_DWithin(i.st_intersection,a.geom_acc, 10000)
    group by st_intersection
)

This is what you need:

WHERE ST_DWithin(i.st_intersection,a.geom_acc, 10000)
group by st_intersection

Edit: In response to comments, it sounds like your JOIN is a bit more complex than the UPDATE ... FROM syntax would need. Take a look at the «Notes» section on this page:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn’t join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.

Normally this would involve changing the syntax to something like:

UDPATE SomeTable
SET SomeColumn = 'Some Value'
WHERE AnotherColumn = 
  (SELECT AnotherColumn
   FROM AnotherTable
   -- etc.)

However, the use of ST_DWithin() in this query may complicate that quite a bit. Without much deeper knowledge of the table structures, relationships, and overall intent of this update there probably isn’t much more help I can give. Essentially you’re going to need to clarify for the database exactly what records need to be updated and how to update them, which may involve changing your query to this latter sub-select syntax in some way.

Источник

GROUP BY is its own clause, it’s not part of a WHERE clause.

This is what you have:

WHERE (
    ST_DWithin(i.st_intersection,a.geom_acc, 10000)
    group by st_intersection
)

This is what you need:

WHERE ST_DWithin(i.st_intersection,a.geom_acc, 10000)
group by st_intersection

Edit: In response to comments, it sounds like your JOIN is a bit more complex than the UPDATE ... FROM syntax would need. Take a look at the «Notes» section on this page:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn’t join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.

Normally this would involve changing the syntax to something like:

UDPATE SomeTable
SET SomeColumn = 'Some Value'
WHERE AnotherColumn = 
  (SELECT AnotherColumn
   FROM AnotherTable
   -- etc.)

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Я пишу SQL-запрос, но продолжаю получать синтаксическую ошибку:

Incorrect syntax near the keyword ‘GROUP’

Что могло вызвать эту проблему? Я попытался найти все очевидные вещи, но не заметил этого, и я также просмотрел предыдущие сообщения, см. Запрос ниже:

(/****** Script for SelectTopNRows command from SSMS  ******/
SELECT ALLGENEDX.[id]
      ,ALLGENEDX.[Chr]
      ,ALLGENEDX.[Start]
      ,ALLGENEDX.[End]
      ,ALLGENEDX.[ref]
      ,ALLGENEDX.[alt]
      ,ALLGENEDX.[Func#refGene]
      ,ALLGENEDX.[Gene#refGene]
      ,ALLGENEDX.[Otherinfo]
      ,CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN PARENT_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN PARENT_OTHERINFO <> 'HOM' AND PARENT_AMMOUNT > 1 THEN 1 ELSE 0 END END AS 'PARENT_INFO'
      ,CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN SIBLING_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN SIBLING_OTHERINFO <> 'HOM' AND (SIBLING_AMMOUNT = 1 OR SIBLING_AMMOUNT IS NULL) THEN 1 ELSE 2 END END AS 'SIBLING_INFO' 
      FROM [Exome Data].[dbo].[ALLGENEDX],
(SELECT [ID],
[Chr],
[Start],
[end],
[REF], 
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO] AS PARENT_OTHERINFO, 
COUNT (ID) OVER (PARTITION BY 
[Chr],
[Start],
[End],
[REF], 
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO]) AS PARENT_AMMOUNT 
FROM [Exome Data].DBO.ALLGENEDX 
WHERE ID IN ('1646304GD', '1646307GD'))AS PARENT, 
(SELECT [id],
[Chr],
[Start],
[end],
[REF], 
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO] AS SIBLING_OTHERINFO, 
COUNT (ID) OVER (PARTITION BY
[Chr],
[Start],
[end],
[REF], 
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO]) AS SIBLING_AMMOUNT 
FROM [Exome Data].DBO.ALLGENEDX 
WHERE ID IN ('1646310GD', '1646314GD', '1646317GD', '1646320GD', '1646325GD'))AS SIBLING
   WHERE SIBLING.[CHR]=ALLGENEDX.Chr
  AND SIBLING.[START]=ALLGENEDX.[START]
  AND SIBLING.[END]=ALLGENEDX.[END]
  AND SIBLING.[REF]=ALLGENEDX.[REF]
  AND SIBLING.[ALT]= ALLGENEDX.[ALT] 
  AND SIBLING.[GENE#REFGENE]=ALLGENEDX.[GENE#REFGENE]
  AND PARENT.[CHR]=ALLGENEDX.[Chr]
  AND PARENT.[START]=ALLGENEDX.[START]
  AND PARENT.[END]=ALLGENEDX.[END]
  AND PARENT.[REF]=ALLGENEDX.[REF]
  AND PARENT.[ALT]= ALLGENEDX.[ALT] 
  AND PARENT.[GENE#REFGENE]=ALLGENEDX.[GENE#REFGENE]
  AND  ALLGENEDX.ID= '1646299GD' AND ALLGENEDX.Func#refGene = 'exonic' 
  AND (EXAC_FREQ<'0.005' OR EXAC_FREQ IS NULL)
  AND ([1000G_ALL]<'0.005' OR [1000G_ALL] IS NULL)
  AND (GNOMAD_EXOME_ALL<'0.005' OR GNOMAD_EXOME_ALL IS NULL)
  HAVING CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN PARENT_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN PARENT_OTHERINFO <> 'HOM' AND PARENT_AMMOUNT > 1 THEN 1 ELSE 0 END END > 0
  OR CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN SIBLING_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN SIBLING_OTHERINFO <> 'HOM' AND (SIBLING_AMMOUNT = 1 OR SIBLING_AMMOUNT IS NULL) THEN 1 ELSE 2 END END > 0
  GROUP BY ALLGENEDX.[id]
      ,ALLGENEDX.[Chr]
      ,ALLGENEDX.[Start]
      ,ALLGENEDX.[End]
      ,ALLGENEDX.[ref]
      ,ALLGENEDX.[alt]
      ,ALLGENEDX.[Func#refGene]
      ,ALLGENEDX.[Gene#refGene]
      ,ALLGENEDX.[Otherinfo]
      ,[PARENT_OTHERINFO]
      ,[SIBLING_OTHERINFO]
     ORDER BY len ALLGENEDX.[Chr], Chr, start;/****** Script for SelectTopNRows command from SSMS  ******/
SELECT ALLGENEDX.[id]
      ,ALLGENEDX.[Chr]
      ,ALLGENEDX.[Start]
      ,ALLGENEDX.[End]
      ,ALLGENEDX.[ref]
      ,ALLGENEDX.[alt]
      ,ALLGENEDX.[Func#refGene]
      ,ALLGENEDX.[Gene#refGene]
      ,ALLGENEDX.[Otherinfo]
      ,CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN PARENT_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN PARENT_OTHERINFO <> 'HOM' AND PARENT_AMMOUNT > 1 THEN 1 ELSE 0 END END AS 'PARENT_INFO'
      ,CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN SIBLING_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN SIBLING_OTHERINFO <> 'HOM' AND (SIBLING_AMMOUNT = 1 OR SIBLING_AMMOUNT IS NULL) THEN 1 ELSE 2 END END AS 'SIBLING_INFO' 
      FROM [Exome Data].[dbo].[ALLGENEDX],
(SELECT [ID],
[Chr],
[Start],
[end],
[REF], 
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO] AS PARENT_OTHERINFO, 
COUNT (ID) OVER (PARTITION BY 
[Chr],
[Start],
[End],
[REF], 
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO]) AS PARENT_AMMOUNT 
FROM [Exome Data].DBO.ALLGENEDX 
WHERE ID IN ('1646304GD', '1646307GD'))AS PARENT, 
(SELECT [id],
[Chr],
[Start],
[end],
[REF], 
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO] AS SIBLING_OTHERINFO, 
COUNT (ID) OVER (PARTITION BY
[Chr],
[Start],
[end],
[REF], 
[ALT],
[Func#refGene],
[GENE#REFGENE],
[OTHERINFO]) AS SIBLING_AMMOUNT 
FROM [Exome Data].DBO.ALLGENEDX 
WHERE ID IN ('1646310GD', '1646314GD', '1646317GD', '1646320GD', '1646325GD'))AS SIBLING
   WHERE SIBLING.[CHR]=ALLGENEDX.Chr
  AND SIBLING.[START]=ALLGENEDX.[START]
  AND SIBLING.[END]=ALLGENEDX.[END]
  AND SIBLING.[REF]=ALLGENEDX.[REF]
  AND SIBLING.[ALT]= ALLGENEDX.[ALT] 
  AND SIBLING.[GENE#REFGENE]=ALLGENEDX.[GENE#REFGENE]
  AND PARENT.[CHR]=ALLGENEDX.[Chr]
  AND PARENT.[START]=ALLGENEDX.[START]
  AND PARENT.[END]=ALLGENEDX.[END]
  AND PARENT.[REF]=ALLGENEDX.[REF]
  AND PARENT.[ALT]= ALLGENEDX.[ALT] 
  AND PARENT.[GENE#REFGENE]=ALLGENEDX.[GENE#REFGENE]
  AND  ALLGENEDX.ID= '1646299GD' AND ALLGENEDX.Func#refGene = 'exonic' 
  AND (EXAC_FREQ<'0.005' OR EXAC_FREQ IS NULL)
  AND ([1000G_ALL]<'0.005' OR [1000G_ALL] IS NULL)
  AND (GNOMAD_EXOME_ALL<'0.005' OR GNOMAD_EXOME_ALL IS NULL)
  HAVING CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN PARENT_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN PARENT_OTHERINFO <> 'HOM' AND PARENT_AMMOUNT > 1 THEN 1 ELSE 0 END END > 0
  OR CASE WHEN OTHERINFO = 'HOM' THEN CASE WHEN SIBLING_OTHERINFO = 'HET' THEN 1 ELSE 0 END ELSE CASE WHEN SIBLING_OTHERINFO <> 'HOM' AND (SIBLING_AMMOUNT = 1 OR SIBLING_AMMOUNT IS NULL) THEN 1 ELSE 2 END END > 0
  GROUP BY ALLGENEDX.[id]
      ,ALLGENEDX.[Chr]
      ,ALLGENEDX.[Start]
      ,ALLGENEDX.[End]
      ,ALLGENEDX.[ref]
      ,ALLGENEDX.[alt]
      ,ALLGENEDX.[Func#refGene]
      ,ALLGENEDX.[Gene#refGene]
      ,ALLGENEDX.[Otherinfo]
      ,[PARENT_OTHERINFO]
      ,[SIBLING_OTHERINFO]
     ORDER BY len ALLGENEDX.[Chr], Chr, start;

Источник

I am trying to link several tables together in SQL Server. The code below shows how I have linked the tables together so far:

select *
from profile
left join learner l on l.learnerid = profileid
left join learner_levels ll on ll.learnerid = l.learnerid
left join subjects s on s.subjectid = ll.subjectid
left join learner_group lg on lg.learnerid = profileid
where ll.archived = '0' and ll.completed = '0'
order by surname asc`

What i want to do is filter the results by «groupid» which is in the table «group». if I add that as a ‘left join’ statement I get the error stated in the title — «Incorrect syntax near the keyword ‘group’. «

This is what I tried:

select *
from profile
left join learner l on l.learnerid = profileid
left join learner_levels ll on ll.learnerid = l.learnerid
left join subjects s on s.subjectid = ll.subjectid
left join learner_group lg on lg.learnerid = profileid
left join group g on g.groupid = lg.learnerid
where ll.archived = '0' and ll.completed = '0' and g.group_name = 'class 1'
order by surname asc`

This is the result in SQL Server Management Studio:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword
‘group’.

Where am I going wrong?

marc_s

734k176 gold badges1332 silver badges1460 bronze badges

asked Jul 20, 2010 at 13:47

Group is a reserved word. Try to use a different name (for the table). Or put square brackets around [group].

answered Jul 20, 2010 at 13:49

TobiasopdenbrouwTobiasopdenbrouw

13.8k1 gold badge22 silver badges27 bronze badges

Wrap Group in []’s

Group is a reserved word, as Tobias said. They need to be changed or wrapped in square-brackets.

select * from profile left join learner l on l.learnerid = profileid left join learner_levels ll on ll.learnerid = l.learnerid left join subjects s on s.subjectid = ll.subjectid left join learner_group lg on lg.learnerid = profileid left join [group] g on g.groupid = lg.learnerid where ll.archived = '0' and ll.completed = '0' and g.group_name = 'class 1' order by surname asc

answered Jul 20, 2010 at 13:51

Mike M.Mike M.

12.3k1 gold badge24 silver badges28 bronze badges

If you use a reserved word, such as GROUP, as a table name, you need to quote it using square brackets:

LEFT JOIN [Group] g

answered Jul 20, 2010 at 13:51

deviodevio

36.9k7 gold badges81 silver badges143 bronze badges

Use [group] to specify the table vs the reserved word group

answered Jul 20, 2010 at 13:51

FlyingStreudelFlyingStreudel

4,4344 gold badges34 silver badges55 bronze badges

«group» is a keyword, so you’ll have to write that line as :

left join [group] g on g.groupid = lg.learnerid

answered Jul 20, 2010 at 13:52

James CurranJames Curran

102k37 gold badges182 silver badges258 bronze badges

Источник

I’m writing an SQL query but I am getting a syntax error on the line with the GROUP BY. What can possibly be the problem?

UPDATE intersection_points i
  SET nbr_victimes =  sum(tue+bl+bg)
    FROM accident_ma a
    WHERE ST_DWithin(i.st_intersection,a.geom_acc, 10000) group by i.st_intersection

asked Mar 23, 2016 at 15:15

You cannot have a group by in an update statement. See this stack overflow question: GROUP BY in UPDATE FROM clause

You’ll need to use a subquery, like:

UPDATE table t1 SET column1=sq.column1
FROM  (
   SELECT t2.column1, column2
   FROM   table t2
   JOIN   table t3 USING (column2)
   GROUP  BY column2
   ) AS sq
WHERE  t1.column2=sq.column2;

answered Mar 23, 2016 at 15:45

MaryBethMaryBeth

3,68623 silver badges41 bronze badges

Источник

вот такой код:
create procedure spisok_rabot (num INT, date1 DATE, date2 DATE)
language sql
as $$;
declare
work_type VARCHAR(20);
count int;
set work_type= (select work_type, count(work_type)
from works where number=num and date_in between date1 and date2
group by work_type);
set count=(select count(work_type)
from works where number=num and date_in between date1 and date2
group by work_type);
$$;
вот такая ошибка:
ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: «VARCHAR»)
LINE 5: work_type VARCHAR(20);
во видимому, я не правильно объявляю тип переменной, но иного способа я не нашел. Помогите, пожалуйста

Вопрос задан

более двух лет назад
791 просмотр

Источник

What causes error 42601 in PostgreSQL?

How we fix the error?

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Интересное по теме: