1 / 24

Queries and Lookups

Queries and Lookups. Simple Department Form. The Simple Form. Insert. Save. LOVBUT. Adding a Department. Query By Example. More Querying. Fetch all departments that begin with ‘SAL’. Adding Manager Info. Revised Module -- Insertion. Querying with Revised Module.

ulysses-mcdaniel
Download Presentation

Queries and Lookups

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Queries and Lookups

  2. Simple Department Form

  3. The Simple Form Insert Save LOVBUT

  4. Adding a Department

  5. Query By Example

  6. More Querying Fetch all departments that begin with ‘SAL’

  7. Adding Manager Info

  8. Revised Module -- Insertion

  9. Querying with Revised Module • Manager Fields do not fill in automatically • So we would like to to improve the querying capability • Unless form is primarily for data entry. • One method: base form on a view

  10. Defining a View

  11. Basing Module on View

  12. Module Diagram • View is considered a table on module side. • No internal structure.

  13. The Form • Querying now works as desired • But no lookup LOV for data entry! • Maybe we can add a lookup

  14. Add a Lookup Note a view can have a primary key and foreign keys!

  15. The New Version

  16. Unfortunately ... • Form Compiler Blows UP • Diagnostic messages about .cpp files • ‘Tell your Oracle Representative’ • Lesson, don’t add lookups to a table already in a view! • But there is another way, closely related ...

  17. Module Views

  18. Module View Looks Like Ordinary Module

  19. Module Component API • Oracle Designer creates the view for you, • when you tell it to. • Use MCAPI. • Creates DDL for the view

  20. The Underlying View CREATE OR REPLACE VIEW cgv$MDPT_MV_DEPARTMENT (DNUMBER,DNAME,. . ., L_EMP_LNAME) AS SELECT DPT.DNUMBER DNUMBER, DPT.DNAME DNAME,. . . ., L_EMP.LNAME L_EMP_LNAME FROM EMPLOYEES L_EMP,DEPARTMENTS DPT WHERE DPT.EMP_EMPID = L_EMP.EMPID (+) /

  21. The View Based Form

  22. Querying

  23. Inserting New Data

  24. Adding an Item Group

More Related