Thursday, January 29, 2009

Missing rows when copy text to excel

I used Excel a lot to generate script. Client wanted a 100 row change, I give them a excel sheet and let them fill up the necessary then I'll write the excel formula to generate the script. Simple.

Then today, when I try to copy a column of text from a text file to excel (this will put the each line of text into the correct row) half of my text missing.

Dude, where's my row?

Try copy the following column of text to excel (version 2003). Expected is 7 rows but somehow it turn out only 3.

EMPP
#END#
@@@@@@
"
?
?
FFF

The problem is the @ and the come after ". Putting the single quote (') in front of the double quote will solve this issue.

The question is why this would happened? Is this a bug? I don't think anyone would bother because nobody is going to copy such a text to excel. :D

Friday, January 16, 2009

Date missing when query with timestamp

The date datatype column in database is rather misleading. This is because it can store 2 types of format. One is without the timestamp (01/01/2009) and the other is with timestamp (01/01/2009 12:00:00 PM). Both format can store in the same column!

The problem is when programmer code their program on the first view of that date column. Let say it currently stored (01/01/2009) and the query will follows as "... where datecolumn = to_date('01/01/2009', 'DD/MM/YYYY' and ....".

Unfortunately, the other programmer thought that seems the column is a date field, he might just store the current date ('01/01/2009 12:34:56 PM') or sysdate which have timestamp. This will leaad to the first programmer's query unable to work because "01/01/2009" represent "01/01/2009 0:00:00 AM".

The solution for this problem is to format all date query to same format (e.g. "DD/MM/YYYY").
Example: "... where to_char(datecolumn, 'DD/MM/YYYY') = '01/01/2009' and ..."

(imagine if you have 1000 date query in a procedure, that will be pain in the butt)

Or just ask the database vendor to make up their mind, that the date datatype should and only should have one format.

Or database vendor create another date datatype. One with datetimestamp, one without.

Therefore, everybody live happily ever after.

Tuesday, January 6, 2009

SQL condition on combined column

I got a strange finding on Oracle SQL today. Just take the example table below.

Column1 Column2
A B
A C
B A
B C
C A
C B

If I want to do a query to remove the third row only (B follows by A). Below is a query I would think of.

Select * from TableAbove where (column1 <> 'B' and column2 <> 'A');

Somehow the query removes all rows with A in column1 and B in column2, which is not I want. The expected result should be only the third row to be removed.

The solution I found so far, which is rather simple is to concatenate both column into one.

Select * from TableAbove where column1 || column2 <> 'BA';

The query is rather ironic because to select only a specific row, we use equal sign (e.g. column1 = 'A' and column2 = 'B'). To remove that specific row, replacing the equal sign (=) into not equal sign (<>) does not reflect the opposite behavior.

Perhaps there are some server setting to allow combined column condition queries. But for now, the concatenated column query at least solve my problem. :P