英文:
ORA-00979 : Another not a GROUP BY expression
问题
可能已经有类似的问题得到了解答，但我找不到它。
我的查询下面引发了ORA-00979错误：
beginDELETE FROM TMP_MY_CHART1;INSERT INTO TMP_MY_CHART1 (SELECT to_number (t.yyyy) as annee ,t.totalFROM (SELECT to_char(fi.RSX_DATDERNMAJ, 'YYYY') as yyyy , COUNT(1) as totalFROM FI_RESEAU fiWHERE fi.RSX_TYPLIC = 'TMP'GROUP BY to_char(fi.RSX_DATDERNMAJ, 'YYYY')) t);end;
TMP_MY_CHART1表只有两列，annee和total，都是数字类型。
顶部的SELECT单独工作正常：
SELECT to_number (t.yyyy) as annee ,t.totalFROM (SELECT to_char(fi.RSX_DATDERNMAJ, 'YYYY') as yyyy , COUNT(1) as totalFROM FI_RESEAU fiWHERE fi.RSX_TYPLIC = 'TMP'GROUP BY to_char(fi.RSX_DATDERNMAJ, 'YYYY')) t;
但是当我在前面加上INSERT INTO时，它会引发错误。
请帮助一下？
英文:
Probably a similar question have already been answered, but I couldn't find it.
My query below throw an ORA-00979 error:
beginDELETE FROM TMP_MY_CHART1;INSERT INTO TMP_MY_CHART1 (SELECT to_number (t.yyyy) as annee ,t.totalFROM (SELECT to_char(fi.RSX_DATDERNMAJ, 'YYYY') as yyyy , COUNT(1) as totalFROM FI_RESEAU fiWHERE fi.RSX_TYPLIC = 'TMP'GROUP BY to_char(fi.RSX_DATDERNMAJ, 'YYYY')) t);end;
The TMP_MY_CHART1 table has only two columns, annee and total, both are of the type number.
The top SELECT alone works ok :
SELECT to_number (t.yyyy) as annee ,t.totalFROM (SELECT to_char(fi.RSX_DATDERNMAJ, 'YYYY') as yyyy , COUNT(1) as totalFROM FI_RESEAU fiWHERE fi.RSX_TYPLIC = 'TMP'GROUP BY to_char(fi.RSX_DATDERNMAJ, 'YYYY')) t;
but when I precede with INSERT INTO, it throw an error.
Any help please ?
答案1
得分: 2
这是关于SQL Developer的一些内容，我认为。
因为你的代码在SQL*Plus中正常运行：
SQL> begin
2 DELETE FROM TMP_MY_CHART1;
3 INSERT INTO TMP_MY_CHART1 (
4 SELECT to_number (t.yyyy) as annee ,t.total
5 FROM (
6 SELECT to_char(fi.RSX_DATDERNMAJ, 'YYYY') as yyyy , COUNT(1) as total
7 FROM FI_RESEAU fi
8 WHERE fi.RSX_TYPLIC = 'TMP'
9 GROUP BY to_char(fi.RSX_DATDERNMAJ, 'YYYY')
10 ) t
11 );
12 end;
13 /
PL/SQL procedure successfully completed.
SQL>;
这里没有明显的问题；幸运的是，有不同的方法来完成相同的事情，可以重写成像下面这样，在SQL Developer中也可以工作：
SQL> begin
2 delete from tmp_my_chart1;
3 insert into tmp_my_chart1
4 select extract(year from rsx_datdernmaj), count(*)
5 from fi_reseau
6 where rsx_typlic = 'TMP'
7 group by extract(year from rsx_datdernmaj);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>;
英文:
It is something about SQL Developer, I think.
Because, your code works OK in SQL*Plus:
SQL> begin2 DELETE FROM TMP_MY_CHART1;3 INSERT INTO TMP_MY_CHART1 (4 SELECT to_number (t.yyyy) as annee ,t.total5 FROM (6 SELECT to_char(fi.RSX_DATDERNMAJ, 'YYYY') as yyyy , COUNT(1) as total7 FROM FI_RESEAU fi8 WHERE fi.RSX_TYPLIC = 'TMP'9 GROUP BY to_char(fi.RSX_DATDERNMAJ, 'YYYY')10 ) t11 );12 end;13 /PL/SQL procedure successfully completed.SQL>
There's nothing obviously wrong about it; lucky you, as there are different ways to do the same thing, rewrite it to e.g. this, which works in SQL Developer as well:
SQL> begin2 delete from tmp_my_chart1;3 insert into tmp_my_chart14 select extract(year from rsx_datdernmaj), count(*)5 from fi_reseau6 where rsx_typlic = 'TMP'7 group by extract(year from rsx_datdernmaj);8 end;9 /PL/SQL procedure successfully completed.SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论