SQL: Turn multiple rows into one row multiple columns

We have a table looks like this:
Name Question Value
Alan        1           5
Alan        2           4
Alan        3           6
Jim          1           4
Jim          2           3
Jim          4           5

We would like to select out a result looks like this:
Name    Q1     Q2    Q3    Q4
Alan       5        4       6      null
Jim          4       3       null   5

This problem seems to be very easy, however, cost me a lot of time to figure out how to proceed. Finally, after some googling a nice post introduce a nice way to handle similar issues using MAX(DECODE()) function.

Basically, you first need to utilise the decode() function. decode() acts like if-else-then. For example, decode(name, 'Alan', 'True', 'False') works like if the name is 'Alan' then return 'True', otherwise return 'False'.

In our problem here, we use decode() to distinguish the answers from different questions: decode(question, 1, value, NULL) as Q1,   decode(question, 2, value, NULL) as Q2, ... In this way we would pick out the value of each question.

After we can get the answer to each question, what we need to do is group the answers by different names. That's why we need max() here to act for the group by clause. Since there is only one value here for each question, so max(), min() or some other reasonable functions are all working well here.:

select name AS NAME, max(decode(question, 1, value, NULL)) AS Q1, max(decode(question, 2, value, NULL)) AS Q2, max(decode(question, 3, value, NULL)) AS Q3, max(decode(question, 4, value, NULL)) AS Q4 from table group by name.

Cannot restart LDAP server on Ubuntu

I did some changes to the ldif file yesterday, and cannot start my LDAP server any more today. I tried different ways, but all unfortunately failed.

Actually the way to workaround is quite easy, that you simply remove the folder slapd.d/ under /etc/ldap, and reinstall the ldap again, ldap will work again.

Intellij IDEA 10.5 stuck in the loading process

Intellij IDEA is a really smart IDA that attracts more and more developers to its community. However, a lot of bugs are still hanging there which annoy people too much.

One of the bugs is when you start IDEA with a pretty large project, there is a highly chance that your loading process will stuck there forever. This has been identified as a bug in IDEA-67401, and hasn't been resolved yet now.

According to that issue page, there is a workaround to load the project. That is to disable the 'Tip of the Day' and 'Productivity Guide' prompt when start a new project or open IDEA.

Besides, there is another possibility that you cannot even get in to adjust your setting before it get stuck. If that is the case, just remember to be very quick when you open IDEA, as soon as the loading project prompt pop up, cancel the loading, as well as all the next loading processes. Once the loading started, you will not be able to cancel it again, and have to kill the IDE and restart again.

Oracle: To convert a row into a column (CROSS-JOIN)

This afternoon, while I am doing some Jasper report stuff, I am puzzled by a SQL query that which takes quite a long time to execute. As the time goes, Jasper will easily get a timeout and a blank screen is waiting for me after retrieving data for about 120 seconds.

As it is not appropriate to use the original data table as the example to describe here, I tried to make up a similar scenario which should explain the cause and the solution sufficiently.

Given a table A, with five columns -- Primary Key (pk), Statistics 1A (s1a), Statistics 1B (s1b), Statistics 2A(s2a), Statistics 2B(s2b), we need to select the data out into a form that looks like -- Primary Key (pk), Statistics A(s1a or s2a), Statistics B(s2b or s2b). Essentially, it is trying to convert a row into one column but two rows.  Someone may prefer to call it pivot query by the way.

Initially, naively I think the most easiest and intuitive way to do it is by two union queries. That is
select pk, s1a, s1b from table where *** union select pk, s2a, s2b from table where ***.
When the data set is small, and it only depends on the single table, it is fine. However, in my case, the data set is huge, and the union will not be two but eight, and more importantly every union section will consists of another 6 inner join tables. That's the reason why Jasper cannot retrieve what it needs on time.

In this link, as suggested by Scott, introduced a very smart way to handle this issue. What it uses is called: cross join. So basically, we use the existing table to cross join with the different type/criteria to return the expected result.

select
   pk,
   case
      when ite = 's1' then s1a
      when ite = 's2' then s2a
   end as sa
   case
      when ite = 's1' then s1b
      when ite = 's2' then s2b
   end as sb
 from
   (
       select pivoter.ite,
           s1a, s1b, s2a, s2b
       from
           table
           cross join (
               select 's1' as ite from dual
               union all
               select 's2' as ite from dual
           ) pivotel
   )

By using the above code, the problem will be solved by the cross join union columns.

