首页 > 解决方案 > Oracle:ORA-00936:缺少表达式,尝试将 where 语句添加到选定列

问题描述

我有一个 SQL 查询,可以很好地从我们的数据库中获取过去 2 年的信息,当我尝试添加一个向 SQL 添加新条件的语句时,问题就出现了。

where ASSIGNED_REP = 'PERSON, SOME',

也许我做错了,但我从中提取的一列是这样设置的:

name_full AS ASSIGNED_REP,

所以我想如果我添加了 where 语句,它应该只返回该列包含我给它的用户名的结果。但是我得到了错误Oracle: ORA-00936: missing expression

我的 SQL 运行良好,直到我where ASSIGNED_REP = 'PERSON, SOME'在第 4 行添加它。

我是 SQL 新手,所以我确定我遗漏了一些东西。

完整的 SQL:

select 
ccc.BID AS B_ID,
TTTT,
name_full AS ASSIGNED_REP where ASSIGNED_REP = 'PERSON, SOME', /* issue is here */
bbb.CUID,
bbb.BNN,
bbb.PGN,
ccc.TYP_CD AS CLM_TYP,
ccc.C_TYP AS SCLM_TYP,
C_DATE,
NEXT_DATE,
SUM(ccc.LINE) AS line_items,
SUM(ccc.AMT) AS c_amount,
SUM(ccc.CO_RSLVD) AS Z_RSLVD,
SUM(ccc.CU_RSLVD) AS C_RSVLD,
ccc.STATUS_TYP AS STATUS,
DATE_FOLLOW_UP,
bbb.STATE
from bstatus bbb
join display_view ccc on ccc.bnn = bbb.bn
left join user_profile up on uuu.zz_id = ccc.ASSIGNED_USER_ID
where ccc.C_DATE >= (sysdate - 730)
/* Also tried */
/* and ASSIGNED_REP = 'PERSON, SOME' */
group by ccc.BID,
TTTT,
uuu.name_full,
bbb.CUID,
bbb.BAN,
bbb.PGN,
ccc.TYP_CD,
ccc.C_TYP,
C_DATE,
NEXT_DATE,
ccc.STATUS_TYP,
DATE_FOLLOW_UP,
bbb.STATE

标签: sqloraclewhere

解决方案


你在正确的轨道上。您想要在这里做的是添加到您现有的 WHERE 子句

SELECT ccc.BID AS B_ID
    ,TTTT
    ,ASSIGNED_REP
    ,bbb.CUID
    ,bbb.BNN
    ,bbb.PGN
    ,ccc.TYP_CD AS CLM_TYP
    ,ccc.C_TYP AS SCLM_TYP
    ,C_DATE
    ,NEXT_DATE
    ,SUM(ccc.LINE) AS line_items
    ,SUM(ccc.AMT) AS c_amount
    ,SUM(ccc.CO_RSLVD) AS Z_RSLVD
    ,SUM(ccc.CU_RSLVD) AS C_RSVLD
    ,ccc.STATUS_TYP AS STATUS
    ,DATE_FOLLOW_UP
    ,bbb.STATE
FROM bstatus bbb
    JOIN display_view ccc ON ccc.bnn = bbb.bn
    LEFT JOIN user_profile up ON uuu.zz_id = ccc.ASSIGNED_USER_ID
WHERE ccc.C_DATE >= (sysdate - 730)
    AND name_full = 'PERSON, SOME'
GROUP BY ccc.BID
    ,TTTT
    ,uuu.name_full
    ,bbb.CUID
    ,bbb.BAN
    ,bbb.PGN
    ,ccc.TYP_CD
    ,ccc.C_TYP
    ,C_DATE
    ,NEXT_DATE
    ,ccc.STATUS_TYP
    ,DATE_FOLLOW_UP
    ,bbb.STATE

现在你只会得到最近两年的票给有名字的人PERSON, SOME


推荐阅读