Math Functions

• ROUND(col/value,n)
• TRUNC(col/value,n)
• CEIL(col/value)
• FLOOR(col/value)
• POWER(col/value,n)
• EXP(n)
• SQRT(col/value)
• SIGN(col/value)
• ABS(col/value)
• MOD(value1,value2)
• LOG(m,n)
• SIN(n)
• SINH(n)
• TAN(n)
• TANH(n)
• COS(n)
• COSH(n)
  

String Functions

• LTRIM(col/value[, 'char(s)'])
• RTRIM(col/value[,'char(s)'])
• LENGTH(col/value)
• TRANSLATE(col/value,from,to)
• REPLACE(col/value,string,replacement_string)
  

String Functions

• LOWER(col/value)
• UPPER(col/value)
• INITCAP(col/value)
• CONCAT(col1/value1, col2/value2)
• LPAD(col/value,n[, 'string'])
• RPAD(col/value,n[,'string'])
• SUBSTR(col/value, pos[,n])
• INSTR(col/value,'string')
  
• INSTR(col/value, 'string',pos,n)

WHERE

SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE
WHERE SAL > 10000;

Operators:
=, >, >=, <, <=, BETWEEN ... AND, IN (List), LIKE, IS NULL, IS NOT NULL, !=, <>, NOT BETWEEN .. AND, NOT IN, NOT LIKE, AND, OR

LIKE wildcards: %, _
IN with numbers: IN (123, 456, 789, 678)
IN with string: IN ('ABC', 'DEF', 'GHI', 'CDE')

ORDER BY

Default: ASC

SELECT ACCOUNT_ID, ACCOUNT_NAME
FROM ACCOUNT
ORDER BY ACCOUNT_ID;

SELECT ACCOUNT_ID, ACCOUNT_NAME
FROM ACCOUNT
ORDER BY ACCOUNT_ID DESC;

NVL

Convert NULL value

SELECT EMPLOYEE_NAME, SALARY*12 + NVL(COMM, 0) ANNUAL_SALARY
FROM EMPLOYEE;

SELECT NVL(LastModifiedDate, '2009-01-01')
FROM ACCOUNT;

SELECT NVL(REVENUE, 0)
FROM ACCOUNT;

SELECT NVL(BILLING_ADDRESS, 'DBA')
FROM ACCOUNT;

SQL Server: ISNULL(BILLING_ADDRESS, 'DBA');

Concatenation Operator

Without literal
SELECT EMPNO || ENAME EMPLOYEE
FROM EMP;

With literal
SELECT EMPNO || '-' || ENAME EMPLOYEE
FROM EMP;

SQL Server: + operator
SELECT EMPNO + '-' + ENAME EMPLOYEE
FROM EMP;

SELECT

List department number, employee name, and manager number in EMP table:

SELECT DEPTNO, ENAME, MGR
FROM EMP;

With Alias
SELECT ENAME, SAL*12 ANNSAL, COMM
FROM EMP;

SELECT * FROM EMP;

Get project issues on EPM

This is the stored procedure I am using to get issues information on my EPM

CREATE PROCEDURE [dbo].[spLoadProjectIssues]
@ProjectName NVARCHAR(255) = NULL,
@FromDate DATETIME = NULL,
@ToDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT t1.Title, t1.AssignedToResource, t1.DueDate, t1.Status, t1.CreatedDate
FROM ProjectServer_Reporting..MSP_WssIssue t1
  INNER JOIN MSP_PROJECTS t2 ON t1.ProjectUID = t2.PROJ_UID
WHERE  t2.PROJ_NAME LIKE @ProjectName
  AND (t1.CreatedDate BETWEEN @FromDate AND @ToDate)
END

Get project name and team members in EPM

SELECT t1.PROJ_UID
, t1.PROJ_NAME
, t3.RES_NAME
, CASE WHEN t1.WRES_UID = t3.RES_UID THEN 1 ELSE 0 END AS IS_PM
FROM MSP_PROJECTS t1
INNER JOIN MSP_PROJECT_RESOURCES t2 ON t1.PROJ_UID = t2.PROJ_UID
INNER JOIN MSP_RESOURCES t3 ON t2.RES_UID = t3.RES_UID
ORDER BY t1.PROJ_NAME