Oracle PLSQL using a dynamic variable in a where clause

Question

For testing in Toad, I have the following code

select ...
from ...
where term_code = :termcode AND
        (
          case 
           when :theSubject is not null then SUBJ_CODE = :theSubject
           else 1 = 1
          end
        )          
AND ptrm_code <> 8

In short: If theSubject is not entered (is null) I want to display all the courses, otherwise I want to display only those where subject_code is the same as the one entered in the variable window in Toad.

But I get an error: [Error] Execution (77: 68): ORA-00905: missing keyword in here: when :theCourse is not null then sect.SSBSECT_SUBJ_CODE = theCourse

A case expression lets you have a value or expression as the then or else result; not a condition, which is what you are trying to do. — Alex Poole
– Alex Poole, Commented Jun 25, 2020 at 14:52
The statement you show mentions :theSubject but the error message mentions :theCourse; thus it seems that the error message does not refer to the statement shown. Also, in the error message the second occurrence of theCourse is not preceded by a colon. ??? — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jun 25, 2020 at 14:57

GMB · Accepted Answer · 2020-06-25 14:39:28Z

3

You can use boolean logic:

where 
    term_code = :termcode 
    and (:theSubject is null or subj_code = :theSubject)

answered Jun 25, 2020 at 14:39

GMB

224k25 gold badges102 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle PLSQL using a dynamic variable in a where clause

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related