Week 7 March 8

1
Week 7March 8

• SQL Chronological Sort, SUBSTRing, ROUND
• Dynamic SQL Host and Lexical Reference Variables
• Overview Drill-down, Roll-up Reports

2
(No Transcript)
3
Chronological sort
Month sequence in the calendar
Order by the months 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)

SQLgt 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(sa
les_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
(No Transcript)
20
Annual
21
Quarters
22
Months within a Quarter