Queries and Lookups

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, dont 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 cgvMDPT_MV_DEPARTMENT (DN
UMBER,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