• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 764
  • Last Modified:

Oracle Apex: Linking to subquery on a query result

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
futureDBA
Asked:
futureDBA
  • 8
  • 4
  • 2
1 Solution
 
flow01Commented:
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
 
futureDBAAuthor Commented:
can i have it come up on the same page on a separate region ?
0
 
flow01Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
futureDBAAuthor Commented:
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
 
gatorvipCommented:
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
 
futureDBAAuthor Commented:
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
 
futureDBAAuthor Commented:
on the hidden item i also made the

source and default value #VENDOR#
0
 
gatorvipCommented:
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
 
futureDBAAuthor Commented:
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
 
futureDBAAuthor Commented:
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
 
gatorvipCommented:
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
 
futureDBAAuthor Commented:
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
 
gatorvipCommented:
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
 
futureDBAAuthor Commented:
thank you sir
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now