By: Arif Khan
Hi there. Some time while working on Oracle Query we need to extract data from database using date range. This scenario often arise while extracting financial and analytical data especially in banks and other financial sector. To accomplish this talk we need to generate a date cycle, by which we can pass loop like dates to data extracting inner query.
Today we will discuss about creating the range of dates using Oracle Model clause. Let me explain you first, that what is actually a Model is.
What is Oracle MODEL Clause
MODEL: To calculate new values we can define a multidimensional array and apply rules on the array with this clause in Oracle SQL. Note that, the rules can interdependent and futuristic calculations.
The multidimensional array can be defined by using Oracle Model clause by mapping the query data columns into three groups.
- Partitioning. 2. Dimension. 3. Partitions.
| Partition: Logical blocks of the result set, similar to the partitions of the analytical functions defined by Partition. Model rules are applied to the cells of each partition. | |
| Dimensions: Each measure cell in a partition identify by Dimensions. These columns identify characteristics such as region, product name and dates. | |
| Measures: In a star schema Measures are correspondent to the measures of a fact table. They usually contain numeric values such as cost or units. By specifying its full combination of dimensions within it partition each cell is accessible. |
MODEL Rule.
To create rules on these multidimensional arrays, in terms of dimension rules we define flexible rules, and can use wild cards and FOR loops. By integrating analyses in database calculations built with the MODEL clause improve on traditional spreadsheet, improving symbolic referencing along with readability, much better manageability and better scalability.
In below example we are going to generate date range using Oracle MODEL, Dimension, Measures and Rules.
Let me explain you, what I did in this example Oracle query. First I define a MODEL clause in the Model I create date Dimension in between braces or rules I created a for loop which will generate the date range by defining its from and to range with its incremental value 1 with in square brackets, its syntax is like list comprehension in Python, those knows Python easily understand the term list comprehension. I named this rule as drule, then in measures I wrote that if drule return null then consider it as 0.
Example ORACLE Query.
Select
to_date(d,'DD-MM-YYYY') date_range
From dual
Model
Dimension by(Trunc(to_date('01012021', 'DDMMYYYY')) d)
measures(0 drule)
rules(
drule [
for d from trunc(to_date('01012021', 'DDMMYYYY')) to to_date('1501021', 'DDMMYYYY') increment 1
] = 0
)
Let us run this query and have it’ result.
| Query Result |
|---|
DATE_RANGE |
| 1 1/1/0021 2 1/2/0021 3 1/3/0021 4 1/4/0021 5 1/5/0021 6 1/6/0021 7 1/7/0021 8 1/8/0021 9 1/9/0021 10 1/10/0021 |
Hearing from you, will be my pleasure, If you have any comments or questions, please post below.
Happy analytics.
