Home » Developer & Programmer » Forms » ORA 00905 missing Keyword (10g)
| ORA 00905 missing Keyword [message #334759] |
Thu, 17 July 2008 17:31  |
doicomehereoften1
Messages: 13
Registered: June 2008
Location: CO
|
Junior Member |
|
|
I'm attempting to run this query behind a form that has controls tied to the variables in the query. However, when I test in the environment and also in SQL Developer, I keep getting the ORA-000905 missing keyword error with a line and column reference in the middle of a table alias name in my CASE expression.
From the research I've done, I've found that some versions of Oracle have different issues with how things are aliased, but my versions should work with how I have it. I think there's a different problem, but I don't know what that could be.
Thanks for looking!
Select distinct STVSBGI.STVSBGI_DESC "HIGH_SCH",
SPRIDEN.SPRIDEN_ID "ID",
SPRIDEN.SPRIDEN_LAST_NAME "L_NAME",
SPRIDEN.SPRIDEN_FIRST_NAME "F_NAME",
SPRIDEN.SPRIDEN_MI "MI"
from FAISMGR.RRRAREQ RRRAREQ,
SATURN.SPRIDEN SPRIDEN,
SATURN.SORHSCH SORHSCH,
SATURN.STVSBGI STVSBGI,
faismgr.rrrareq bb
where SPRIDEN.SPRIDEN_PIDM = RRRAREQ.RRRAREQ_PIDM
and RRRAREQ.RRRAREQ_PIDM = SORHSCH.SORHSCH_PIDM
and STVSBGI.STVSBGI_CODE = SORHSCH.SORHSCH_SBGI_CODE
and bb.rrrareq_pidm = RRRAREQ.RRRAREQ_PIDM
and bb.rrrareq_aidy_code = RRRAREQ.RRRAREQ_AIDY_CODE
and &GO_BUTTON is null
and SPRIDEN.SPRIDEN_CHANGE_IND is not null
and RRRAREQ.RRRAREQ_AIDY_CODE like '0809'
and RRRAREQ.RRRAREQ_TREQ_CODE like 'CPRMS'
and (Case
When &ALL_STUDENTS like '1'
THEN SPRIDEN.SPRID/*this is the exact spot referenced in the error,
there should be no space here*/EN_ID LIKE 'A%'
When &UNSAT_REQS like '1'
Then (bb.rrrareq_trst_code like 'I'
or bb.rrrareq_trst_code like 'E')
When &NO_FAFSA like '1'
Then RRRAREQ.RRRAREQ_PIDM not in
(Select bb.rrrareq_pidm
from rrrareq cc
Where cc.rrrareq_aidy_code=RRRAREQ.RRRAREQ_AIDY_CODE
and cc.rrrareq_treq_code = 'FAFSA'))
order by STVSBGI.STVSBGI_DESC,
SPRIDEN.SPRIDEN_LAST_NAME |
|
|
|
| Re: ORA 00905 missing Keyword [message #334772 is a reply to message #334759] |
Thu, 17 July 2008 23:11  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Before using CASE expression, you shall first study its syntax, as stated in documentation, found e.g. online on http://tahiti.oracle.com/.
SQL Reference
Chapter 6 Expressions
CASE Expression
Note the difference between expression, which is required after THEN keyword, and condition which you actually use there. As your conditions are different for each WHEN branch, CASE is not suitable here; you shall use simple combination of logical conditions, like ( ( &ALL_STUDENTS = 1 AND <condition after then> ) OR
( &UNSAT_REQS = 1 AND <condition after then> ) OR
( &NO_FAFSA = 1 AND <condition after then>) )
If your substitution variables would contain non-numeric characters, it would be good to enclose them between single quotes (as you use string comparison on them). Otherwise use numeric comparison as used in the preceding code.
Also using LIKE without wildcard in mask is not nice; why not simply test on equality (=)?
|
|
|
|
| Re: ORA 00905 missing Keyword [message #334806 is a reply to message #334759] |
Fri, 18 July 2008 01:50 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Or, if you insist on CASE expression, you may nest them as CASE WHEN &ALL_STUDENTS = 1 THEN
CASE WHEN <condition after then> THEN 1 ELSE 0 END
WHEN &UNSAT_REQS = 1 THEN
CASE WHEN <condition after then> THEN 1 ELSE 0 END
WHEN &NO_FAFSA = 1 THEN
CASE WHEN <condition after then> THEN 1 ELSE 0 END
END = 1
Be aware, that the result will be different than the one from my previous post when more parameters are set to 1.
Choose the variant which fits the requirements.
|
|
|
|
|
|
|
|
| Re: ORA 00905 missing Keyword [message #335238 is a reply to message #334759] |
Mon, 21 July 2008 09:45 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> As shown in that Article, you can even have multiple conditions for each When clause
| Quote: | CASE {value or expression}
WHEN {value}
THEN {something}
[WHEN...]
[THEN...]
[ELSE...] --<-- NULL if not specified and no WHEN tests satisfied
END
|
Wrong. There is no reference about conditions in the statement, but expressions (or "values" or "something" - the terminology is a little vague there).
The only word condition is used in | Quote: | A simple CASE expression takes the following format. As with all CASE expression and statement formats in this article, it will evaluate from top to bottom and "exit" on the first TRUE condition.
|
, which means condition build as comparison of expressions (values) in CASE and WHEN sections.
You shall re-read the links I posted about conditions and expressions to notice their difference.
You may argue, this is rather semantics error; but, as conditions and expressions are syntactically different, it is recognized as syntax error.
|
|
|
|
| Re: ORA 00905 missing Keyword [message #335240 is a reply to message #334759] |
Mon, 21 July 2008 10:09 |
doicomehereoften1
Messages: 13
Registered: June 2008
Location: CO
|
Junior Member |
|
|
Well, I don't know what's wrong then. By my reading of the documentation, it should work. It clearly states in the SQL reference under the bold type that reads searched_case_expression::= (which is what I've referred to in each of my posts in this thread) this:
| SQL Reference | In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, Oracle returns else_expr. Otherwise, Oracle returns null.
|
You will note that it is SEARCHED not SIMPLE. They are two different things.
Here is an example of searched case expression (not simple) from the above link that I posted:
CASE
WHEN column IN (val1, val2)
AND another_column > 0
THEN something
WHEN yet_another_column != 'not this value'
THEN something_else
END
So I'm sorry, but according to this, what I posted first, except for forgetting to put END in, it should work. I did just catch that, and that may make a difference, but I can't test that right now, the database server crashed and they're working on getting it back up and running.
|
|
|
|
| Re: ORA 00905 missing Keyword [message #335242 is a reply to message #335238] |
Mon, 21 July 2008 10:21 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Yes, but I suppose they mean expression in general, just want to distinguish them in describing text.
SQL> select case when 1 = 1 then 3+5 else 6-4 end from dual;
CASEWHEN1=1THEN3+5ELSE6-4END
----------------------------
8
| flyboy wrote on Mon, 21 July 2008 16:45 | You shall re-read the links I posted about conditions and expressions to notice their difference.
|
SPRIDEN.SPRIDEN_ID is expression.
'A%' is expression.
SPRIDEN.SPRIDEN_ID LIKE 'A%' is condition.
You used condition in WHEN clause, which is (and ever was) wrong. Period.
In the end, I will show example using expression in WHERE condition (which is wrong too):
SQL> select * from dual where 'asdf';
select * from dual where 'asdf'
*
ERROR at line 1:
ORA-00920: invalid relational operator |
|
|
|
|
|
| Re: ORA 00905 missing Keyword [message #335353 is a reply to message #334759] |
Mon, 21 July 2008 23:04 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> Ok, that makes way more sense.
More than what? You were told this from the very first post in this thread.
| flyboy wrote on Fri, 18 July 2008 06:11 | Note the difference between expression, which is required after THEN keyword, and condition which you actually use there.
|
Moreover, even the link you posted contains reference to Oracle SQL Reference with exact CASE syntax and semantics.
I showed you two ways how to write the condition. For some input, their result will not be the same.
I am just curious whether you understand their difference and which one fits your requirements.
|
|
|
|
Goto Forum:
Current Time: Wed Mar 12 01:44:15 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
