February 7th, 2011
I have high respect for the stock prices of IBM and Oracle. But in operational sense, they are no different from much smaller establishments. In some sense they are much worse, because they claim to be the “analytics”, “decision” and “real-time” powerhouses, while having as much difficulty processing an unsubscribe request as your local florist.
I recently attended an analytics conference, and in a moment of keynote induced dizziness, swiped my conference card at Oracle and IBM booths. Since then, I have been flooded with their real time analytics “innovations”. Following the unsubscribe links doesn’t really help. Even though you do get a confirmation message, you continue to get the emails even weeks later. (Perhaps they use a different scale for “real-time”.) Oracle has problems with creating a working user-interface for unsubscribe requests as well. They do have an “Opt-Out completely” link, and this is where it brings you to:
There are only 3 triggers on the page: “login”, “create account” or “update profile”. “Update profile” link brings you to the login page, and after you login, there is no Opt Out option. So, while Oracle respects your right to unsubscribe, it has conveniently forgotten a mechanism that allows you to exercise that right.
Some convenience, and some UI designers.
July 31st, 2009
Venn Diagram queries in Oracle have saved the day for me many many times. Consider this:
You have a table “analyzethis”, and say mv (modelvalue) is in 0/1 and tv (TruthValue) is in 0/1. Then, the following query does something nice.
select sum (val), match from
(select count(*) val, ‘M’ as match from analyzethis where mv=1 and tv=1 union
select count(*), ‘M’ from analyzethis where mv=0 and tv=0 union
select count(*), ‘N’ from analyzethis where mv=1 and tv=0 union
select count(*), ‘N’ from analyzethis where mv=0 and tv=1)
group by match
This query returns:
Of course a similar result can also be obtained by decode, sign and square functions.
July 15th, 2009
Some things go without saying. Such as, a telephone number is a number. Like 703 123 4567. But if I go back to Dave Roberts, who taught me CSCI 242 an infinite number years ago, he would say, can you give me your telephone number, and add it to Peter’s and subtract Anna’s? Well, that really doesn’t make much sense.
In database modeling terms, telephone number is a String, a varchar2, a text. Not a number. This pretty much goes without saying as well, for anyone who has been data modeling for more than a few days. Here, are a couple of reasons telephone number is considered a String, not a number:
- It can have symbols. People enter their telephone numbers themselves, using hyphens, parenthesis, plus sign, alphabetical (1-800-CALL ATT), etc. The database mostly needs to save the data that the user entered, and show it later. It would be unacceptable to modify the user’s data and show it in a different format. Would be cute and dumb at the same time.
- You don’t do any Math on the telephone number. You don’t take average, or sum of all telephone numbers, like you do with invoice_total and avg _time_in_queue.
Now, there really are systems that store a telephone number as a number, not a varchar2. That is primarily done as a short cut for validation. These systems have those annoying (but perhaps highly functional) websites where you get those messages (“Please use numbers from 1-10 only.”) when trying to enter a white space or parenthesis in a telephone number. The good aspect of that is, that they are very very good at catching the 10 digit US/Canada telephone numbers. Works absolutely great for utility companies and gas electric cooperatives with US as their only market, but if international market space is your thing, then not so much.
I take this as my gentle remember, that all rules are meant to be learnt, and then we can break some. The good news is that you get to pick which ones you break! The bad news of course is that you need to learn them first, and then break them.
January 23rd, 2008
Consider a table in which you have addresses of the following form:
- 123 Main St, City, State, Country
- 123 Main St, Apt 1, City, State, Country
- 123 Main St, Apt 1, City, State 12345, Country
- 123 Main St, Apt 1, City, State 12345
- 123 Main St, Apt1, City, State 12345-1234
Say you want to extract the city portion of the data.
Following query does the trick for first three cases:
select DISTINCT REVERSE (SUBSTR(REVERSE(address), 1, INSTR(REVERSE(address),’,', 1, 3) – 2)) citystatecountry
Query can be easily modified to handle other similar cases as well.
I fully recognize that this Query is just clever, not optimal. In fact, if you are using Oracle 10g, you may be able to use a neat regexp to achieve this result easily. Still, this may help out someone who like me was stuck on 9i.
On the flip side, if you know of an easier way (or if you have the regexp working in 10g), please leave me a note.