Oracle SQL - 奇怪的“ORA-00907 缺少右括号”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1242965/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL - Strange 'ORA-00907 Missing Right Parenthesis' error
提问by tuinstoel
I've written a query to test out a simple linear regression to get the line of best-fit between two sets of weight measures. It should hopefully return results like below, but it's throwing a strange error
我写了一个查询来测试一个简单的线性回归，以获得两组体重测量之间的最佳拟合线。它应该有希望返回如下结果，但它抛出了一个奇怪的错误
'ORA-00907 Missing Right Parenthesis'
'ORA-00907 缺少右括号'
and TOAD is pointing towards the part where it says:
和 TOAD 指向它说的部分：
case ( when trn.wid_location = 28.3 then
I've been combing it over for missing parenthesis but I don't think that's the issue because if I replace the case statement with
我一直在为缺少括号而对其进行梳理，但我认为这不是问题，因为如果我将 case 语句替换为
100 as mine,
the error disappears and the query executes.
错误消失并执行查询。
Any thoughts?
有什么想法吗？
Cheers,
干杯，
Tommy
汤米
select
decode(wid_location,28.3,'CL',29.6,'DA') as site,
(n*sum_xy - sum_x*sum_y)/(n*sum_x_sq - sum_x*sum_x) as m,
(sum_y - ((n*sum_xy - sum_x*sum_y)/(n*sum_x_sq - sum_x*sum_x))*sum_x)/n as b
from (
select
wid_location,
sum(wids) as sum_x,
sum(mine) as sum_y,
sum(wids*mine) as sum_xy,
sum(wids*wids) as sum_x_sq,
count(*) as n
from (
select
trn.wid_location,
con.empty_weight_total as wids,
case (
when trn.wid_location = 28.3 then con.empty_weight_total*0.900-1.0
when trn.wid_location = 29.6 then con.empty_weight_total*0.950-1.5
end
) as mine
from widsys.train trn
inner join widsys.consist con
using (train_record_id)
where mine_code = 'YA'
and to_char(trn.wid_date,'IYYY') = 2009
and to_char(trn.wid_date,'IW') = 29
)
group by wid_location
)
And here are the results i'd be happy to see
这是我很乐意看到的结果
-- +----------+--------+----------+
-- | SITE | M | B |
-- +----------+--------+----------+
-- | CL | 0.900 | -1.0 |
-- +----------+--------+----------+
-- | DA | 0.950 | -1.5 |
-- +----------+--------+----------+
回答by tuinstoel
T think the syntax of the case is not correct.
T 认为 case 的语法不正确。
Do something like:
做类似的事情：
SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN ‘Low'
WHEN 0.15 THEN ‘Average'
WHEN 0.2 THEN ‘High'
ELSE ‘N/A'
END ) Commission
FROM employees ORDER BY last_name;
回答by jle
Try getting rid of both parens in the case statement. You don't need them.
尝试去掉 case 语句中的两个括号。你不需要它们。
It can be:
有可能：
case when trn.wid_location = 28.3 then con.empty_weight_total*0.900-1.0
when trn.wid_location = 29.6 then con.empty_weight_total*0.950-1.5 end as mine
