Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Join SQL view with APEX item as the join condition

Posted on 2016-10-08
4
Medium Priority
?
90 Views
Last Modified: 2016-10-14
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
0
Comment
Question by:Daniel993
  • 2
  • 2
4 Comments
 
LVL 38

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41835442
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
 

Author Comment

by:Daniel993
ID: 41843100
Hi Pawan.

Sorry for the delay, that worked perfectly, thank you.
0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41843142
Hi, Great that it worked. enjoy !!
0
 

Author Closing Comment

by:Daniel993
ID: 41844052
worked perfectly
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question