Wednesday, November 24, 2010

Shift the condition to fields

The report needs to sum() fields in opposite condition. Example: sum(salary) where employee id > 500 and sum(salary) where employee id <=500. Below is how the example report looked like.

Department, Total salary where id > 500; Total salary where id <= 500
Account, 10000, 20000
IT, 20000, 10000

Usually, we would go for sub-query to solve the above problem such as (or putting the sub-query in the FROM clause and use a join):

SELECT emp.dept, (SELECT SUM(emp1.salary) FROM emp1 WHERE emp1.id > 500 AND emp1.dept = emp.dept), (SELECT SUM(emp2.salary) FROM emp2 WHERE emp2.id >= 500 AND emp2.dept = emp.dept)
FROM emp GROUP BY dept

Now, there is another way to generate the same result without using sub-query by using decode (matching character condition) or case (true or false condition)

SELECT emp.dept, SUM(CASE WHEN emp.id > 500 THEN emp.salary ELSE 0 END), SUM(CASE WHEN emp.id<=500 THEN emp.salary ELSE 0 END)
FROM emp GROUP BY dept

So, the sum will only do its work when the case condition is true and thus, eliminate the need sub-query. The performance are better using this way and reduce the cost for additional table access.

Off topic: Writing using cellphone and bluetooth

I just found out that I can blog by using my crappy cellphone with bluetooth. All I need just write the stuff in Notes app in cellphone and push the text to laptop using bluetooth. It worked marvelously and surely will help me to keep up the posting.

I was thinking of getting a PMP  (portable media player)  (so it comes with appropriate keyboard instead of my 12 keypad to do all my typing) to do my blogging and capture ideas wherever I am. So far the list I found are Archos 43, Ipod Touch, Creative Zen Touch 2.

Tech stuff changes all the time. I will see how far I can get with 12 keypad typing until I got fed up. :P

SQL: Identify day of the week

Select to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE = AMERICAN') from dual

Due to different server in different region has different setting for first day of the week, this can become tricky when we try to identify, lets say Wed, using numerical format. By nailing down the date languaga as American and format as DY, this way we can sure the result returned WED is surely Wednesday no matter where the server or what the setting is.

Monday, November 22, 2010

Off topic: Mencoder subtitle not working in Ubuntu

I was doing some video converting for my tiny little mp4 player and some of the subtitle worked, some do not. This got me curious because on the the video I'm looking forward to convert has a subtitle file that does not work in mencoder. I knew it was the character encoding on the file but I'm just too lazy to use some application just to convert the file although all are in English. Fortunately, I found a trick to get rid of the character encoding app by using the step below.

1. Create an empty file in the same folder of the subtitle.
2. Edit the "problematic" subtitle using gedit. Select all and copy.
3. Edit the empty file using gedit and paste the subtitle content.
4. Re-start the conversion again.

It worked.

I guess the same trick also work for other character conversion purposes minus the hassle to get a conversion app (e.g. iconv)

P.S. Subtitle overlapping at blank area? Try using -subpos to position the subtitle in the picture frame. This will get rid of the overlapping issue. It doesn't looked nice but at least the subtitle can be read.