220 likes | 223 Vues
Week 7 March 8. SQL: Chronological Sort, SUBSTRing, ROUND Dynamic SQL: Host and Lexical Reference Variables Overview: Drill-down, Roll-up Reports. Chronological sort. Month sequence in the calendar. Order by the month’s sequence. Without Chronological Order. Length. Beginning position.
E N D
Week 7March 8 SQL: Chronological Sort, SUBSTRing, ROUND Dynamic SQL: Host and Lexical Reference Variables Overview: Drill-down, Roll-up Reports
Chronological sort Month sequence in the calendar Order by the month’s sequence
Length Beginning position SUBSTR captures only parts of a string
Without SUBSTR Wasted space
ROUND rounds a number to a specified number of decimal places Number of decimal places
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
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
Creating a Lexical Reference Variable • Create a lexical reference variable under User Parameters of the Data Model (in the Object Navigator) Select Create
Create a User Parameter Default name Select the new parameter, right-mouse click and select Property Palette
Change the Properties Change the name Change the data type to character Assign an initial value (optional)
During Runtime... Host variable Lexical reference variables (default values) Host variable
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
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
+ + =