Week 10 April 5

1
Week 10April 5

• Subquery and Thresholds
• Graphics Builder and Forms

2
The Importance of ORDER BY
3
Relying on Fate
View name
4
Whats the Problem?
5
Ordered by record labels!
6
140 rows
All 2001
7
Paste the New Query into the Dialog Box
8
Revised Graph
Too many lines!
9
Whats a Threshold and Why Do We Need It?
10
Not much usable information
11
Reduce the Number of Record Labels
Top 7 record labels in sales
12
Create a set of threshold values
Every record label must beat every value in this
set, OR simply every record label must beat the
highest (i.e., maximum) value in this set
Highest value
13
REC 2001 2002 2003 2004
2005 --- ----------- ----------
---------- ----------- ----------- RCA
98562.88 96126.71 99773.52 112097.35
108383.42 COL 2285882.3 2336707.93 2472875.7
2635497.41 2621255.29 GRP 448870.6
469021.43 480428.06 531105.46 518445.68 GTS
156955.23 154830.01 174953.32 196814.02
200256.45 WB 571478.66 576644.41 580731.48
587747.77 563445.49 ARI 638935.25
628407.24 625556.25 644993.73 601378.96 KUC
48260.05 40662.4 36247.64 35417.3
30704.89 VER 188098.59 202036.86 209501.61
245316.62 269777.58 DOM 106147.96 120980.78
118289.17 116859.98 120437.15 LIB
42279.47 42602.71 35975.38 39944.48
57172.18 S/A 116819.02 131737.36 122668.75
137014.64 167739.88 CAP 1019419.4 1007901.07
972170.61 1005423.39 976683.56 KOK
59802.31 82005.05 78328.95 78980.63
85922.7 SHA 62003.69 81029.3 94834.46
120347.68 128159.91 DSY 104735.01 97572.02
83157.61 88339.8 98077.64 MCA
343229.06 345217.11 365019.64 392166.76
381035.82 MER 69748.69 69507.16 66263.35
61333.23 55619.23 MOT 252342.84
263438.36 259107.84 290508.9 300567.62 NAS
75892.67 91443.5 118311.33 172166.27
191768.39 PM 350717.42 354469.91 355604.56
363074.29 378489.29 WH 364718.11
374305.23 405000.86 483654.29 490804.67 CHS
62510.26 76541.45 109559.04 106997.1
93288.6 GEF 70964.37 70399.53 70265.02
85641.58 88666.07 APL 257074.33 296953.73
311523.14 324438.59 291354.25 ATL 1017378.32
1023850.51 993520.05 1082629.88
1117300.7 E/A 83585.13 81487.22 99320.6
110026.61 118170.16 EMI 70391.52
71098.97 75852.13 69773.29 55397.99 POL
469854.45 467419.28 485843.36 502552.77
453941.7
Yes
Yes
Yes
Yes
YEAR THRESHOLD ---- ---------- 2001
330283.016 2003 346523.920 2005 369748.584 2002
337903.903 2004 371730.234
Yes
Highest
Eliminate the ones with red
Yes
Yes
14
Working Backwards

• Create a set of all record labels that have all
  sales values greater than those found in the set
  of thresholds

All record labels must beat all threshold values
(or simply the minimum sales value must beat the
maximum threshold value)
15
Retrieve all record labels annual sales of those
with minimum sales exceeding all values found in
the thresholds
Subquery Select only those record labels that
exist in the set that contains record labels with
sales values greater than all threshold values
35 rows
16
Anatomy of the Subquery
Basic Query for the Data Model
select category, year, sales from view1
alias1 where exists (select category,
min(sales) from view1 alias2 where
alias1.category alias2.category group by
category having min(sales) gt all (select
threshold from view2))
Retrieves only those category members that are
found in the set produced by the first subquery
Creates a set of category members whose minimum
sales must be greater than all threshold values
? Creates a set of threshold values
17
In Graphics Builder
18
A Few Simple Steps for Creating a Graph(From
Last Week)

• Build the initial SQL command in SQL Plus
• In Graphics Builder
• Build the data model
• Build the graph
• Select the graph type
• Assign the independent and dependent to the
  categories and values, respectively
• Format the various components of graph as needed
• Save and run the graph

19
Layout Editor
Tool palette
Chart tool - Create data model and graph
20
A few steps later

• After Building the Graph

21
Generating an OGR File
? From the File menu, select Administration,
Generate and File System
Be sure to generate a new OGR file every time a
change is made to the OGD file.
22
Generating an OGR File
? Save the file
23
In Form Builder
24
Create a Chart Item on the Canvas
Select Data Block and the create tool in the tool
palette
25
Create a Chart Item on the Canvas
Select Build a new data block manually and click
on OK
26
Create a New Item in the Data Block
Select Items and click on the Create Tool in the
tool palette
27
Change the Items Property
Right-mouse click the item (e.g., ITEM5) and
select Property Palette
28
Change the Item Type Property
1. Change the Item Type property from Text Item
to Chart Item
29
Change the Canvas Property
2. Change the Canvas property from ltNullgt to the
canvas name (e.g., CANVAS2)
3. Close the Property Palette
30
Expand the Chart Item on the Canvas
31
Attach the OG.PLL File
Select Attached Libraries and click on the create
tool in the tool palette
32
Locate and Attach the OG.PLL File
1. Click on Browse and search for the file
2. Select the file and click on Open
Hint. Locate the OG.PLL file through a search in
Windows Explorer and copy it into your working
directory. It may be located under Developers
\tools\devdem60\demo\forms subdirectory.
33
Attach the OG.PLL File
3. Click on Attach and select Yes (remove path)
34
1. Add a push button 2. Right-mouse click the
push button and select PL/SQL Editor
35
Select a Trigger
Select the WHEN-MOUSE-CLICK trigger and click OK
36
PL/SQL
Open and close the OGR file
Compile the PL/SQL code
37
Test the Form
38
Test the Form
39
Modify the Canvas Properties
40
Modify the Chart Item Properties
Remove the bevel
Change the Background Color to match the canvas
41
First Graph
42
PL/SQL
43
Second Graph