Tuesday, April 29, 2008

Handling NULL in SQL Statements

It is easy to be confused by how NULLs are handled by the SQL engine. NULL can be referred to as undefined or does not exist. When evaluating an expression which includes a NULL ( ex. 5 - NULL ) cannot be evaluated and thus returns a NULL.

This is particularly frustrating when concatenating strings together. For example: concatenating first, middle and last names together to get a full name can return NULL if any of the three fields is NULL.

EXCEPTIONS

There are two exceptions to this rule. First SQL Aggregate functions ( i.e. SUM, MAX, MIN, AVG, COUNT ) ignore NULLs and return results based on the values in the specified fields. The second exception are Logical or Boolean fields. Many implementations of SQL treat an unknown in a Boolean data type as True. Advantage does not treat a NULL in a Boolean field as True.

It is important to note that an empty value is different than a NULL. DBF files do not support NULLs and thus have a default empty value. With the FoxPro support included in Advantage version 9.0 allows for NULLs in FoxPro DBF files. ADT (proprietary format) tables have always supported NULLs.

Using IFNULL

IFNULL is the easiest function to use to handle NULL fields within your SQL statement. IFNULL is equivalent to the following IIF statement

IIF (Field IS NULL, <alternate value>, Field)

The <alternate value> is the value you want to be returned when the selected field is NULL. In the case of string concatenation this could be an empty string ( '' ). The IFNULL function condenses this statement and allows for better readability. The syntax is below:

IFNULL ( Field, <alternate value> )

COALESCE

The COALESCE statement returns the first non-NULL expression result. It is equivalent to the following CASE statement:

CASE
WHEN value1 IS NOT NULL THEN value1
WHEN value2 IS NOT NULL THEN value2
WHEN value3 IS NOT NULL THEN value3
...
END

The COALESCE statement condenses this complex CASE statement into a more readable format.

COALESCE ( value1, value2, value3, ... )

This article on Wikipedia contains a lot of useful information on how SQL handles NULLs.

Monday, April 21, 2008

Advantage News and Tech Tips

The monthly Advantage Newsletter will be sent out today. The newsletter includes Product News, Press coverage, Events, Tech Tip and new KB Items. It is an excellent way to get a snapshot on what is happening in the Advantage world. You can subscribe to the Advantage Newsletter here.

Starting this month and continuing on for the next several months the Tech Tips will focus on the new features included in Advantage 9.0. This months tip is about support for the SQL MERGE statement. MERGE is often referred to as an "upsert" because it allows for an INSERT and UPDATE statement to be combined into a single statement. Essentially it runs an update statement for records that match specified criteria and an INSERT statement on records that do not match the criteria. The syntax looks like this:

MERGE INTO table1 USING table2 ON condition WHEN MATCHED THEN UPDATE SET column1 = value1[, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT column1 [, column2 ...] VALUES (value1 [, value2 ...])

You can read the April Tech Tip here and you can see a full list of tech tips on the Advantage DevZone. You can get more information about the MERGE statement in the Advantage Help File.

Thursday, April 17, 2008

European Advantage Conference - Kassel Germany

The European Adavntage Conference had its second stop in Kassel Germany on April 14th and 15th. There were about 50 attendees from around Southern Europe. We taught the same classes as we did in London last week.
Just like London we had great participation from the attendees and we received a lot of good feedback about how they are using Advantage. Once again I would like to thank all of the people who attended the conference.
I would also like to thank the Advantage staff from the iAnywhere office in Germany. Kemal Haslaman, Tiberius Stelli and Joachim Duerr were the ones who really made this event work. Be sure to watch the web site for the next Advantage Conference.

Sunday, April 13, 2008

European Advantage Conference - London

The European Advantage Conference (EAC) began in London on Thursday. It is always an excellent conference and this year was no exception. The schedule is very busy with 4 separate tracks along with three group sessions. This makes for some very long days but they are very valuable.
We received some excellent ideas and feedback from all the attendees. I would like to thank all of the people who attended my sessions and I look forward to coming back again.

Wednesday, April 2, 2008

Advantage Technical Summit

The Advantage Technical Summit began today in Boise Idaho. The summit is two days of Advantage specific training taught by the Advantage staff. The event is held at least twice a year and is well worth the trip to beautiful Boise. We hope to see you at our next training, visit the Advantage web site for details on the next event.