Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

ss

Oracle Tips by Burleson 

Parent Child Select Lists

There have been several questions on how to do this on the HTML DB forum, so I figure I’d better cover the topic.  A Parent/Child select list is two select lists working together.  The child select list is populated based on the selection from the parent select list.  So, if there are stores and departments, it would be desirable to select a store and then have the child select list populate with all the departments in that store.  Furthermore, it would not be desirable to have the departments select list populated if there were no selection in the store select list.

Figure 10.23 shows a region having two select lists.  The following exercise shows how to create these two select lists.  To follow along, create a page in the Easy HTML DB Book application with the page ID equal to 2908.  To create the tables and data use the file name parent_child_tables.sql in the online code depot.

Creating the Store Select List

1.       Click on the create item button in the Items area of the Page Rendering region.

2.       Choose the Select List option and click Next.

3.       Choose the Select List with Redirect option and click Next.

Use of this option is not desirable in all cases.  The reason is if the values in page items have not been saved in session state, the values will be lost performing a submit when the page is rendered again from the redirect.  When page item values are to be saved in session state, the Select List with Submit option should be chosen.

4.       On the Display Position and Name page:

  •  Item Name: P2908_STORE_ID

  • The rest can stay with the defaults and then click Next.

5.       On the List of Values page:

  • Display Null Option: YES

  • Null Text: - Select a Store -

  • Null Value: -1

  • List of Values Query:

select name d, store_id r
from  store
order by 1

  • Click Next.

This case uses a query, as opposed to an LOV, to keep it simple.  In reality it is better to use an LOV.

6.       On the Item Attributes page, fill in the desired information and click Next.  There is nothing special here pertaining to the parent / child relationship.

7.       On the source page, enter -1 as the text for the Default value and click the Create Item button.  A numeric value is used here since the STORE_ID column is numeric.

Creating the Department Select List

1.       Click on the create item button in the Items area of the Page Rendering region.

2.       Choose the Select List option and click Next.

3.       Choose the Select List option again and click Next.

This time there is no special action needed from the Select List when an item is selected.

4.       Enter the Item Name, Sequence, and Region information and click Next.

5.       On the List of Values page:

  • Named LOV: leave this alone

  • Display Null Option: YES.

  • Null Text: - Select a Department -

  • Null Value: -1 because the column return value is numeric.

  • List of Values Query: Enter the text shown in Figure 10.24

  • Click Next.

This time something special must be done for the List of Values page.  It is not desirable to create an LOV with a WHERE clause for this, so the select statement for the LOV will be provided right in the item definition as shown in Figure 10.18.  The query uses the page item name from the parent select list, P2908_STORE_ID, to only select the departments associated with the store selected.

6.       Again, the Item Attributes are not important for the outcome of the parent / child select list.  Leave the default values and click Next.

7.       On the Source page, enter X for the Default value and click the Create Item button.

8.       Run the report and see the Parent / Child select lists in action.


The above book excerpt is from:

Easy HTML-DB Oracle Application Express

Create Dynamic Web Pages with OAE

ISBN 0-9761573-1-4   

Michael Cunningham & Kent Crotty

http://www.rampant-books.com/book_2005_2_html_db.htm

 

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks