Hello there,
I have this query to get the results when the user make a search query:
select * from (
select
"ID",
"ID" ID_DISPLAY,
"SHIFT_DATE",
"SHIFT",
"OFFENSE_ID",
"DESCRIPTION",
"ANALYST",
"STATUS",
"SUBSTATUS"
from "#OWNER#"."IDSIEM_OFFENSES")
where
OFFENSE_ID IN(:P223_OFFENSES) AND
(
instr(upper("DESCRIPTION"),upper(nvl(:P223_DESCRIPTION,"DESCRIPTION"))) > 0
)
AND
(
instr(upper("SHIFT"),upper(nvl(:P223_SHIFT,"SHIFT"))) > 0
)
AND
(
instr(upper("SUBSTATUS"),upper(nvl(:P223_SUBSTATUS,"SUBSTATUS"))) > 0
)
AND
(
instr(upper("ANALYST"),upper(nvl(:P223_ANALYST,"ANALYST"))) > 0
)
AND
(
instr(upper("SHIFT_DATE"),upper(nvl(:P223_SHIFTDATE,"SHIFT_DATE"))) > 0
)
AND
(
instr(upper("STATUS"),upper(nvl(:P223_STATUS,"STATUS"))) > 0
)
ORDER BY OFFENSE_ID DESC
The thing that I want to do is to put multiple values on the field P223_OFFENSES when I search. For example an offense is a number, so I would like to put in the search field 1111, 3333, 4444, 5555 and the report shows me those 4 offenses in the report. The search operation works only when I put only 1 offenses, but when I put more than 1 separated by comma, it shows me this error: report error:ORA-01722: invalid number. That's why because is a number and the comma character is not allowed, how can I achieve this? Thank you in advance.
Regards, Bernardo