NANVL is used to return an alternate value for a BINARY_FLOAT or BINARY_NUMBER that has a Nan (Not a Number) value. The number to check is the first argument, and the second argument is the replacement value if the 1st
Treat – Oracle SQL Function
TREAT allows you to change the declared type of the expr argument. This function comes in handy when you have a subtype that is more specific to your data and you want to convert the parent type to the more
Sysdate – Oracle SQL Function
SYSDATE returns a DATE that represents the date and time set on the operating system of the machine Oracle is installed on. The format of SYSDATE is controlled by the NLS_DATE_FORMAT session parameter. Example: SELECT SYSDATE FROM DUAL; SYSDATE ——————-
Power – Oracle SQL Function
POWER returns the first argument raised to the power of the second. The arguments can be a numeric value or any type that can be implicitly converted to a number. Using POWER is a good way of turning a LOG
Trim – Oracle SQL Function
TRIM returns a VARCHAR2 string with either the leading, trailing, or both the leading and trailing characters char trimmed from source. TRIM([LEADING] [TRAILING] [BOTH] char FROM SOURCE) IF you specify TRAILING then the trailing characters that match char will be
Systimestamp – Oracle SQL Function
SYSTIMESTAMP returns a TIMESTAMP WITH TIME ZONE result from the underlying operating system date, timestamp, fractional seconds and time zone. Example: SELECT SYSTIMESTAMP FROM DUAL; SYSTIMESTAMP ————————————- 13-SEP-05 10.40.32.818000 PM -05:00
Covar_samp – Oracle SQL Function
COVAR_SAMP returns the sample covariance of a pair of numbers. Syntax: COVR_SAMP(expression1, expression2) Example: SELECT id, COVAR_SAMP(list_price,min_price) as RESULT FROM product_information GROUP BY id;
Cume_dist – Oracle SQL Function
CUME_DIST returns the relative position of a row within a group meeting certain criteria. You can specify one or more expressions to pass as arguments to the function. Syntax: CUME_DIST(expression1,… WITHIN GROUP (ORDER BY) Example: SELECT CUME_DIST(5000,103) WITHIN GROUP (ORDER
Dense_rank – Oracle SQL Function
DENSE_RANK returns a NUMBER representing the rank of a row within a group of rows. Syntax: DENSE_RANK(expression1,…) WITHIN GROUP (ORDER BY) Example: SELECT DENSE_RANK(5000,103) WITHIN GROUP (ORDER BY SALARY, MGR_ID) as RESULT FROM EMP; RESULT ———— 43
Group_id – Oracle SQL Function
GROUP_ID assigns a number to each group defined in a GROUP BY clause, GROUP_ID can be used to easily see duplicated groups in query results. Example: select avg(salary), mgr_id, group_id() gid from EMP group by mgr_id; AVG(SALARY) MGR_ID GID ———————-