Dylan Wan

COALESCE function and CASE WHEN

2011-12-01T20:20:00.000-08:00

I tried to compare COALESCE and CASE WHEN.



SELECT  SUM(val)

FROM    (

  select CASE

       WHEN 1 IS NULL THEN  LENGTH(RAWTOHEX(SYS_GUID()))

       ELSE  1

       END AS val

  FROM dual

  CONNECT BY level = 10000

  );

There is no difference!

COALESCE function

2011-12-01T19:03:00.000-08:00

I have been using NVL. However, I think that we should all move to use COALESCE.

COALESCE is more modern function that is a part of ANSI-92 standard.
NVL is Oracle specific, it was introduced in 80's before there were any standards.

This testing describes the reason:

In case of two values, they are synonyms.
However, they are implemented differently.
NVL always evaluates both arguments, while COALESCE stops evaluation whenever it finds first non-NULL:

SELECT  SUM(val)
FROM    (
        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

This runs for almost 0.5 seconds, since it generates SYS_GUID()'s, despite 1 being not a NULL.

SELECT  SUM(val)
FROM    (
        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

This understands that 1 is not a NULL and does not evaluate the second argument.

SYS_GUID's are not generated and the query is instant.

COALESCE is much efficient than NVL!

Fixed barrack, floating soldiers

2011-08-05T17:24:00.000-07:00

I read a good article. It is written in Chinese

铁打的营盘流水的兵

This sentence's original meaning was "Soldiers come soldiers go, the military camps stay fortified." In military, soldiers typically stayed for a fixed period, such as two years. However, there are regular missions and work have to done, and cannot be stopped.

If staff changing is expected, the way how to handle the situation is to keep some staff changes, not as often as others.

However, I also disagree that an organization should keep adding new people. Adding people requires team building activities. Changing the team member too often may make the team under an unstable state.

A good way to handle it is that assuming soldier only stay two years. Replacing half of them a year and keep the other half not changed. You will still have people can lead. You need to have some sergeants who can stay longer and lieutenants who may stay even longer.

Keeping everyone staying as long as possible may not be a good practice if there is not much growing opportunities inside, but a lot of great job opportunities outside.

Create a Quick Code for your contact information

2011-06-19T00:04:00.000-07:00

I found that an easy way may be use PingTag.

I have been using Linkedin for several year. PingTag can create the QR Code from the Linedin information.

vCard and QR Code

2011-06-18T23:58:00.000-07:00

vCard is an old standard. It is a way to represent the contact information. It is a digital version of a business card.

When you receive a business card, you probably want to enter the data into your cell phone.

This is where the QR Code comes in.

QR Code is a way to get information into your phone. Nowadays, phones almost all have the camera capability. QR Code is a way to let you take a picture of the bar code and to scan the bar code into your phone. One image can contain a lot of information.

Combining QR Code and vCard allows you to pass the information from the business card to your phone, without manually typing those information.

See also:
How to create a business card QR code
6 Good Reasons to Add a QR Code to Your Business Card

Gnumeric

2011-06-04T22:19:00.000-07:00

I have been using Gnumeric for several months. Although people still use Excel, I have set Gnumeric as the main tool to open Excel file lately.

Excel for some unknown reasons cannot work together with the web conference system I am using. A ridiculous workaround is that while I am using the web conference system, I have to open Microsoft Net meeting. I guess some secret code from Microsoft only compatible with Net Meeting.

This is so annoying. I do not really need any new advanced features from Excel 2007. For me, Excel 2007 is a defect product if I can use an older version of Excel with web conference system without any problem.

I found that Gnumeric is actually faster than Excel in turn of opening the file and show data.

Almost all functions are compatible.

Rest Is The Preparation For The Further Journey

2011-04-04T10:38:00.000-07:00

休息是为了走更远的路

A feature I am looking for from a presentation software

2011-04-01T22:30:00.000-07:00

It should be easy to group a list of slide and give them a section overview.

Each slide should have six navigation buttons in Play mode:

Home
End
Next
Previous
Back to Section Overview
Next Section

The side navigation panel in the Design mode should show the outline structure.
Use the Section Header slide to "group" other sides and allow collapse and expand.

I found an old article "A Flexible Alternative to PowerPoint" on the net. It said the same thing:

One of the disadvantages cited by Tufte and others is that PowerPoint tends to confine the speaker to a single pre-set path, discouraging spontaneity and diminishing flexibility in response to the audience's interests.

Browsers can not only emulate much of PowerPoint, they can also do things that PowerPoint does not....A table-of-contents window can be on screen at all times, allowing you to jump spontaneously to different topics.

Revolution

2011-03-10T12:12:00.000-08:00

Jython or Python?

2011-02-07T14:51:00.000-08:00

I like Jython.

It supports unicode. Python does not support it well by default until Python3.

However, I still would prefer if we have Jython support for Python 3.

C:\>cd jython2.5.1
jython.bat

c = '斌'
c
'\xe6\x96\x8c'
print (c)
斌

It is working!!

The End of the Beginning

2011-01-27T15:21:00.000-08:00

Now this is not the end. It is not even the beginning of the end. But it is, perhaps, the end of the beginning.
~Winston Churchill

Oracle Application Express(4)

2011-01-22T20:32:00.000-08:00

Sample Applications

The best way to create a new apps is actually copying and modifying the sample apps.

People Search
Bug Tracker
Document Library

http://www.oracle.com/technetwork/developer-tools/apex/application-express/packaged-apps-090453.html

Oracle Application Express(3)

2011-01-22T16:34:00.000-08:00

SQL Workshop

SQL Workshop is an existing web application available in the workspace.

It may confuse those people who do not have SQL background and do not know anything about database.

However, since the APEX service providers assume that the workspace users are database application developers., so it provides such applications.

Most database developers use some kind of the database client software, such as SQL Developer. However, I do not think that you are allowed to use the SQL developer to access the hosted APEX database. The SQL Workshop is provided for serving the purposes:

1. Create Tables and other objects (view, index, sequence, synonym, trigger, etc.)
2. Create PL/SQL package, function, or procedure.
3. Query the database

It is the "SQL*Plus" for accessing APEX database.

However, it is also designed for those who do not know SQL and it provides wizard like step by step process to create tables and create queries. I think that most of the real database application developers may not need them. But it is a good learning tool for those who do not have SQL or database experiences.

You do not have to use those wizards. You can still do DDL and DML using SQL.

You can create scripts and upload your scripts. It is a little IDE for PL/SQL.

You can also upload data.

Oracle Application Express (2)

2011-01-22T16:19:00.000-08:00

Application Workspace registration

You need to provide your name and your email during the registration.

Your email address will be used as the 1st user account that is assigned to this workspace.

Each workspace can have multiple users.

The user account created using the email address will be the workspace administrator.

You can create other users after you login.

However, the users created under the workspace are really the developers.

If people are just users of the applications, do not create them as users directly under the workspace.

Related Posts:

Oracle Application Express (1)

Oracle Application Express (1)

2011-01-22T16:14:00.000-08:00

You can get an account here:

apex.oracle.com.

It is a hosted service that allows you to create web applications.

All these applications are database applications.

Behind the scene, an Oracle database is there.

After you register, an Oracle database schema is created for your workspace.

The application modules are declarative apps. The metadata is, of course, stored in the database.

ARIS Express for drawing diagrams

2010-12-21T19:55:00.000-08:00

I just downloaded the ARIS Express. It seems a pretty good drawing tool.

The Wikipedia page has a very good introduction. It particularly highlighted the "Noteworthy features".

The ARIS Community web site provides the online video tutorials.

Oracle EBS Data Model

2010-12-13T01:43:00.000-08:00

Oracle EBS provides the technical reference guide in eTRM. eTRM is a documentation service provided by Oracle to the customers that have the support contract - etrm.oracle.com.

Oracle EBS stores the tables and descriptions in FND_TABLES and FND_COLUMNS. If the table and columns are registered using AD_DD, the table and column descriptions will be stored in the above tables.

See Also:
http://oracledollars.forums-free.com/all_tab_cols-t30.html
http://www.allguru.net/oracle-apps-dba/fnd_tables-fnd_columns/

To get the correct list of columns for a table, the Oralce database data dictionary should be used. FND tables store the design time data. The FND table and column definition may be stored ahead of time before you apply a patch. The obsolete columns may still be represented in the tables.

Here is a script I found that merge the FND and Oracle database data dictionary.

http://www.sap-img.com/oracle-database/script-to-create-a-data-dictionary-listing-which-combines-the-oracle-dictionary-with-the-oracle-apps-dictionary.htm

Oracle EBS does not define the foreign keys within the database. In order to get the reference information, FND tables should be used. Oracle EBS does not use the USER_TAB_COMMENTS or USER_COL_COMMENTS.

Golf Team vs. Basketball team

2010-11-05T19:15:00.001-07:00

I learned this from a management class I attended recently.

For a Golf team, you add the individual scores up to get the team's score. You put best players together to get the best score.

For a Basketball team, the team members need to work together to make score. You putting the best players together does not means that you have a best team.

As a manager, you have to make people work together. People can do a lot more than what individuals cannot do.

On the other hand, I have learned a old Chinese story: Three Monks, when I was a kid:

A young monk lives a simple life in a temple on top of a hill. He has one daily task of hauling two buckets of water up the hill. He tries to share the job with another monk, but the carry pole is only long enough for one bucket. The arrival of a third monk prompts everyone to expect that someone else will take on the chore. Consequently, no one fetches water though everybody is thirsty.

It is a big challenge for a manager to make people working together so 1 + 1 > 2, or at least 1 + 1 won't less than 2.

.

Customer Orientation vs Competitor Orientation

2010-11-03T00:13:00.000-07:00

This is a very old topic. I first touch this topic while I was in college. I have always keeping this topic in my mind.

First, this is what I think about Competitor Orientation -

You only need to be better than others. As long as your customer do not have any other choice, you can be the king and do whatever you want.

And, Customer Orientation -

Customer is the king. You should try your best to satisfy your customer' need.

In software development, customer oriented design means thinking of what customer is looking for and competitor oriented design means looking at what competitor is offering.

In my experience, the competitor oriented design is a dangerous approach and really hurts the industry. However, unfortunately, both competition and customers lead us to take that approach.

A very simple example - or the most stupid thing I have seen - but still frequently happen is about adding columns to tables.

People seems never learned from mistakes.

Since the competitor's product has such column, we should add this column. Since the competitor's product has these many of columns, we should add more than what they have.

Does the customer really buy the product based on the number of columns?

Project Investiment Decision

2010-10-21T07:42:00.000-07:00

The project investment decision should be based on the future forecasting.

What happened in the past is just sunk cost.

People frequently make mistake by using the "current" state to determine the future investment.

The "current" is only useful if it can be used to predict "future" in the investment decision.

A product like XX, which is the core product in Suite WW and is in almost all installations and the product XX is a very mature product.

Does that mean that we need to allocate more on it?

It is only meaningful if we assume that the more customers are using it, the more engineers need to be allocated to maintain it. Unfortunately it is typically not the case.

Should the size of the team match the revenue that the product is bringing? It sounds reasonable. The cost and revenue typically do not match in the same year. If you use operational leverage, the break even point can be multiple years. You won't do any big, potentially high return projects if you only look at the current revenues. You will never do new projects.

The revenue itself is not always a good measurement since the nature of the number is "short term". A better financial goal is to maximizing the shareholder value. We should look at the long term ROI. The discount cash flow can support the goal much better.

The discount cash flow concept sounds simple but seems frequently overlooked.

For each project or proposal, what we can do is that for each bucket, we can calculate the following:

how much revenue potential and thus the cash in flow it will bring in,
plus the cost saving the project can bring to us,
minus the initial cost to create it,
minus the cost to sustain it.

All of them need to present as the current value then we compare these initiatives.

One difficulty for making investment decision for software products is about the existing commitments, such as those over commitments done by sales. On the other hand, there are also implicit commitments. When a customer bought a product in the past, they are not just buying the product feature that available at that time. They also buy products from enterprise apps vendor to assume that the vendor will take care of their future needs. They believe that the product has a future.

Out of Box cross product integrations

2010-10-05T11:20:00.000-07:00

I have been working on application integration since the day one I started my career in enterprise apps development. It is really an interesting topic.

On one hand, we would like to ship the integration out of box, so the customer can buy the individual products, as well as, "buy" the integration solution.

On the other hand, we may also not want to enable integration out of box and assume all customers are using all products.

If the product is reflecting the best practice and would like demonstrate the best way to utilize the product, we may want to build and ship the integrated solution.

What is the guiding principles?

I think that the targets should be

Provide the solution - We should not expect the solution is a customization just because it is hard to deliver the solution.
Make the solution OOTB as an option- It is a product the customer can purchase, but do not assume that everyone has it
Easy to disable and enable it - We also need to document how to enable or disable it.

Also, pre-packaged apps is designed based on the majority of the customers who are the targets of the products. If most customers are looking for the OOTB solution, we can ship the enabled integration and let the customer to disable it. If most of customers are not yet ready for an integrated solution, we can disable it in the OOTB solution and let the customers to enable it.

Interesting blog comparing work cultures from different companies

2010-09-12T22:28:00.000-07:00

I found Li Gong's blog recently. I am interesting in this Li Gong since he was one of the person who worked for Sun and gained Java related patents for Sun. His name mentioned in two of the patents that mentioned in the recent document "COMPLAINT FOR PATENT AND COPYRIGHT INFRINGEMENT Oracle vs. Google".

I do not have any opinion and any comment about the case. I was a Java developer when I first joined Oracle in 1996. My first project is to use Java to build an application integration workbench. I was one of very few engineers who had a chance to work on Java related project at e-business suite development team at that time.

Lately I found that Android is a quite interesting and seems a popular platform. I gave up Window CE based Black Jack and moved to Black Berry Curve last year. While many my co-workers are using iPhone, I am considering to have a HTC with Android for my next phone if possible by the end of this year.

Li Gong's post talked about the culture differences between Oracle, Sun, and Microsoft. His conclusion is - “go back to school, do not venture out, it is a jungle out there”, the world wishes the best to all Sun folks.

We have gone through several acquisition at Oracle and my co-workers from various companies from PeopleSoft, Siebel, Hyperion, Retail, Agile, G-Log, Sunoposis (ODI), Golden Gate, etc. I found that I am quite happy to have a chance to work with these smart people joined from various companies and learned the advance technologies and applications, as well as the valuable experiences from them.

Yahoo!Bookmarks is horrible

2010-09-07T00:58:00.000-07:00

I have not used del.icio.us for a while. I am reorganizing my bookmarks and decided to get Yahoo!Bookmark a try.

I cannot believe that this is a product from Yahoo!. It has a very poor UI design.

1. I am confused about the Folder and Tag switch.

I do not understand why we need the concept of "Folder". I think that if they would like to introduce the tree-like structure as the folder, they should provide a default folder structure as a starting point. The structure should, of course, allows the users to customize, including add and deleting folder entries.

Also, they know to display more than one level in their directory service. They use a Tree like UI on the left that is different from their traditional directory service.

2. I do not know how to add a category
The UI should allow the user to add a category from the Add bookmark popup.

3. I do not know what "Save a Copy" means.

4. I am also confused with "Quick List". Why do we need it?

They have a decent product called del.icio.us. I am not sure why they reinvent something such poor.

5. The bookmark icon in the toolbar also confuse me.

6. If I want to edit an entry, why I cannot just select a link next to the entry. I do not understand what the UI designer thinks.

7. Why do I have to select more than one entry in order to update the category?

If I just simply change the category or remove tags for an entry, why do I need to 1) select the checkbox; 2) use the top action links. Why not just show a delete checkbox and let me delete it immediately?

