how to use LIKE operator in the QUERY in interactive report?

October 17, 2023

How can I use LIKE operator in oracle apex interactive report QUERY in where condition

I tried this query

SELECT APPT_NO , REQUESTED_OPERATION , CLINICIAN_NAME , CLINICIAN_NAME_AR , STATUS , 
START_DATE  , CREATED_DATE , app_comment,
PRACT_FIRST_NAME_A || ' '|| PRACT_FAMILY_NAME_A as "Doctor"
FROM appt_notify
WHERE   PATIENT_Data LIKE :P39_IDNO

but its not working when I tried static data its working

SELECT APPT_NO , REQUESTED_OPERATION , CLINICIAN_NAME , CLINICIAN_NAME_AR , STATUS , START_DATE  , CREATED_DATE , app_comment,
PRACT_FIRST_NAME_A || ' '|| PRACT_FAMILY_NAME_A as "Doctor"
FROM appt_notify
WHERE   PATIENT_Data LIKE '%10280%'

How can I use LIKE with percentage %PAGE_ITEM% in where condition?

>Solution :

Your code will work if you pass :P39_IDNO as the value %10280% with the leading and trailing % to perform wild-card matches.

If you are passing the value 10280 then you want to concatenate the leading and trailing % characters:

SELECT APPT_NO,
       REQUESTED_OPERATION,
       CLINICIAN_NAME,
       CLINICIAN_NAME_AR,
       STATUS, 
       START_DATE,
       CREATED_DATE,
       app_comment,
       PRACT_FIRST_NAME_A || ' '|| PRACT_FAMILY_NAME_A as "Doctor"
FROM   appt_notify
WHERE  PATIENT_Data LIKE '%' || :P39_IDNO || '%'