Saturday, January 19, 2013

[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