1 / 43

Subquery and Thresholds: Graphics Builder and Forms

Learn how to use subqueries and thresholds in the Graphics Builder and Forms tool. Create graphs and forms based on specific criteria and thresholds.

Download Presentation

Subquery and Thresholds: Graphics Builder and Forms

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. Week 10April 5 Subquery and Thresholds Graphics Builder and Forms

  2. The Importance of ORDER BY

  3. Relying on Fate… View name

  4. What’s 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. What’s 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.8896126.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) > 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 Item’s Property Right-mouse click the item (e.g., ITEM5) and select Property Palette

  28. Change the Item Type Property 1. Change the Item Type propertyfrom Text Item to Chart Item

  29. Change the Canvas Property 2. Change the Canvas property from <Null> 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 Developer’s \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

More Related