英文:
Invalid identifier oracle sql
问题
这个错误似乎经常出现，我得到了一些不同的答案，比如更改名称，但我在这方面的查询中没有发现任何问题。也许我漏掉了什么？
例如：
select
sport,
sum(turn_over) as turnover,
count(distinct(user_no)) as users,
sum(turn_over) / count(distinct(user_no)) as average,
extract(
month
from
ref_date
) as outer_month,
extract(
year
from
ref_date
) as outer_year
from
dw_unica.T_DAILY_SB
group by
sport,
outer_month,
outer_year;
导致以下错误：
ORA-00904: "OUTER_YEAR": 无效标识符
00904. 00000 - "%s: 无效标识符"
*原因:
*操作:
错误发生在第61行，第271列。
英文:
This error seems to appear a lot and I've had mixed answers such as changing names, but I couldn't see any issues with my query regarding this. Perhaps I have not spotted something?
For example:
select
sport,
sum(turn_over) as turnover,
count(distinct(user_no)) as users,
sum(turn_over) / count(distinct(user_no)) as average,
extract(
month
from
ref_date
) as outer_month,
extract(
year
from
ref_date
) as outer_year
from
dw_unica.T_DAILY_SB
group by
sport,
outer_month,
outer_year;
Gives the following error:
ORA-00904: "OUTER_YEAR": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 61 Column: 271
答案1
得分: 2
无法在GROUP BY子句中使用在SELECT子句中定义的别名，因为SELECT子句在GROUP BY子句之后处理。
需要对底层函数调用进行GROUP BY，而不是对别名进行GROUP BY：
select sport,
sum(turn_over) as turnover,
count(distinct user_no) as users,
sum(turn_over) / count(distinct user_no) as average,
extract(month from ref_date) as outer_month,
extract(year from ref_date) as outer_year
from dw_unica.T_DAILY_SB
group by
sport,
extract(month from ref_date),
extract(year from ref_date);
英文:
You cannot use aliases defined in the SELECT clause in the GROUP BY clause as the SELECT clause is processed after the GROUP BY clause.
You need to GROUP BY the underlying function calls rather than the aliases:
select sport,
sum(turn_over) as turnover,
count(distinct user_no) as users,
sum(turn_over) / count(distinct user_no) as average,
extract(month from ref_date) as outer_month,
extract(year from ref_date) as outer_year
from dw_unica.T_DAILY_SB
group by
sport,
extract(month from ref_date),
extract(year from ref_date);
答案2
得分: 1
你正在尝试对外部年份（outer_year）进行分组，但它并不存在（它是一个别名）。相同情况适用于外部月份（outer_month）。
分组依据
运动,
从ref_date提取的月份,
从ref_date提取的年份
英文:
You are trying to do Group By outer_year whicch does not exist (it is an alias). The same stands for outher_month.
Group By
sport,
extract(month from ref_date),
extract(year from ref_date)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论