Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Parameter passing in query

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)
image.pngOracle 11gR1
Many thanks for the provision of any opinion
Regards.
This post has been answered by cormaco on Aug 23 2021
Jump to Answer

Comments

715399
Hi Brian,

Do you still get the exception if you remove the DISTINCT keyword or add the ORDER BY variable to the select list?

Cheers,
Vladimir
715399
Hi Brian,

I just noticed you were using Jena 2.0. You also might want to try using the latest version of the Jena Adaptor (works with Oracle 11.1.0.7 and 11.2)

Cheers,
Vladimir
1 - 2

Post Details

Added on Aug 23 2021
#parameter
10 comments
357 views
4 mentions