Regardless of the logic purpose of the following codes, let go through each function that have been used within them
Round(Avg(To_Char(SysDate-7* RowNum,’ DD Month YYYY’)))
SYSDATE: Returns the current date-time from the server where the
database is located
TO_CHAR(date,format): Converts a date to a string in the specified format
ROWNUM: is an Oracle pseudo column which numbers the rows in a result set, which you can use directly within your SQL query. With SysDate-7* RowNum, you would get a list of dates that has an interval of a week time.
AVG(expression) : Returns the average of the values in a set of rows
ROUND(number, decimal places): Rounds a value to the specified number of decimal places
CEIL(number): number is the value used to find the smallest integer value. See the difference against Round();
DECODE: has the functionality of an IF-THEN-ELSE statement. The value returned by Ceil() will be checked against each following parameter. So, if it’s 1, the decode() function will return ‘1’. else if 2, return ‘2’. else if 3, return, if neither of the preceding ones it will all be ‘4’.
TRUNC(datetime): Removes the time component from a date-time value
ADD_MONTHS(date, number ofmonths): Adds the specified number of months to the date value (subtracts months if the number of months is negative)
Cascaded select query is very often used for query base on the records set which need to be collected by other queries. Like the following example, the select query needs to be based on the records sets a and b, which need to be collected by the two inner queries.
FROM a, b
One more thing need to be aware is the alias. It is recommended to provides alias for columns that need to be returned by the inner query, although they could be referred by their column name by default.
Joins could be specified either in the From clause or in the Where clause. The difference is the expression. (used by oracle 9i and after)
left out join b on a.placeId=b.placeId
The preceding code will be also expressed by the following code. (used by oracle8i and ealier, see http://www.kingtraining.com/confdownloads/downloads/Oracle9iJoin_paper.pdf)
Select * from a, b
Where a.placeId =(+)b.placeId
FIRST_VALUE, OVER and PARTION BY
,First_Value(CR.Published_Date) Over(Partition By DE.Display_Name Order By CRP.Purchase_Date Desc)
From CompanyReportPurchase CRP, CompanyReport CR, DirectoryEntry DE
AND DE.COMPANY_ID = CRP.COMPANY_ID
The highlighted code above is trying to fetch the CR.Published_Date value from first row (by First_value) of the records returned by the later part. The code inside the Over() function is running based on the records returned by the where clause. Inside the Over() function, the Partition By DE.Display_Name will subgroup the records by DE.Display_Name and order them by CRP.Purchase_Date.
The general syntax of this analytic function could be concluded as:
Function(arg1,…, argn) OVER ( [PARTITION BY <…>] [ORDER BY <….>] [<window_clause>] )