Using Partition Outer Join

Most of the time , the analyst would need a report to display the activity for a month on a daily basis.

For me , I have scripted the data import job from external system and I would like to see the recordcount on a daily basis for the past one week.

Let us see an example . Let us create a table and populate data into the table.

And here is the table structure and the data .

SQL> desc datalog
Name Null? Type
----------------------------------------- -------- ----------------------------
LOADDATE NOT NULL DATE
TABLENAME VARCHAR2(30)
ROWCOUNT NUMBER


SQL> select * from datalog;


LOADDATE TABLENAME ROWCOUNT
--------- ------------------------------ ----------
29-MAY-10 ORDER 5012577
29-MAY-10 PAYMENT 165851
30-MAY-10 ORDER 5012577
30-MAY-10 PAYMENT 5073
31-MAY-10 ORDER 5012577
31-MAY-10 PAYMENT 5073
01-JUN-10 ORDER 5003
01-JUN-10 PAYMENT 5073
02-JUN-10 ORDER 5003
02-JUN-10 PAYMENT 5073
04-JUN-10 ORDER 5003
04-JUN-10 PAYMENT 5073

12 rows selected.

Here is the SQL to display the report using Partition Outer Join to show the date , though it may not have the data ( aka rowcount for that date , 3rd June , in our example) . Also , I would like to transpose it using PIVOT so that we can have a better view ( like a cross tab report )
Here is the SQL and its output.

SQL> SELECT tablename ,
2 DAY1,
3 DAY2,
4 DAY3,
5 DAY4,
6 DAY5,
7 DAY6,
8 DAY7
9 FROM
10 (SELECT Load.Tablename ,
11 NVL(Load.RowCount, 0 ) RowCount ,
12 row_number() over ( partition BY Tablename order by listdatetime DESC ) rn
13 FROM
14 (SELECT loaddate ,
15 Tablename ,
16 Rowcount
17 FROM DataLog
18 WHERE LoadDate >= Sysdate - 7
19 ) Load PARTITION BY (Tablename)
20 RIGHT OUTER JOIN
21 (SELECT to_date(sysdate - level + 1 )listdatetime
22 FROM Dual
23 CONNECT BY Level <= 7
24 ) Tim
25 ON ( TRUNC(LoadDate ) = TRUNC(Listdatetime) )
26 ) Pivot ( MAX(Rowcount) FOR Rn IN ( 1 AS Day1 , 2 AS Day2 , 3 AS Day3 , 4 AS Day4 , 5 AS Day5 , 6 AS Day6 , 7 AS Day7 ) )
27 ORDER BY tablename
28 ;

TABLENAME DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
ORDER 5003 0 5003 5003 5012577 5012577 5012577
PAYMENT 5073 0 5073 5073 5073 5073 165851

Good luck.

Multiple Table Insert

The multi table insert is one of the great features that will  benefit  the developers in ETL like processing . Basically , you would select the data from the datastore and insert into the multiple tables.

 Let us look at an example.

SQL> select count(*) from all_objects;

COUNT(*)
----------
69495

SQL> Create table t1 as Select object_id , object_name from all_objects Where 1 = 2;
Table created.

SQL> Create table t2 as Select object_id , object_name from all_objects Where 1 = 2;
Table created.

SQL> Insert

2 All
3 When (Object_Type) <> 'TABLE' Then
4 Into T1
5 (Object_Id , Object_Name )
6 Values
7 (500 , Object_Name )
8 When (Object_Type) = 'TABLE' Then
9 Into T2
10 (Object_Id , Object_Name )
11 Values
12 (600 , Object_Name )
13 Select Object_Name , Object_Type
14 From All_Objects;

69497 rows created.

SQL> select count(*) from t1;

COUNT(*)
----------
66604

SQL> select count(*) from t2;

COUNT(*)
----------
2893

 



As you can see , we loaded the data from all_objects to different tables t1 and t2. 

In my tenure with Barnes & Noble , I have used this feature ( and the error logging feature , which I will cover in the next few posts )  heavily , there by reducing the data load time from hours to minutes.