Home » Developer & Programmer » Forms » ORA 00905 missing Keyword (10g)
ORA 00905 missing Keyword [message #334759] Thu, 17 July 2008 17:31 Go to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #335231 is a reply to message #334759] Mon, 21 July 2008 09:12 Go to previous messageGo to next message
doicomehereoften1
Messages: 13
Registered: June 2008
Location: CO
Junior Member
Sorry it took me so long to get back here, we don't work on Friday's. Cool

Anyhow, I did consult the documentation while building that query, and in the form of a Searched Case Expression (which this is) you are able to test different conditions as they may be. So I'm not really understanding why it won't work given the straightforward syntax. Condition does not have to be evaluating the same condition each time. Some examples of that are HERE in the 9i New Features Article. As shown in that Article, you can even have multiple conditions for each When clause. I'll attempt to adapt some of what you posted, but I'm not sure that I understand why it would or should be done in those forms.

Everybody else that might have some insight on this, I'd like to hear from you, too!

Very Happy
Re: ORA 00905 missing Keyword [message #335233 is a reply to message #335231] Mon, 21 July 2008 09:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe I read the link wrong, and the docs from Oracle itself, or maybe you should reread them.
There is no indication that what you do is valid.
Re: ORA 00905 missing Keyword [message #335238 is a reply to message #334759] Mon, 21 July 2008 09:45 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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
See - syntax error again.
Re: ORA 00905 missing Keyword [message #335245 is a reply to message #335242] Mon, 21 July 2008 10:38 Go to previous messageGo to next message
doicomehereoften1
Messages: 13
Registered: June 2008
Location: CO
Junior Member
flyboy

SPRIDEN.SPRIDEN_ID is expression.
'A%' is expression.
SPRIDEN.SPRIDEN_ID LIKE 'A%' is condition.




Ok, that makes way more sense. You'll have to forgive me on that one, I'm a self-learning SQL user and I've really only been at it for a few months. I'm really not surprised I missed something that otherwise should have been obvious to me. Now I get to go beat up on the certified programmers for not catching that while they were trying to help me!

Laughing
Re: ORA 00905 missing Keyword [message #335353 is a reply to message #334759] Mon, 21 July 2008 23:04 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: Using PL/SQL library object in the sub Menu
Next Topic: how to use ifrun60.EXE in forms 6i in forms 10g ?
Goto Forum:
  


Current Time: Sat Dec 03 13:41:35 CST 2016

Total time taken to generate the page: 0.10622 seconds