Miscellaneous Points for Oracle Join Query

As a software developer, you must have the experience to deal with all sorts of join queries. And I believe everyone has once or still now struggle with all these different terms about inner, outer, left, right, and etc. As I have spent roughly two hours these morning, to summarize some of the points that I am easily to forgotten and got wrong, it's a good chance to write it done in case later I need to test my memory.

1) What is the difference of join...on... grammar and little (+) sign?

Oracle used to only support (+) in old days, which is also kind of created by Oracle. Later, as the ANSI formalized the standard for join grammar, Oracle adopted both for join queries. As a result, it won't be a surprise if you see both two styles in your project which comes from different developers.

2) Any difference between inner join and join?

No.

3) Why do we need outer join?

Generally, inner join will return result sets that have a match in both Table A and B. However in reality sometimes, we also need results to be returned even the matching is null. That's the place where outer join shows its ability. There are plenty of tutorials available to discuss about the outer join. What I want to mentioning here is the null value will only applied to the join's destination table, but not the starting table.

4) Any more differences?

Yes. Actually there are two more I want to emphasize:

1. ANSI style supports outer join, which you can google what does it mean. But traditional Oracle doesn't directly support it. ( By saying it directly, I mean there always exists workarounds, and various workarounds)
2. One of the most important feature to differentiate the two is the ANSI style separate the join condition and query filter criteria, which is much more nice tidy and clean. (Is it, at least I think so). Meanwhile, you will also be able to avoid a lot of caveats that may come with you when you go for the traditional style. See Common errors seen when using OUTER-JOIN.

Australian Permanent Residency and Citizenship

Last Friday, after "illegally" struggling in Australia for more than one year, I was granted permanent residency visa. With a lot of congratulations coming from different people, a common question they ask is 'what is Australian Permanent Residency'? This question even comes from many Aussies which I thought the answer should be crystal clear for them. Now I realized the one who knows it well can only be those people who had/have experience to deal with the evil DIAC ( Department of Immigration and Citizenship).

Anyway, in this post, I will shortly introduce what is Australian Permanent Residency and how does it work for migrating people like me.

As everyone knows, generally there are only a few countries in the world publicly welcome immigration, Australia, Canada, New Zealand to name a few. Most of these countries share the same features like highly developed but low population density. In order to keep these contries' development, more on economical perspective, they need immigrates to fill the holes for skills shortage. The occupations varied from country to country depending on what kind of main industry the country runs. Let's go back to Australia again, the immigration policy has changed dramatically in the last ten years. Start from 2000, as the strong need from Australia government, it was extremely easy for an overseas student to get a permanent residency after studying in Australia for a tertiary degree. Under that policy, many many students came to Australia and settled since then. Meanwhile, they acted as an advertisement or agent, and attracted more crowds keeping coming. Consequently, a lot of changes of the migration policy had been put on. From 2007, a two-year minimum study period and IETLS 4*7 was started to be effective. Though pretty hard, comparing to the huge base numbers of students, still too many are eligible to stay in Australia after graduation. Started from 2008, more and more changes put on migration law made most of the graduates lost the opportunity to apply for PR, and had to return to their own country after spending thousands of thousands dollars here.

Currently, migration law is still under discussion, and another dramatic overhaul is being in progress and is due to release on Jul 1, 2011. Australia is no longer a country that can be easily migrated.

So, after you got PR, what benefits you can get out of it?

• You are eligible to staying in Australia indefinitely
• You can work, study, or nearly do whatever you want in Australia
• You are free to leave Australia and back to Australia as many times as you want
• You have the right to apply Medicare
• You can also apply for centrelink after two years
• You can freely go to New Zealand

Comparing the citizenship, the only a few disadvantages are

• You have to renew your PR visa every five years, and within this period time, you have to stay at least 2 years to show you are genuinely would like to be a resident in Australia
• You have no political right in Australia, and cannot vote (which I guess most people would not care)
• You cannot apply for Australian passport, and if you have to use your original passport to apply any third-country visa (Except New Zealand) 

I would say, the most benefit to apply for a citizenship is you get an Australian passport which allows you to enter most of the countries in the world without worring about visa. However, if your original country doesn't allow dual citizenship (e.g. China), then the moment you join Australia, you lost your original country's citizenship. This may be a big problem, if you wish to go back to your original country after 20 years or would like to work there, and will also be a problem for your property or deposits back in your own country. That's why, there are many people working in Australia holding a PR visa not applying for the citizenship.

Get rid of JTA warning message for WebLogic

