1 / 22

Dynamic SQL: Using SUBSTR, ROUND, and Lexical Reference Variables for Chronological Reports

Explore the power of Dynamic SQL in ordering data chronologically, utilizing SUBSTR to extract parts of strings, ROUND function to control decimal places, and Lexical Reference Variables for flexible queries in SQL. Learn to create drill-down and roll-up reports for detailed insights.

jameshammond
Download Presentation

Dynamic SQL: Using SUBSTR, ROUND, and Lexical Reference Variables for Chronological Reports

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 7March 8 SQL: Chronological Sort, SUBSTRing, ROUND Dynamic SQL: Host and Lexical Reference Variables Overview: Drill-down, Roll-up Reports

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

  3. Without Chronological Order

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

  5. Without SUBSTR Wasted space

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

  7. Without ROUND

  8. Order by Quarter

  9. 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

  10. 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

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

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

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

  14. Insert the Lexical Reference Variables(User Parameters)

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

  16. 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

  17. 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

  18. + + =

  19. Annual

  20. Quarters

  21. Months within a Quarter

More Related