Oracle Database Discussions

Hello,

I have a report in ApEx with a multiple selection box. When I make more than one selection, it will save the data seperated with a colon. In my table it will look like this:

ID NAME SHR_ID_FROM
1 TEST 1:3

Where in the selection column the two values are stored. These numbers are linked to a name somewhere else in the database. When I have only one selection, there is no error and the name is given correctly. The report will then look like this:

ID NAME SHR_ID_FROM
1 TEST SELECTION1

When I have more than one selection and data is saved with colon, I cannot get the report to give me the data I need. It will give the ORA-01722: Invalid Number error.

I want the report to give me the following:

ID NAME SHR_ID_FROM
1 TEST SELECTION1(linked to number 1), SELECTION3(linked to selection 3)

So it has to give all selections. I am using the following SQL code to view the report

SELECT
"SRR_SERVICES"."ID" "ID",
"SRR_SERVICES"."NAME" "NAME",
(select "SRR_RQH"."NAME"
from "SRR_RQH"
where "SRR_RQH"."ID" = "SRR_SERVICES"."SHR_ID_FROM"
) as "ServiceHandler Van",
...... - rest of code -

I think the error lies in this line:
where "SRR_RQH"."ID" = "SRR_SERVICES"."SHR_ID_FROM"

And I believe the solution should be something like I have to split each number, remove the colon and make sure it is send to the report with the use of a comma to seperate each name. But my sql knowledge is not very good, so I have no clue on how to solve this issue.

Thanks,
Wijnand

Edited by: user8726418 on 15-sep-2010 1:38