Search code examples
sqloracle-databaseplsqlwhere-clause

Oracle PLSQL using a dynamic variable in a where clause

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

Solution

  • You can use boolean logic:

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