1. Week 7March 8 SQL: Chronological Sort, SUBSTRing, ROUND Dynamic SQL: Host and Lexical Reference Variables Overview: Drill-down, Roll-up Reports

3. Chronological sort Month sequence in the calendar Order by the month’s sequence

4. Without Chronological Order

5. Length Beginning position SUBSTR captures only parts of a string

6. Without SUBSTR Wasted space

7. ROUND rounds a number to a specified number of decimal places Number of decimal places

8. Without ROUND

9. Order by Quarter

10. Dynamic SQL • As opposed to static SQL, dynamic SQL allows the user to specify parts of the query (i.e., column names, table names, conditions, etc.) during runtime • For example (static SQL): SQL> select manufacturer_code, to_char(sales_month_97,'q'), 2 sum(sales_revenue_97), sum(sales_revenue_98) 3 from sales_97_98 4 where lower(manufacturer_code) = 'son' 5 group by manufacturer_code, to_char(sales_month_97,'q') 6 order by to_char(sales_month_97,'q'); The same columns, table and condition are used

11. Dynamic SQL • With dynamic SQL, parts of the query can be substituted by variable names • During run time, the user will specify a value for P_time_interval Lexical reference variable name Host variable Lexical reference variable designator select manufacturer_code, &P_Time_interval, sum(sales_revenue_97), sum(sales_revenue_98) from sales_97_98 where lower(manufacturer_code) = :P_manufacturer_code group by manufacturer_code, to_char(sales_month_97,'q') order by &P_Time_interval; Host variable designator

12. Creating a Lexical Reference Variable • Create a lexical reference variable under User Parameters of the Data Model (in the Object Navigator) Select  Create

13. Create a User Parameter Default name Select the new parameter, right-mouse click and select Property Palette

14. Change the Properties Change the name Change the data type to character Assign an initial value (optional)

15. Insert the Lexical Reference Variables(User Parameters)

16. During Runtime... Host variable Lexical reference variables (default values) Host variable

17. Drill-Down, Roll-up Reports • A drill-down report is actually two or more reports working together • The top-level report is like a master record • Launches a report that provides more details about the data in its current record • Generally, the detail report displays information related to the master • Provides details for a single record, a group of records, or the report as a whole • Master report "links" to the detail report by passing parameters that control the execution of the detail Master Detail

18. Drill-Down Reports Master Report (Annual) Data Model 1 Parameter-list1 Drill-down Report (Quarters) Data Model 2 Parameter-list2 Drill-down Report (Months) Data Model 2

19. + + =

20. Annual

21. Quarters

22. Months within a Quarter