Link to home
Start Free TrialLog in
Avatar of futureDBA
futureDBA

asked on

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
Avatar of flow01
flow01
Flag of Netherlands image

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
Avatar of futureDBA
futureDBA

ASKER

can i have it come up on the same page on a separate region ?
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.
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.
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.
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 ?
on the hidden item i also made the

source and default value #VENDOR#
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.
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. ?
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.
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 attachedUser generated image
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
ASKER CERTIFIED SOLUTION
Avatar of gatorvip
gatorvip
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you sir