Hello: I am having trouble working with a multi-select variable in a CA Service Catalog data object. My SQL is below.
select
case when cnt.inactive = 0 then 'ACTIVE' else 'INACTIVE' end record_status
,cnt.cnt_name_full name_full
,cnt.pri_phone_number phone_number
,cnt.email_address
,cnt.zemp_id employee_id
,cnt.ct_org agency
,cnt.ct_loc_name location_name
,cnt.ct_jobtitle job_title
,cnt.alternate_identifier alt_info
,case
when cnt.supervisor_uuid is not null
then to_char(cnt.supervisor_name) || ' (' || to_char(cnt.supervisor_email_address) || ')'
else ''
end supervisor_name
,cnt.vendor_name
,cnt.contact_uuid cnt_uuid
,cnt.last_name
,cnt.first_name
,cnt.middle_name
,cnt.organization_uuid agency_uuid
,cnt.location_uuid loc_uuid
,cnt.job_title job_title_id
,cnt.supervisor_contact_uuid supervisor_uuid
,cnt.vendor_uuid
from zview_contact_agp cnt
where
instr(lower(cnt.last_name),'-dupe-',1) = 0
and cnt.last_name like 'Smith%'
and cnt.inactive in (%z_contact_status%)
I set-up z_contact_status as an integer, multi-select drop-down with values (Active = 0, Inactive = 1) and a comma delimiter.
It works fine if I only choose one item. If I select both, it only gives me active records (the first item in the list). I have no idea why this is not working. Please help.