Multi-select should not be the only option.

What are important to our success?

2010-06-25T22:31:00.000-07:00

1. People

Great people build great product. We need smart people.

2. Accountability

Putting great people together is not enough. People should be accountable. Giving them responsibility as well as authority. Who does what and how to reward people based on their performance.

3. Truth or Facts

We need to make decision based on facts, not based on imaginations. We can manage by numbers.

4. Technical Excellence

As a leading software company, we should drive technology advancement.

5. Focus

We cannot do everything or at least do everything at the same time. Resource is not unlimited. Properly use our resources in the area that can improve our margin.

Mobile Apps Wish List 1

2010-06-17T17:57:00.000-07:00

1. Dial In to a conference call with one button

It should allow me to program the call script first. I should be able to save a phone number and a series of keys and save the time waiting between pressing these keys.

This is so simple function. Not sure if any phone can support this.

2. Join the Web conference to view slide show

I think that the change required on the web conference technology. They need to support a more light weight presentation/ screen play.

Maybe only supporting powerpoint like slide show should be enough. Many web conference is showing PPT. We do not need such high bandwidth communication in between.

The slides can be sent in one page at a time. Mouse movement events do not have to be passed.

Also, text content should be treated as text, not image, to lower the traffic.

Then I should be able to view it from my phone.

3. Check Availability of the people I am calling

I am able to see the status before I contact people via Instant Messenger. I should be able to do the same thing in my phone.

This can be a separate message sent to the person's mobile phone. The mobile phone should answer this type of requests to a list of people in the contact list.

I should be able to publish my status via my phone with the text message "busy", "in a meeting", "studying", or "free to talk" and people should be able to check before they call me. It will lower the traffic too.

4. Pre-record messages for a short period of time

I should be able to pre-record the message for people calling me. The feature is similar to answer machine. I can say "I am in a meeting now, please press #6 if this is urgent, otherwise, I will be available at 3pm, please press #5 if you want to leave me a voice message."

I should be able to set the end time of this voice message to 3pm, just like setting the vacation auto response emails.

5. Low batttery reminder

My phone should call me to remind me. Some alarm will help.

6. Check bus schedule and active status

The car should publish where it is to the network. The subscription program should be able to show the current location on a map.

This does not necessary requiring a GPS.

The bus is going with a scheduled routine. When the bus is reached a stop, the bus driver can just press a button to recognize the bus's location. The key is that the message should be sent to a server or to be published using some message to the network. (If this is done by a human being, it will be just a phone call to someone.)

More to come.....