SQL COALESCE

I find it difficult to use this function. Its use is really limited.

The function coalesce(esp1,esp2....) is actually an expansion of NVL(esp, replacement) in some language.

Both function checks for NULL. NVL() is translated to if "esp" is null then use "replacement" otherwise use "esp". Coalesce() extends the "if" to many evaluations and has no "replacement" unless you specifically define one expression that is always true.

What then is the use of coalesce()? Basically, it allows you to return a value from a list of expressions in the order that you defines it. It will pick up the value of the first expression that return a NOTNULL.

In a more English term, the function allows you to choose the first field from a list of fields that is not empty.

In practice, I can't find a real use of it. At least not for the field of work I am in. However, I can imagine a practical use. For example, I have a list of prices for a given part. The prices are select in the order of preferences refurbished, local, regional and worldwide. If the higher preference price is not available then the next lower price preference is used. Usually, the different prices are not compiled. Rather, it is taken from joins that refers to different tables which are updated independently. Thus it is normally unknown if a particular preference has a value. Obviously, there must be another coalesce to indicate where the source of the price is from.

Another example is as follows.

I have a list of free gifts which I need to keep track of the stock. When customer indicates a choice. It is updated into the database at the particular choice field. I am then able to simply "count" the different choices made by customers. At the same time, I am able to view the choices made by individual customer. Two actions with just one update.

Choosing dates in SQL

If there is a datetime field in your database, chances is that you would want to choose a period between dates. There are more than one way to choose a period.

First I would show the wrong way.

Select mydate from mytable where datefield > '1/1/1900' and datefield < '1/31/1900'

This SQL is not wrong by itself. However, without time qualification, the default time is 00:00:00. It then posts a problem. ">" actually means greater than. This means that the date 1/1/1900 00:00:00 is excluded. "<" refers to less than. This means that the date 1/31/1900 00:00:00 and above will not be included too. In English, this condition means "exclusive" term.

A more correct way is as follows.

select my date from mytable where datefield >= '1/1/1900' and datefield < '2/1/1900'

Another way to select a period is as follows.

select my date from mytable where datefield between '1/1/1900' and '1/31/1900'

I am not sure if that is correct too since the time definition on the second date is missing. To be more exact the second date should be 1/31/1900 23:59:59.

Vlan Tag

Read from Blogger (mili liew) that you don't really have to use 2wire 5012NV for your Singtel NGBN.

There is a blog that details how you can use your own switch to connect to the Optical hub.

The suggested switch used is a "managed switch that support vlan tagging". Vlan tagging is actually a protocol IEEE 802.1Q. The switch must be able to support this tagging.

Tag used as belows.

vlan 10 - Internet

vlan 20 - MIO TV

vlan 30 - VOIP

vlan 40 - Management

I don't quite know what vlan 40 is used for.

Cleanup vista.

My harddisk has only 100G. 15 of which is used for Linux partitioning. There is not much left to play with. Recently, it has been reduced to 20G free space. Some desperate measures has to be done.

First I tried to remove manually all orphans from un-installed programs. It looked like AVG left quite a few GB storage. Don't know what they are used for. On my other pc, it only took up 150MB even without uninstalling.

Next I go to "my computer" right click c:\ and select "properties". Clicked "Disk Cleanup". It seems that there is a way to clean up "hibernation" file from here. Also at "more options" tab, you could clear all shadows except the latest. Shadows are actually "System Restore points". It took up almost 27GB for all the hdd in the computer. 19GB is actually for c:\. I did a "clean up" for "system restore and shadow copies".

I also did a defrag using "defragger". The result is impressive. I had 49GB free now.

SQL Server Replication issues

Still having problem doing the replication.

One thing I notice is that the user had to be both OS admin user and SQL Server Admin user. Just add servername\adminuser as user and allow sysadmin right to it. Subsequently, use this user for all replication.

Another thing is that error 2812 can be caused by a number of reasons. One blogger suggest to use "SP_removedbreplication 'databasename' (not the distribution database rather the actual database to be replicated). I tried that and it actually works.

It is a bad idea to remove the "distribution" database. SQL server does not cleanup after you. The publisher and distributor somehow still retains the information.

Brioquery does not refresh table structure

When you use BrioQuery, be aware that the tables in the sections does not refresh itself. If the server database alters the table structure, it will not be updated in your section. You need to delete the table and re-insert the same table to get updates.

WEBGL

After trying to get CUDA to work, I come across this technology called WebGL. It is a Web-based Graphics Library that could be controlled by Javascript and worked on HTML5 canvas.

I started on Firefox, it displays a message that "javascript" is needed. I enabled "javascript". It then tells me that "WebGL is enabled" but a setting in the browser stops it from running. However, there is no indication of what was it that stops it from running. I gave up on Firefox.

Next, I turn to Chrome. It runs perfectly without problem. Now this is done on Vista.

Today, I tried on my XP. Chrome refused to run WebGL. Firefox does not run also. On further digging, I managed to get Firefox to run WebGL. The setting is quite simple. Go to about:config and search for webgl. Just change webgl.force-enabled to "true". Also ensure that webgl.disabled is "false".

Turning back to Chrome, I have tried various methods as advised by forums without success. Most suggest having the command line setting like '--ignore-gpu-blacklist'. '--no-sandbox', '--enable-webgl', etc...

There is a suggestion to use about:flags and change "GPU accelerated composition" and "webgl" to on. However, "webgl" setting is not found. Thus, this setting does not apply for Chrome 11.

Further reading shows that Google disabled "webgl" on xp. Finally, I give up just like the "CUDA". It is just a waste of time. Until such utilities are mature, it is pointless to keep digging.

Haredware Acceleration is good if it works generally without technical knowledge. Browsers are not for techies only. It should not get the non-technical users scratching the head as to what went wrong. Chrome has made the situation even worse by blocking it from XP. At least Firefox still enables it to run if we do the correct setting.

I had enough of playing with Graphics. For crying out loud, I don't even have Vista premium. Why am I bothered with graphics on the web? Games? I don't play games that requires GPU or 3D.