Join SQL view with APEX item as the join condition

Hi Experts

I'm trying to create a report in Oracle APEX (version 3.2.1) and the report should retrieve values from a database view based on what the user selects into the shuttle box.

I created the following join:

select * from
select COLUMN_1, COLUMN_2
from TABLE
where upper("COLUMN_1") = upper(:SHUTTLE_ITEM)

Open in new window


and below is the code that was generated by APEX in the "source" box.

select * from
( select COLUMN_1, COLUMN_2
from TABLE
where upper("COLUMN_1") = upper(:SHUTTLE_ITEM))
where
(instr(upper("COLUMN_1"),upper(nvl(:SEARCH,"COLUMN_1"))) > 0 or
 instr(upper("COLUMN_2"),upper(nvl(:SEARCH,"COLUMN_2"))) > 0 )

Open in new window


The problem is that, as you probably know, the shuttle item separates the items with a colon ( : ), therefore
 the join does not work correctly. Could you please provide some help in creating the correct join syntax.

I've tried utilizing the following utility but I think I was doing something wrong.
Oracle string to text utility APEX_UTIL
Daniel993Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Pls try this . You first need to separate the items by :(semicolon) coming out from the shuttle box then join it will the other table.

I have taken e.g. as 'p:Q'

SELECT Shutles
FROM
(
	SELECT UPPER(REGEXP_SUBSTR('p:Q', '[^:]+', 1, LEVEL)) AS Shutles
	   FROM DUAL
	CONNECT BY REGEXP_SUBSTR('p:Q', '[^:]+', 1, LEVEL) IS NOT NULL
);

Open in new window


Now just join it with the other table/View

--


SELECT COLUMN_1, COLUMN_2
FROM TABLE1 t
INNER JOIN 
(
	SELECT Shutles
	FROM
	(
		SELECT UPPER(REGEXP_SUBSTR(SHUTTLE_ITEM, '[^:]+', 1, LEVEL)) AS Shutles
		   FROM DUAL
		CONNECT BY REGEXP_SUBSTR(SHUTTLE_ITEM, '[^:]+', 1, LEVEL) IS NOT NULL
	);
)m
ON m.Shutles = t.UPPER("COLUMN_1")

--

Open in new window

0
 
Daniel993Author Commented:
Hi Pawan.

Sorry for the delay, that worked perfectly, thank you.
0
 
Pawan KumarDatabase ExpertCommented:
Hi, Great that it worked. enjoy !!
0
 
Daniel993Author Commented:
worked perfectly
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.