OBIEE : Useful SQL statements in Business Intelligence

This is used to create an Oracle 10g Database table using another existing Oracle 10g Database table:

1. SQL> create table TIME_DIM_NEW AS (SELECT * from TIME_DIM WHERE MONTH='srikanth');

This is used to retrieve the count of all DISTINCT date rows(in the format dd/mm/rrrr) after stripping out the "time:secs" information from the CREATED_DATE field:

1. SQL>   SELECT count(DISTINCT to_date(CREATED_DATE, 'dd/mm/rrrr')) FROM CONTRACT_DASHBOARD;

This is used to get a range of values from the select statement using ROWNUM Oracle 10g Database:

1. SELECT  name, price FROM items WHERE ROWNUM > 5  and  ROWNUM <  20 ;

This is used to get the Current Date from the Oracle 10g Database:

1. select sysdate from dual;

Output: 

SYSDATE                                                                        
---------                                                                      
27-MAY-02                                  

This is used to get the Current User in the Oracle 10g Database:

1. select user from dual;

Output:

USER                                                                           
------------------------------                                                 
SCOTT

This is used to get all the rows that has NO words, numbers and underscores in the Oracle 10g Database:

SQL> SELECT cust_name FROM sales  WHERE REGEXP_LIKE (cust_name, '[^[:alnum:]_]');
OR
SQL> SELECT cust_name FROM sales  WHERE REGEXP_LIKE (cust_name, '[\W]');

This is used export and import tables from an Oracle 10g database instance to another Oracle 10g database instance:

This is used to export and import only few tables  in the schema ORCL.

1. exp USERID=srikuser/srikpassword@ORCL TABLES=(BUS_ORG_DIM, CONTRACT_DIM, CONTRACT_FACT, PRODUCT_DIM, REGION_DIM, TIME_DIM) FILE=obieetab.dmp
2. imp USERID=newuser/newpassword@ORCLDD TABLES=(BUS_ORG_DIM, CONTRACT_DIM, CONTRACT_FACT, PRODUCT_DIM, REGION_DIM, TIME_DIM) IGNORE=Y FILE=obieetab.dmp

This is used to export and import all the tables in the schema ORCL.

1. exp srikuser/srikpassword@ORCL FILE=mototabs.dmp
2. imp newuser/newpassword@ORCLDD  FILE=mototabs.dmp

This "SQL Copy" is used to copy tables from one Oracle Database 10g Schema to another Oracle Datbase 10g Schema:

1. SQL> create user srikuser identified by srikpwd default tablespace users;
2. SQL> grant connect,resource to srikuser;
3. copy FROM servstg/servpasswd@ORCL TO srikuser/srikpwd@ORCLNW CREATE BUS_ORG_DIM USING SELECT * FROM BUS_ORG_DIM
4. copy FROM servstg/servpasswd@ORCL TO srikuser/srikpwd@ORCLNW CREATE CONTRACT_DIM USING SELECT * FROM CONTRACT_DIM
5. copy FROM servstg/servpasswd4@ORCL TO srikuser/srikpwd@ORCLNW CREATE CONTRACT_FACT USING SELECT * FROM CONTRACT_FACT
6. copy FROM servstg/servpasswd@ORCL TO srikuser/srikpwd@ORCLNW CREATE PRODUCT_DIM USING SELECT * FROM PRODUCT_DIM
7. copy FROM servstg/servpasswd@ORCL TO srikuser/srikpwd@ORCLNW CREATE REGION_DIM USING SELECT * FROM REGION_DIM
8. copy FROM servstg/servpasswd@ORCL TO srikuser/srikpwd@ORCLNW CREATE TIME_DIM USING SELECT * FROM TIME_DIM