Partition Over in Oracle

Quick data retrieval is what we expect, Oracle in 10g has provided some new keywords that can be used to reduce the time when group by is used.Below are the queries which are self explanatory.

The query is used to find employees whose salaries are greater than average salary of the department.

Normal Query:

select e.empid,e.salary,e1.avgsal from emp e join (select deptid,avg(salary) avgsal from emp group by deptid)e1 on e.deptid=e1.deptid where e.salary>e1.avgsal

New Query:

select empid,salary,deptid,avg(salary) over (partition by deptid) avgsal from emp e

 

Egg: Veg or Non-Veg

There is always confusion regarding the status of the egg. As a vegetarian i was in a confusion whether to eat or not. I won’t eat egg directly but consume indirectly in the form of cakes and other pastries. I came across this answer from my uncle and he disclosed that it is considered as non-veg as it considered as output of hen’s menstrual cycle. Hope so people accept this.

Visit to BR hills, Bandipur,Mudumalai and Himavad Gopalswamy Betta

After a long wait a weekend outing was planned with family in the disguise of visit to temples. It looked picture perfect that it will one of the most memorable trips, but it was full of surprises,turns and twists to become a really unforgettable trip.It started with a visit to Bannari temple and there was surprise. The ghat road was closed for traffic as a lorry laden with Sugarcane capsized in a hair pin bend due to heavy load. I got plan B ready but to my surprise they opened traffic for light vehicles. The journey through Dimbam ghat road is always enjoyable with the road taking you to 1100 meters above mean sea level.

With a good road it took an hour to reach Chamrajnagar and 20 minutes to Yelandur and a detour to BR hills.The sanctuary was not green due to its dry and deciduous nature.The drive lasted for another 20 minutes through a thick forest leading to the summit and i was surprised as there was settlement on the summit.

The Bilgiri Rangaswamy temple was very old and the view from the top was breathtaking one.After a brief visit to the temple with full devotion my journey started to reach Bandipur through Gundulpet.

The road condition was very bad from Chamrajnagar to Gundulpet but the road was very straight.Then on reaching Bandipur the wildlife safari was too expensive but the funny thing was their suggestion to visit neighbouring  Mudumalai in Tamilnadu. On reaching Mudumalai,touts created nuisance yet we managed to get tickets for wildlife Safari.

The entry into Van greeted me “Sighting animals is purely luck”, i thought what would be my luck.I was able to spot few elephants, gaur, nilgiri languar and couple of peococks with my Dad passing comments “You can spot everything in Sathyamangalam and the visit is not great”.I was little bit shaken by his comments.

Then we took a small detour to visit Himavad (Ice in Kannada)Gopalswamy temple at Gopalswamy betta and the view was breathtaking along with the temple at the top. The temple had its own piece of history with icy garbagraha and the architecture was still unknown.

The return journey to Sathyamangalam had its own momemts with traffic blockage at the same Thimbam Ghats but this time at different hair pin bend due to overload delaying my return to home by more than an hour.It again gave me anxious momemts as i was in doubt whether i will catch train back to Chennai for work………

Informatica-Variable Ports

Variable ports can be used in places where you need to retrieve previous rows data. But the order in which we create variable ports is very important. Generally the order is

1.Input Ports

2.Variable Ports

3.Output Ports.

But the order can change  according to the output we need.

Here comes a small example about the usage of the variable port.

We need Students in Dept  to be generated in Informatica and we can use variable port to generate this numbers

The below screenshot shows the mapping

The properties of the Expression transformation are showed in the screenshot below.