[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle Apex: Linking to subquery on a query result

Posted on 2012-08-15
14
Medium Priority
?
730 Views
Last Modified: 2012-08-17
I'm not sure where to even start. But I will explain as best as I can

in Apex I will have a Query that will give a set of results

lets say the results are

Yankees
RedSox
Angels

I want these results to be a link to a subquery that will be based on values of the link

so if i click Yankees
it take me to another query that has the team members and age for example.

any direction greatly appreciated
0
Comment
Question by:futureDBA
  • 8
  • 4
  • 2
14 Comments
 
LVL 21

Expert Comment

by:flow01
ID: 38296214
create another page that shows the team-information based on the value of a page-item
on your original page add  an item of type link to the team-rows and provide the page you want to link to and the arguments to use
0
 

Author Comment

by:futureDBA
ID: 38296318
can i have it come up on the same page on a separate region ?
0
 
LVL 21

Expert Comment

by:flow01
ID: 38296631
Depends on what you want to do : if you don't need datamanipulation try creating a master/detail-report.   For as for as I can remember , the default (wizard) pages with datamanipulation are based on 1 query only. Apart from that you can do a lot by manipulating the pages by yourself, but then you will have to cope with all the behaviour you want and the APEX wizards normaly provide for free. You would not have asked your question here if you were that experienced.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:futureDBA
ID: 38296699
I did not claim to be that experienced.

I am looking for a path to having individual row results from a query link to results of another query which will be based on the row that was clicked on.

I have done Master Details before in apex, but i am not sure i can update records on a query, but i want to tackle one thing at a time, which right now my imediate concern is what i originally stated

individual row results from a query linking  to results of another query which will be based on the row that was clicked on.
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 38296962
You can do this on a single page, there are several ways to do it. The simplest way I can think of is with hidden page items.

I'm gonna use your example and create two regions (of type SQL Report) and a hidden page item :P1_TEAMID. The first region is, say, defined as follows:
select team_id, team_name from teams

Open in new window


Open the Report Attributes for the region then click on the edit icon for team_id. In the Column Link section put in #TEAM_ID#, Target is Page In This Application, Page is 1 (you can also set the Clear Cache field to 1, if needed). For Item1, use P1_TEAMID as the item and #TEAM_ID# as the value.

Now create your second region, say the code for that is
select player_name, player_age from team_members where team_id=:P1_TEAMID

Open in new window


That's pretty much it.
0
 

Author Comment

by:futureDBA
ID: 38297444
gatorvip, following your example.

I have done the following on a single page,

Region 1 (Name Inbound)

SQL query
select
  ship_date,
  vendor,
  sum(ord)
from wag
group by
ship_date,
vendor

Open in new window


On the Reports Attributes, I clicked on Edit for Vendor

on column link,

Link Text i typed #VENDOR# Link Attributes I did the same #VENDOR#

I created a hidden item and named it P7_VENDOR

Under Source for Source value or expression, typed #VENDOR#

I then created a second region on the page named Inbound Sub

the Region source is

select 
  custitemcode,
  sum(ord)
from wag
where vendor = ':P7_VENDOR'
group by custitemcode

Open in new window



I played with different variations where P7_vendor is

eg

where vendor = ':P7_VENDOR'
where vendor = '%:P7_VENDOR%'
where vendor = :P7_VENDOR



doesnt work, though i see the logic. am i missing something ?
0
 

Author Comment

by:futureDBA
ID: 38297452
on the hidden item i also made the

source and default value #VENDOR#
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 38297477
where vendor = ':P7_VENDOR'
That should just be vendor = :P7_VENDOR (no quotes).

Link Text i typed #VENDOR# Link Attributes I did the same #VENDOR#

I created a hidden item and named it P7_VENDOR

Under Source for Source value or expression, typed #VENDOR#
Something here needs to specify that your target is P7_VENDOR, just follow the steps I outlined in my previous post.
0
 

Author Comment

by:futureDBA
ID: 38300704
can this only be done once per page? i have a separate region where i follow your instructions, but when i update the link settings on the report attributes and hit apply. even though it says it updated, it really does  not. and the check mark next to link does not show up.

it worked fine for the first set, but on the 2nd which is on the same page but a separate region. it doesn't take link. ?
0
 

Author Comment

by:futureDBA
ID: 38300752
actually figured that out...

another problem im having is if

the results of

where item = :P1_ITEM

has a # in the query results. it will not link..

so for example

the links for

team #1
team #2
team #3
team 4
team 5

only the links for team 4 and 5 work.
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 38304736
Check to see that the hidden item doesn't have a default value set (as you indicate in a previous post, "on the hidden item i also made the source and default value #VENDOR# ") and that your source looks like the attachedblank source
0
 

Author Comment

by:futureDBA
ID: 38305546
I tried, that but when I click on links which has a value with a # in it, the results are blank, any column that doesnt have a # returns the results fine
0
 
LVL 20

Accepted Solution

by:
gatorvip earned 2000 total points
ID: 38306460
That seems to be due to HTML treating # as an anchor tag when passed via an URL. What you need to do is basically build the link yourself in SQL  and escape the #.

See this thread for more details and examples.
0
 

Author Closing Comment

by:futureDBA
ID: 38306857
thank you sir
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

830 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