Saturday 16 June 2012

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

No comments:

Post a Comment