Tuesday, May 24, 2011

SQL Functions: Calculate difference between columns of current and previous/next row

Hi friends,

How to calculate difference between columns of current and previous/next row?

Let's say, you want to monitor how often products are sold from your store.

i.e. 

You want difference between current and previous SELL_DATE then you can write query as follows:

SELECT TO_CHAR(SELL_DATE, 'DD-MON-YYYY HH24:MI:SS'),
       TO_CHAR(LAG(SELL_DATE) OVER (ORDER BY SELL_DATE), 
       'DD-MON-YYYY HH24:MI:SS') AS SELL_DATE_PREV,
       (SELL_DATE - LAG(SELL_DATE) OVER (ORDER BY SELL_DATE))
       * 24 * 60 * 60 AS DIFF_IN_SECONDS
FROM PRODUCT_SALES
ORDER BY SELL_DATE DESC;

More Info: Oracle - Base : Lag Lead Analytic Functions

Regards,


Sohil Bhavsar

Monday, May 16, 2011

Oracle Application Express 4.0 with ExtJS

Oracle Application Express 4.0 with ExtJS

Oracle Application Express 4.0 with ExtJS is quite interesting book. I feel it will be very helpful to the APEX community.

Because, there are very limited resources available for integration of ExtJS and Oracle APEX and the way the book is written it is very easy to understand the concepts of ExtJS.

APEX team has already integrated jQuery in Oracle APEX 4.0 onwards, so before reading the book I had a mindset that jQuery is better then ExtJS but now I feel for Rich User Experience ExtJs is quite good option and APEX Developmt Team should focus towards that.

Congratulations Mark Lancaster for publishing this book.