Oracle: ORA-00936: missing expression, trying to add where statement to selected column

Question

I have a SQL query that works just fine for getting info of the past 2 years out of our database the problem comes up when I try to add a statement that adds a new condition to the SQL.

where ASSIGNED_REP = 'PERSON, SOME',

Maybe I am doing this wrong but one of the columns I am pulling from is set up like this:

name_full AS ASSIGNED_REP,

So I figured if I added the where statement it should only return results where that column contains the username I gave it. However I am getting the error Oracle: ORA-00936: missing expression.

My SQL works perfectly until I added where ASSIGNED_REP = 'PERSON, SOME' to it on the 4th line.

I am new to SQL so I am sure I am missing something.

Full 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

Please Show the complete statament. you can not use an alias from `select` clause in `where`! — Jens, Sep 12 '18 at 14:19
You can not add a WHERE condition based on an alias, but you need to refer to the column: `WHERE name_full = …` — Aleksej, Sep 12 '18 at 14:20
possible duplicate https://stackoverflow.com/questions/356675/using-an-alias-in-a-where-clause — Jens, Sep 12 '18 at 14:21
maybe the extra comma at the end? maybe you are trying to use an alias? Maybe you are typing `where`multiple times? Lots of maybes is what you are going to get until you post the entire sql statement. — Igor, Sep 12 '18 at 14:21
Sorry about the SQL statement. I had to take some time to modify it as to no show any company info. It has been added now. — Mike - SMT, Sep 12 '18 at 14:31
You already have a `where` clause; you can't just tack another one in the middle of the select list. If you want to look for a specific name you should do that as part of the existing `where` clause, as `and name_full = 'PERSON, SOME'` — Alex Poole, Sep 12 '18 at 14:34
@AlexPoole Currently my code pulls a list of ticket from the past 2 years. What I was trying to add was a simple parameter that checked for a specific rep name assigned to the tickets. — Mike - SMT, Sep 12 '18 at 14:38
Just wondering why all the down votes? Is there something wrong with the way my question is formatted? (so I can improve my questions in the future) — Mike - SMT, Sep 12 '18 at 14:57

score 1 · Answer 1 · answered Sep 12 '18 at 14:39

You are on the right track. What you want to do here is add to your existing WHERE clause

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

Now you will only get tickets for the last two years for person named PERSON, SOME

That looks like it did the trick. My query run with the desired results. Thank you very much. My main issue was using the wrong column name reference. I should have been doing name_full instead of ASSIGNED_REP. I did try to do an AND statement as well before but with the wrong column name. — Mike - SMT, Sep 12 '18 at 14:46

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

The structure of a query is pretty well defined. You can't have more than one where clause¹, and it doesn't belong in the middle of the select list.

You want to add another condition, so add it to the existing where clause:

select 
ccc.BID AS B_ID,
TTTT,
uuu.name_full AS ASSIGNED_REP,
...
where ccc.C_DATE >= (sysdate - 730)
and uuu.name_full = 'PERSON, SOME'
group by ccc.BID,
...

Note that you can't use the ASSIGNED_REP alias in the where clause, you have to use the original column name. The documentation linked to above also includes (emphasis added):

c_alias

Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.

So you can order by ASSIGNED_REP, but you can't use it in the where clause or anywhere else.

¹ I'm ignoring subqueries...

Thanks Alex. For some reason I thought it would be ok to use the alias. I didn't realize that was where my issue was. — Mike - SMT, Sep 12 '18 at 14:47
@Mike-SMT - I've highlighted the relevant bit from the docs. Don't worry, that's a common mistake *8-) — Alex Poole, Sep 12 '18 at 14:52
Thanks. I have selected your answer as it explains why what I was doing would not work. Much appreciated. — Mike - SMT, Sep 12 '18 at 14:53

Oracle: ORA-00936: missing expression, trying to add where statement to selected column

2 Answers2