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.
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?
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
1
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
1
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.
I am facing an error while grouping a statement. Here is my code
DECLARE @avg_volume INT
SELECT @avg_volume = ISNULL(AVG(Total_Volume), 0)
FROM ( SELECT station_id ,
DATEPART(Year, date_time) AS YEAR ,
DATEPART(month, date_time) AS MONTH ,
CONVERT(DATE, date_time) AS DATE ,
DATENAME(weekday, date_time) AS weekday ,
SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
)
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
ORDER BY DATEPART(Year, date_time) ,
DATEPART(month, date_time) ,
CONVERT(DATE, date_time)
SELECT @avg_volume
What I am trying to do is ,taking the sum from volume group by a set of conditions(Inner query), which will give result as
station_id YEAR MONTH DATE weekday Total_volume
7 2013 2 2013-02-21 Thursday 192
7 2013 2 2013-02-27 Wednesday 2699
7 2013 2 2013-02-28 Thursday 196
2 2013 3 2013-03-07 Thursday 192
7 2013 3 2013-03-07 Thursday 192
now I want to take average of that. Any assistance will be helpful.
My Table primary table
station_id date_time volume
7 2013-02-21 00:00:00.000 96
7 2013-02-21 01:00:00.000 96
7 2013-02-27 00:00:00.000 356
7 2013-02-27 00:00:00.000 410
7 2013-02-27 00:00:00.000 471
7 2013-02-27 00:00:00.000 530
7 2013-02-27 00:00:00.000 338
7 2013-02-27 00:00:00.000 211
7 2013-02-27 00:00:00.000 159
7 2013-02-27 00:00:00.000 128
7 2013-02-27 00:00:00.000 96
7 2013-02-28 00:00:00.000 96
7 2013-02-28 01:00:00.000 100
7 2013-03-07 00:00:00.000 96
2 2013-03-07 00:00:00.000 96
2 2013-03-07 01:00:00.000 96
7 2013-03-07 01:00:00.000 96
Desired Output :
station id year month weekday average_volume
7 2013 2 Thursday 194
NePh
9078 silver badges21 bronze badges
asked Feb 28, 2014 at 4:09
0
You need to name your subquery:
DECLARE @avg_volume INT
SELECT @avg_volume = ISNULL(AVG(Total_Volume), 0)
FROM ( SELECT station_id ,
DATEPART(Year, date_time) AS YEAR ,
DATEPART(month, date_time) AS MONTH ,
CONVERT(DATE, date_time) AS DATE ,
DATENAME(weekday, date_time) AS weekday ,
SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
) AnyNameYouLikeButYouHaveToGiveOne --<-- Here
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
ORDER BY DATEPART(Year, date_time) ,
DATEPART(month, date_time) ,
CONVERT(DATE, date_time)
SELECT @avg_volume
answered Feb 28, 2014 at 7:57
Based on what you want to do, you don’t need to do a second explicit aggregation or order by. Just take the average:
DECLARE @avg_volume INT
SELECT @avg_volume = ISNULL(AVG(Total_Volume), 0)
FROM ( SELECT station_id ,
DATEPART(Year, date_time) AS "YEAR" ,
DATEPART(month, date_time) AS "MONTH" ,
CONVERT(DATE, date_time) AS "date" ,
DATENAME(weekday, date_time) AS weekday ,
SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
) t;
Your specific error was because you had no alias on the subquery (well, the keyword group doesn’t count as an alias).
Some of your column names a keywords, so I quoted those. The list of such words is here.
Actually, when writing a query like this, you don’t need to include all the columns in the select, so you could also do:
DECLARE @avg_volume INT
SELECT @avg_volume = ISNULL(AVG(Total_Volume), 0)
FROM ( SELECT SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
) t;
NePh
9078 silver badges21 bronze badges
answered Feb 28, 2014 at 4:15
Gordon LinoffGordon Linoff
1.2m54 gold badges625 silver badges765 bronze badges
1
There are few fundamental issues in your query. You are summing up and finding average on basis of month year Date ETC. I do not see any filters, for a year or month or an station id. So I assume there will be more than one row in your final select query and you are selecting into a variable. So you may get only the value from the first/last row in the variable(Of course depending on the ordering) .You have to make some modification to the query here.
Is this query giving you correct output?
SELECT ISNULL(AVG(Total_volume), 0)
FROM ( SELECT station_id ,
DATEPART(year, date_time) AS YEAR ,
DATEPART(month, date_time) AS MONTH ,
CONVERT(DATE, date_time) AS DATE ,
DATENAME(weekday, date_time) AS weekday ,
SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(year, date_time) ,
DATENAME(weekday, date_time)
ORDER BY DATEPART(year, date_time) ,
DATEPART(month, date_time) ,
CONVERT(DATE, date_time)
) T;
NePh
9078 silver badges21 bronze badges
answered Feb 28, 2014 at 4:13
1
I am facing an error while grouping a statement. Here is my code
DECLARE @avg_volume INT
SELECT @avg_volume = ISNULL(AVG(Total_Volume), 0)
FROM ( SELECT station_id ,
DATEPART(Year, date_time) AS YEAR ,
DATEPART(month, date_time) AS MONTH ,
CONVERT(DATE, date_time) AS DATE ,
DATENAME(weekday, date_time) AS weekday ,
SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
)
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
ORDER BY DATEPART(Year, date_time) ,
DATEPART(month, date_time) ,
CONVERT(DATE, date_time)
SELECT @avg_volume
What I am trying to do is ,taking the sum from volume group by a set of conditions(Inner query), which will give result as
station_id YEAR MONTH DATE weekday Total_volume
7 2013 2 2013-02-21 Thursday 192
7 2013 2 2013-02-27 Wednesday 2699
7 2013 2 2013-02-28 Thursday 196
2 2013 3 2013-03-07 Thursday 192
7 2013 3 2013-03-07 Thursday 192
now I want to take average of that. Any assistance will be helpful.
My Table primary table
station_id date_time volume
7 2013-02-21 00:00:00.000 96
7 2013-02-21 01:00:00.000 96
7 2013-02-27 00:00:00.000 356
7 2013-02-27 00:00:00.000 410
7 2013-02-27 00:00:00.000 471
7 2013-02-27 00:00:00.000 530
7 2013-02-27 00:00:00.000 338
7 2013-02-27 00:00:00.000 211
7 2013-02-27 00:00:00.000 159
7 2013-02-27 00:00:00.000 128
7 2013-02-27 00:00:00.000 96
7 2013-02-28 00:00:00.000 96
7 2013-02-28 01:00:00.000 100
7 2013-03-07 00:00:00.000 96
2 2013-03-07 00:00:00.000 96
2 2013-03-07 01:00:00.000 96
7 2013-03-07 01:00:00.000 96
Desired Output :
station id year month weekday average_volume
7 2013 2 Thursday 194
NePh
9078 silver badges21 bronze badges
asked Feb 28, 2014 at 4:09
0
You need to name your subquery:
DECLARE @avg_volume INT
SELECT @avg_volume = ISNULL(AVG(Total_Volume), 0)
FROM ( SELECT station_id ,
DATEPART(Year, date_time) AS YEAR ,
DATEPART(month, date_time) AS MONTH ,
CONVERT(DATE, date_time) AS DATE ,
DATENAME(weekday, date_time) AS weekday ,
SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
) AnyNameYouLikeButYouHaveToGiveOne --<-- Here
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
ORDER BY DATEPART(Year, date_time) ,
DATEPART(month, date_time) ,
CONVERT(DATE, date_time)
SELECT @avg_volume
answered Feb 28, 2014 at 7:57
Based on what you want to do, you don’t need to do a second explicit aggregation or order by. Just take the average:
DECLARE @avg_volume INT
SELECT @avg_volume = ISNULL(AVG(Total_Volume), 0)
FROM ( SELECT station_id ,
DATEPART(Year, date_time) AS "YEAR" ,
DATEPART(month, date_time) AS "MONTH" ,
CONVERT(DATE, date_time) AS "date" ,
DATENAME(weekday, date_time) AS weekday ,
SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
) t;
Your specific error was because you had no alias on the subquery (well, the keyword group doesn’t count as an alias).
Some of your column names a keywords, so I quoted those. The list of such words is here.
Actually, when writing a query like this, you don’t need to include all the columns in the select, so you could also do:
DECLARE @avg_volume INT
SELECT @avg_volume = ISNULL(AVG(Total_Volume), 0)
FROM ( SELECT SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(Year, date_time) ,
DATENAME(weekday, date_time)
) t;
NePh
9078 silver badges21 bronze badges
answered Feb 28, 2014 at 4:15
Gordon LinoffGordon Linoff
1.2m54 gold badges625 silver badges765 bronze badges
1
There are few fundamental issues in your query. You are summing up and finding average on basis of month year Date ETC. I do not see any filters, for a year or month or an station id. So I assume there will be more than one row in your final select query and you are selecting into a variable. So you may get only the value from the first/last row in the variable(Of course depending on the ordering) .You have to make some modification to the query here.
Is this query giving you correct output?
SELECT ISNULL(AVG(Total_volume), 0)
FROM ( SELECT station_id ,
DATEPART(year, date_time) AS YEAR ,
DATEPART(month, date_time) AS MONTH ,
CONVERT(DATE, date_time) AS DATE ,
DATENAME(weekday, date_time) AS weekday ,
SUM(volume) AS Total_volume
FROM rvtcs_data_aggregated_hourly
GROUP BY station_id ,
CONVERT(DATE, date_time) ,
DATEPART(month, date_time) ,
DATEPART(year, date_time) ,
DATENAME(weekday, date_time)
ORDER BY DATEPART(year, date_time) ,
DATEPART(month, date_time) ,
CONVERT(DATE, date_time)
) T;
NePh
9078 silver badges21 bronze badges
answered Feb 28, 2014 at 4:13
1
|
99Екатерина99 1 / 1 / 0 Регистрация: 05.12.2016 Сообщений: 178 |
||||
|
1 |
||||
|
28.12.2016, 15:56. Показов 4723. Ответов 1 Метки нет (Все метки)
Ошибка! Что не так в этом запросе? Добавлено через 49 минут
__________________ 0 |
|
1039 / 855 / 335 Регистрация: 08.12.2016 Сообщений: 3,283 |
|
|
28.12.2016, 15:59 |
2 |
|
в 9 строке лишняя закрывающая скобка 0 |
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»;
Пытаюсь создать табличку, вот такую
CREATE TABLE screens_items ( screenitemid bigint NOT NULL, screenid bigint NOT NULL, resourcetype integer DEFAULT '0' NOT NULL, resourceid bigint DEFAULT '0' NOT NULL, width integer DEFAULT '320' NOT NULL, height integer DEFAULT '200' NOT NULL, x integer DEFAULT '0' NOT NULL, y integer DEFAULT '0' NOT NULL, colspan integer DEFAULT '0' NOT NULL, rowspan integer DEFAULT '0' NOT NULL, elements integer DEFAULT '25' NOT NULL, valign integer DEFAULT '0' NOT NULL, halign integer DEFAULT '0' NOT NULL, style integer DEFAULT '0' NOT NULL, url varchar(255) DEFAULT '' NOT NULL, dynamic integer DEFAULT '0' NOT NULL, sort_triggers integer DEFAULT '0' NOT NULL, application varchar(255) DEFAULT '' NOT NULL, PRIMARY KEY (screenitemid) );
Получаю
Error: ОШИБКА: ошибка синтаксиса (примерное положение: "application")
psql --version psql (PostgreSQL) 9.4.9
Вроде слово «application» не зарезервировано?
Я пишу 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;
вот такой код:
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);
во видимому, я не правильно объявляю тип переменной, но иного способа я не нашел. Помогите, пожалуйста
-
Вопрос заданболее двух лет назад
-
745 просмотров