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
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
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.
ASKER
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.
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:
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
That's pretty much it.
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 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
That's pretty much it.
ASKER
gatorvip, following your example.
I have done the following on a single page,
Region 1 (Name Inbound)
SQL query
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
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 ?
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
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
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 ?
ASKER
on the hidden item i also made the
source and default value #VENDOR#
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#Something here needs to specify that your target is P7_VENDOR, just follow the steps I outlined in my previous post.
I created a hidden item and named it P7_VENDOR
Under Source for Source value or expression, typed #VENDOR#
ASKER
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. ?
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. ?
ASKER
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.
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you sir
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