Himu\’s Attempt at Blogging

Tidbits from my thoughts

Archive for the ‘Oracle’ Category

SQL – Dynamic Date Range, Number Range in Oracle, SQL Server, and DB2/400

with 9 comments

Often, we need to make coverage reports that span over a period of time and where all points in the period do not contain data.

Assume a shop has 50 outlets and we want to find the number of sales in each branch over the past ten days. Business is bad in some areas and so some outlets had no sale at all. A query like the following will give only those outlets that had sales but not a list of all outlets:

SELECT outletid, saledate, count(*)
FROM sales
GROUP BY outletid, saledate
ORDER BY outletid, saledate

Read the rest of this entry »

Written by mhimu

May 7, 2009 at 11:03 am

ODI – Incremental Update and Surrogate Key using Database Sequence

with 16 comments

I am writing this because it had me lose my sanity for some time. If,

  • You’re using Oracle Data Integrator
  • You’ve defined a dimension table with a surrogate key even if it is not a SCD type 2 (recommended by Kimball)
  • The surrogate key is maintained through a database sequence (Oracle sequence in my case)
  • Your IKM in Incremental Update
  • You’re frustrated about how to control updates to a dimension having a sequenced surrogate key

Then read on for the solution that worked for me. The solution is pretty standard – you only need to know what you’re doing and setting the right options.

Read the rest of this entry »

Written by mhimu

May 4, 2009 at 4:35 pm

Using Oracle 10g Data Pump Export

with 4 comments

I recently installed Oracle 10g Release 2 (10.2.0.1) on a Dell PowerEdge 2800 (Dual Xeon 2.8 GHz, 2GB RAM and 2 x 73GB SCSI HDD) running Red Hat Enterprise Linux 4 for self-tutoring administration.For better understanding, I didn’t create a database during installation (instance-only setup). The database was created separately following instructions from the Apress book Expert Oracle 10g Administration (ISBN 1-59059-451-7).

OS Environment variables:

$ORACLE_BASE=/u01/app/oracle
$ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
$ORACLE_OWNER=oracle
$ORACLE_SID=nwndb

initnwndb.ora directory entries:
Read the rest of this entry »

Written by mhimu

August 11, 2007 at 1:18 pm

Posted in Oracle