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.
Friday, June 24, 2011
Tuesday, May 24, 2011
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.
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.
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.
Monday, May 16, 2011
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.
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.
Sunday, May 15, 2011
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.
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.
Monday, April 25, 2011
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?
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.
Wednesday, April 20, 2011
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!
<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!
Subscribe to:
Posts (Atom)