Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Select list using many-to-one relationship (Oracle Database 10g, Oracle Application Express, Windows)
Select list using many-to-one relationship [message #613918] Tue, 13 May 2014 09:44 Go to next message
mhugh
Messages: 10
Registered: May 2014
Junior Member
I have created an interactive report in APEX using the code below to bring in the table signature_bib:
select ID, Author, pub_date, pub_title, journal, keyword, PI
from Signature_Bib
where ID=NVL(:P3_ID, ID)

I have created a table sig_keywords with three variables: ID (sequentially assigned),
Key_id and keyword. For each ID from signature_bib, there are multiple KEY_IDs with a
unique keyword, e.g., KEY_ID=1 and keyword=Fish, KEY_ID=1 and keyword=nitrogen, etc. So,
this is a many-to-one relationship.

I want to create a 'keyword' filter on the signature_bib report using a select list from sig_keywords
to select signature_bib entries. Key_id from sig_keywords relates to ID in signature_bib. I have
tried the code below to create the select list page control under the filter. The filter shows up, but
there is no keyword list, only a NULL value. There is no error message.
How do I connect the two tables to get a keyword list? If I run the
basic code in the SQL command region, it runs without a problem.

select DISTINCT k.keyword d ,b.ID r
from sig_keywords k, signature_bib b
where b.ID =k.key_id and
b.ID=:P3_id
group by k.keyword , b.ID
order by k.keyword

Report message to a moderator

Re: Select list using many-to-one relationship [message #613952 is a reply to message #613918] Tue, 13 May 2014 16:33 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try to comment this line out: 
and b.id = :P3_ID
What happens? Does anything show in the select list? If so, it means that P3_ID's value isn't saved in session state so you'll have to do it yourself.

By the way (not related to your problem): remove GROUP BY clause, it does nothing in this context.

[EDIT] As you didn't reply (yet?), I don't know whether you managed to fix it or not. Anyway: "you'll have to do it yourself" can be done in a simple manner by putting P3_ID into select list's "Cascading LOV Parent Item(s)" property.

[Updated on: Wed, 14 May 2014 00:13]

Report message to a moderator

Re: Select list using many-to-one relationship [message #614014 is a reply to message #613952] Wed, 14 May 2014 07:26 Go to previous messageGo to next message
mhugh
Messages: 10
Registered: May 2014
Junior Member
Thanks for your reply. As I was modifying my code yesterday, I actually ended up with the code below which
accomplishes what your advice suggests that my code wasn't doing. The 'b.ID like....' does hold
the keyword in session state as this bit of code is also in the table query. However,
the filter is still not working quite right.....

select k.keyword d ,b.ID r
from sig_keywords k, signature_bib b
where b.ID =k.key_id and
b.ID like '%'|| :P3_ID ||'%'
order by k.keyword

Thanks again,
Melissa Hughes

Report message to a moderator

Re: Select list using many-to-one relationship [message #614015 is a reply to message #614014] Wed, 14 May 2014 07:35 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't understand what you meant by saying:Quote:

The 'b.ID like....' does hold the keyword in session state as this bit of code is also in the table query

Report message to a moderator

Re: Select list using many-to-one relationship [message #614018 is a reply to message #614015] Wed, 14 May 2014 07:52 Go to previous messageGo to next message
mhugh
Messages: 10
Registered: May 2014
Junior Member
This is the code to create the APEX table that is in the keyword select list above.

select ID, Author, pub_date, pub_title, journal, PI
from Signature_Bib
where PI=NVL(:P3_PI, PI) and b.ID like '%'|| :P3_id ||'%'

Thanks!

Report message to a moderator

Re: Select list using many-to-one relationship [message #614075 is a reply to message #614018] Thu, 15 May 2014 13:28 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You use terminology I'm not familiar with. Now, either I don't follow the newest phrases, or you are fabricating them.

Quote:

This is the code to create the APEX table


What is "the APEX table"?

While I'm not being able to understand everything you say, I kind of forgot what the actual problem is. I *think* that it is
Quote:

the filter is still not working quite right

Could you explain that? What is not quite right, and how does right look like? If possible, create a test case (CREATE TABLE and INSERT INTO several sample records), run your query which shows what it currently returns, and explain the rule(s) which lead to correct result.

Report message to a moderator

Re: Select list using many-to-one relationship [message #614107 is a reply to message #614075] Fri, 16 May 2014 08:13 Go to previous messageGo to next message
mhugh
Messages: 10
Registered: May 2014
Junior Member
Thank you for your interest in my posts and taking the time to reply.
I have to put aside this project for a time. So, I don't want to
take up more of your time with further posts. Thank you again.

Report message to a moderator

Re: Select list using many-to-one relationship [message #614118 is a reply to message #614107] Fri, 16 May 2014 15:16 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem. I'm sorry I failed to understand you better.

Report message to a moderator

Previous Topic: Tab is not visible
Next Topic: WANT TO INSERT JSON VALUE IN A TABLE
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Dec 11 17:39:11 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.