错误：语法错误在或接近“和”

问题描述：

select 
    ad.escore, 
    ad.mscore, 
    round(sum(ps.cnt)/sum(n.cnt) * 100,1) as percent 
from 
(
    select 
    account_no, 
    -- 602 becomes '595-604' 
    to_char(trunc(empirica_score - 5, -1) + 5, '9999') || '-' || to_char(trunc(empirica_score - 5, -1) + 14, '9999') as escore, 
    -- 97 becomes '76-100'. Change the expression to group differently. 
    cast(((mfin_score - 1)/25) * 25 + 1 as text) || '-' || cast(((mfin_score - 1)/25) * 25 + 25 as text) as mscore 
    from account_details 
) ad 
join 
(
    select custno, count(*) as cnt 
    from paysoft_results 
    where result = 'Successful' 
    and resultdate >= '13/08/2014'  <------- HERE 
    and resultdate <= '12/19/2014'  <------- HERE 
    group by custno 
) ps on ps.custno = ad.account_no 
join 
(
    select customer_code, count(distinct start_date) as cnt 
    from naedo 
    and start_date >= '13/08/2014'  <------- HERE 
    and start_date <= '12/19/2014'  <------- HERE 
    group by customer_code 
) n on n.customer_code = ad.account_no 
group by ad.escore, ad.mscore;

它工作完美，如果我没有像上面安装的日期。

如果我把日期我会得到一个错误ERROR: syntax error at or near "and"

任何想法，为什么？

UPDATE

好吧，我想我可以问一个问题，现在，所以如果我可以追加在这一个。

ERROR: date/time field value out of range: "13/08/2014"

我的查询日期比较。什么是正确的方法来做到这一点？

莫尔纳，更新可能应该是一个_new_问题，但我已经介绍了它在一边，我原来的答复。 – paxdiablo 2014-09-19 10:16:29

不**不依赖隐式数据类型转换。始终使用适当的日期文字，而不是字符串常量。 ''13/08/2014''是一个字符串，不是日期。您应该使用Oracle的to_date（）函数：'to_date（'13/08/2014'，'DD/MM/YYYY'）或者（稍短一点）ANSI日期文字：'date'2014-08-13' '。 – 2014-09-19 12:13:18

答

好，这位将不起作用：

select customer_code, count(distinct start_date) as cnt 
from naedo 
and start_date >= '13/08/2014'  <------- HERE 
and start_date <= '12/19/2014'  <------- HERE 
group by ...

因为where子句开始与where，而不是一个and。否则，我们就都可以称其为and条款:-)

这将需要是：

select customer_code, count(distinct start_date) as cnt 
from naedo 
where start_date >= '13/08/2014' 
    and start_date <= '12/19/2014' 
group by ...

您标HERE（第二段其他位，第一join条款）看起来不错，它应该没有错误地工作。

顺便说一句，至少一个日期的是格式不正确。段：

and start_date >= '13/08/2014' 
and start_date <= '12/19/2014'

要么有十九（或十七拉丁前缀基于什么8 ^日 UNDECIMBER的日期或12 ^日的，好，我甚至不知道在真正的月份已经失去了）是。

你需要弄清楚你的数据库支持哪一个mm/dd/yyyy或dd/mm/yyyy，然后坚持只是之一。

既然你怀疑更新指出它抱怨13/08/2014，你可能会发现它应该被写为08/13/2014，在mm/dd/yyyy格式。

facePalm ...是的只是注意到。 – morne 2014-09-19 09:28:31

答

select customer_code, count(distinct start_date) as cnt 
     from naedo 
     Where start_date >= '13/08/2014'  <------- HERE 
     and start_date <= '12/19/2014'  <------- HERE 
     group by customer_code

答

“去哪儿”失踪查询：

" select customer_code, count(distinct start_date) as cnt 
     from naedo where 
     start_date >= '13/08/2014'  <------- HERE" 
" 
============================ 
select 
     ad.escore, 
     ad.mscore, 
     round(sum(ps.cnt)/sum(n.cnt) * 100,1) as percent 
    from 
    (
     select 
     account_no, 
     -- 602 becomes '595-604' 
     to_char(trunc(empirica_score - 5, -1) + 5, '9999') || '-' || to_char(trunc(empirica_score - 5, -1) + 14, '9999') as escore, 
     -- 97 becomes '76-100'. Change the expression to group differently. 
     cast(((mfin_score - 1)/25) * 25 + 1 as text) || '-' || cast(((mfin_score - 1)/25) * 25 + 25 as text) as mscore 
     from account_details 
    ) ad 
    join 
    (
     select custno, count(*) as cnt 
     from paysoft_results 
     where result = 'Successful' 
     and resultdate >= '13/08/2014'  <------- HERE 
     and resultdate <= '12/19/2014'  <------- HERE 
     group by custno 
    ) ps on ps.custno = ad.account_no 
    join 
    (
     select customer_code, count(distinct start_date) as cnt 
     from naedo where 
     start_date >= '13/08/2014'  <------- HERE 
     and start_date <= '12/19/2014'  <------- HERE 
     group by customer_code 
    ) n on n.customer_code = ad.account_no 
    group by ad.escore, ad.mscore;

错误：语法错误在或接近“和”

相关推荐