This is the second time I ran into the JTA exceptions. Basically, you will get this message every one minute when running your EJB on any version of WebLogic server:

<Warning> <JTA> <BEA-110486> <Transaction BEA1-05576B5644FBD4F5B49F cannot complete commit processing because resource [weblogic.jdbc.wrapper.JTSXAResourceImpl] is unavailable. The transaction will be abandoned after 67,910 seconds unless all resources acknowledge the commit decision.>

Last I did google it, get rid of it, and eventually forget about it at all. Now it's a good chance for me to pick it up and record it down. Hopefully next time I can handle it in no time without spending half an hour wondering around. I tried different ways like restart server, uninstall/reinstall application, etc. No luck at all.

This warning message is because something is wrong in your code, specifically of transaction. There must be some of the transaction still keep opening after some exception happens or similar alike. As a result, the server complain that there is a transaction still running which no one seems to be using it at all.

In order to get rid of this ugly message, just go to your domain: <your-domain>/servers/AdminServer/data/store/default, there should be a file like "_WLS_ADMINSERVER000000.DAT" there. The number will random. If you delete this file, and restart the server, problem solved!

Firefox 4 Two Days

I was aware of the release of Firefox since Day 1, and kept trying to use numerous beta or trial version or even the minefield (sounds scary as the name or the real experience?) before the official release. However every time I just switched back to my lovely and sleek Chrome. Don't blame my patience, it just never worked out.

But this time, I have to say, the FF4 release is a huge success in terms of every aspect I can consider so far. The speed improvement is the topic that people keeping discussing all these two days. It's dramatically speed up everything even ten tabs are opening at the same time. The memory is another point I reckon, which used to consume nearly 700-800 MB on my poor machine, now can stably up and down around 300-400 MB. Actually this is one of the killing point for my working machine since this old PC always needs to Intellij Idea, WebLogic, SQLDeveloper and all sorts of dev tools all at once, you know how hard time I am having.

Another thing I like FF better than Chrome is it can temporarily record down the input you have put in before. Next time, you don't need to repeat those lengthy characters but just double click the text field. For me it's really helpful since I have do those boring task everyday to make sure programs never being screwed up.

Because it's only Day 2, I don't mind the add-ons are not ready for use. But please, guys, at least make some of the most common tools available ASAP. I have spent nearly half an hour this morning trying to find a good add-on for twitter. You will know the result if you do the same search now, not two days later, I reckon things may change less than 48 hours because weekend is coming :)

Anyway, now I have both Chrome and FF running at the same time finally (which also thanks for the latest upgrade of the memory), each sitting in a monitor. Before I make any final decision to stick on which browser, I will hang over, or later I may get used to the dual browser environment. Should I?

P.S. It would be nice if we can have a cross-browser bookmark application. Any recommendations?

Java Multithreading

I know this is one of the most basic topic that even second year university should be familiar with, however, this afternoon I have spent nearly a whole afternoon to debug some issues with Java Multi-threading, and only at that point I realized I am far far away from fully understanding the seemingly easy problem.

As I have reading through all kinds of tutorials, discussions available, I believe the best way would be to sommarize what I have found and record it as my own writing.

So here is the beginning, Java always running with a single thread if there is no request for a new thread to be spawned. However, in some circumstances, a separate thread is needed to perform some other tasks either to be parallel with the main thread or as a background job. There are two ways to achieve multi-thread in Java, as everyone should be familiar with: implements Runnable and extends Thread.

Basically, most of the time, implements Runnable should be the preference over extends Thread, unless you need to specifically override the life cycle method of the thread (which is rare). These two ways do nearly the same thing except when you subclass Thread, you are trapped in the spot that no other class can inherit as Java only allows single inheritance. While if you implements the interface, you still have the ability to extend any class you wish.

Next, let's go into the main method in the body -- run(). Actually, you have the choice to call run() and start() to execute the body. However, there is some slight differences between these two methods. run() will execute the method immediately in the current thread, and start() will spawn a new thread and the execution will be invoked undeterministicallly. As a rule of thumb, we should avoid to use run() but use start() all the time. The reason we have a multi-thread class is to run it in a separate thread. If we just call the run(), everything will run sequentially as there is no thread existed. run() should always invoked by JVM not the application.

Another point we should pay some attention is Executor which comes from JDK 5. Instead of explicitly calling new MyThread().start() to invoke the new thread, Executor can decouple the task submission from the real method perform -> executor.execute(new MyThread()).