Monday, January 19, 2009

PL/SQL Reserved Words

ALL ARRAY AUTHID ALTER AS AVG AND ASC ANY AT BEGIN BOOLEAN BETWEEN BULK BINARY_INTEGER BY BODY CASE CLOSE COMMENT CONSTANT CURSOR CHAR CLUSTER COMMIT CREATE CHAR_BASE COALESCE COMPRESS CURRENT CHECK COLLECT CONNECT CURRVAL DATE DEFAULT DO DAY DELETE DROP DECLARE DESC DECIMAL DISTINCT ELSE EXCLUSIVE EXTENDS ELSIF EXECUTE EXTRACT END EXISTS EXCEPTION EXIT FALSE FORALL FETCH FROM FLOAT FUNCTION FOR GOTO GROUP HAVING HEAP HOUR IF INDICATOR NTERSECT ISOLATION IMMEDIATE INSERT INTERVAL IN INTEGER INTO INDEX INTERFACE IS JAVA LEVEL LONG LIKE LOOP LIMITED LOCK MAX MLSLABEL MIN MOD MINUS MODE MINUTE MONTH NATURAL NOCOPY NUMBER NATURALN NOT NUMBER_BASE NEW NOWAIT NEXTVAL NULL OCIROWID OPEN ORDER OF OPERATOR ORGANIZATION ON OPTION OTHERS OPAQUE OR OUT PACKAGE POSITIVE PRIVATE PARTITION POSITIVEN PROCEDURE PCTFREE PRAGMA PUBLIC PLS_INTEGER PRIOR RAISE RECORD REVERSE ROWNUM RANGE REF ROLLBACK ROWTYPE RAW RELEASE ROW REAL RETURN ROWID SAVEPOINT SET SQL STDDEV SYNONYM SECOND SHARE SQLCODE SUBTYPE SYSDATE SELECT SMALLINT SQLERRM SUCCESSFUL SEPERATE SPACE START SUM TABLE TIMEZONE_REGION TO THEN TIMEZONE_ABBR TRIGGER TIME TIMEZONE_MINUTE TRUE TIMESTAMP TIMEZONE_HOUR TYPE UID USE UNION USER UNIQUE UPDATE VALIDATE VARIANCE VALUES VIEW VARCHAR VARCHAR2 WHEN WITH WHENEVER WORK WHERE WRITE WHILE YEAR  ZONE

Tuesday, January 6, 2009

How to copy table in Oracle and SQL Server

Assume that you have a table tblCustomers, you want to copy this table (schema and data) to test data on it.
You can use this script:

CREATE TABLE tblCustomersCopy AS SELECT * FROM tblCustomers
Note that if you use this script in SQL Server, you can meet an error like that: Incorrect syntax near the keyword 'AS'.
To copy table in SQL Server, you can use this script:
SELECT * INTO tblCustomersCopy FROM tblCustomers

Test your new table:
Oracle:
SELECT * FROM tblCustomerCopy WHERE RowNum <>
SQL Server
SELECT TOP 10 * FROM tblCustomerCopy

DROP TABLE tblCustomersCopy

Monday, January 5, 2009

Select all tasks based on each project in EPM


SELECT t2.PROJ_NAME,
t1.TASK_NAME,
t1.TASK_EARLY_FINISH,
t1.TASK_LATE_START,
t1.TASK_STOP_DATE,
t1.TASK_RESUME_DATE,
t1.TASK_OUTLINE_LEVEL,
t1.TASK_DUR,
t1.TASK_DUR_FMT,
t1.TASK_ACT_DUR,
t1.TASK_REM_DUR,
t1.TASK_START_DATE,
t1.TASK_FINISH_DATE,
t1.TASK_ACT_START,
t1.TASK_ACT_FINISH,
t1.TASK_CONSTRAINT_DATE,
t1.TASK_EARLY_START,
t1.TASK_LATE_FINISH,
t1.TASK_WORK,
t1.TASK_ACT_WORK,
t1.TASK_REM_WORK,
t1.TASK_COST,
t1.TASK_WBS,
t1.TASK_SUMMARY_PROGRESS_DATE,
t1.CREATED_DATE,
t1.MOD_DATE
FROM dbo.MSP_TASKS t1
INNER JOIN dbo.MSP_PROJECTS t2 ON t1.PROJ_UID = t2.PROJ_UID

Select all tasks in EPM


SELECT TASK_NAME,
TASK_EARLY_FINISH,
TASK_LATE_START,
TASK_STOP_DATE,
TASK_RESUME_DATE,
TASK_OUTLINE_LEVEL,
TASK_DUR,
TASK_DUR_FMT,
TASK_ACT_DUR,
TASK_REM_DUR,
TASK_START_DATE,
TASK_FINISH_DATE,
TASK_ACT_START,
TASK_ACT_FINISH,
TASK_CONSTRAINT_DATE,
TASK_EARLY_START,
TASK_LATE_FINISH,
TASK_WORK,
TASK_ACT_WORK,
TASK_REM_WORK,
TASK_COST,
TASK_WBS,
TASK_SUMMARY_PROGRESS_DATE,
CREATED_DATE,
MOD_DATE
FROM dbo.MSP_TASKS

Select all projects in EPM


SELECT PROJ_NAME
PROJ_PROP_AUTHOR,
PROJ_PROP_COMPANY,
PROJ_INFO_CURRENT_DATE,
PROJ_OPT_DEF_FINISH_TIME,
PROJ_OPT_DEF_START_TIME,
PROJ_INFO_FINISH_DATE,
PROJ_LAST_SAVED,
PROJ_CREATION_DATE,
PROJ_INFO_START_DATE,
PROJ_INFO_STATUS_DATE,
PROJ_PROP_TITLE,
PROJ_CHECKOUTDATE,
WPROJ_DESCRIPTION,
WPROJ_LAST_PUB,
WPROJ_STS_SUBWEB_NAME,
PROJ_SESSION_DESCRIPTION,
PROJ_PROP_MANAGER
FROM dbo.MSP_PROJECTS

Oracle Query