Forum Stats

  • 3,814,862 Users
  • 2,258,920 Discussions
  • 7,892,880 Comments

Discussions

Parameter passing in query

Muhammad Masoom Ansari
Muhammad Masoom Ansari Member Posts: 77 Blue Ribbon

Hi,

I have an employee table with CNIC# column. if pass dummy parameter in query as 'YES', query find those records having cnic# (cnic# is not null), and if parameter pass with 'NO' then query should find those records that have no cnic# (cnic# is null). if don't pass any parameter then all records should be viewed .

I tried with decode function but I think it don't support the "not null". such as

select emp_id,emp_name,emp_cnic

from  employee_view a

where  emp_cnic =decode(:p_cnic,'YES',emp_cnis is not null,'NO',emp_cnic is null,emp_cnic)

Oracle 11gR1

Many thanks for the provision of any opinion

Regards.

Tagged:

Best Answer

  • cormaco
    cormaco Member Posts: 1,898 Silver Crown
    Answer ✓

    You can write it down explicitly like this:

    select * from employee_view  
    where 
        :p_cnic = 'YES' and emp_cnic is not null or 
        :p_cnic = 'NO'  and emp_cnic is null
        
    

Answers