Notes for Oracle SQL Usage

 

General Functions

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

 

Decode(Ceil((To_Char(SysDate,’YYYY’)-:paramDate)/5),1,’1′,2,’2′,3,’3′,’4′)

 

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(Add_Months(SysDate,+1*(RowNum-1)

 

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

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.

 

WITH

a AS(

            SELECT …

)

,b As(

            SELECT …

)

SELECT *

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.

 

Outer Joins

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)

Select *

from a,

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

        Select Distinct

        DE.Display_Name Company

        ,CRP.PURCHASE_ID ID

        ,First_Value(CR.Published_Date) Over(Partition By DE.Display_Name Order By CRP.Purchase_Date Desc)

        CR_Published_Date

        From CompanyReportPurchase CRP, CompanyReport CR, DirectoryEntry DE

        Where CR.Report_ID=CRP.Report_ID

        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>] )

 

Reference:

http://www.psoug.org/reference/date_func.html

http://vpf-web.harvard.edu/applications/ad_hoc/key_functions_in_oracle_sql.pdf

 

 

Advertisements
This entry was posted in Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s