[Solved] CASE statement returning nulls

[superfuntastic]

I have a .csv datasource full of contact info that I'm trying to query with the following abridged CASE statement:

Code: Select all

SELECT "Name", 
  "Nickname", 
  CASE
    WHEN "Nickname" IS NULL THEN "Name" 
    ELSE "Nickname"
  END AS "Normalized_Name"
FROM "contacts"

The results look something like this:

Code: Select all

NAME         NICKNAME  NORMALIZED_NAME
John Smith   Johnny     
Jane Doe     Jenny 

Any idea why the Normalized Name column returns nulls when running the query? The above query was just for troubleshooting, the end result is to simply return either the Name or the Nickname alongside other fields when populating mailing addresses on a form. I'm also open to alternative solutions as well if this can be handled on the form side.

Thanks in advance!

[Villeroy]

I tried with other conditions such as "Nickname" = 'Johnny', "Nickname" = '' and length("Nickname")=0 with same results.
It looks as if the construct CASE WHEN THEN END does not work with the file based drivers for csv, dBase and spreadsheets.
This is the 18 year old documentation: http://www.openoffice.org/dba/specifica ... tions.html

It is possible to use a true database driver for this, MySQL, PostgreSQL or even the HSQL driver that is shipped with your office suite. Then you have the full set of functions provided by that driver. You may even edit the csv files through Base.
[Tutorial] Using csv/text files as editable data source.
1) Create a blank new database of type "embedded HSQL"
2) Use Tools>SQL... with a CREATE TEXT TABLE statement.
3) Bind the text table to some input file with import parameters with the SET TABLE statement

[superfuntastic]

Thanks for the quick response. Ultimately I ended up editing the source data to fill Nickname with Name if it was null, but in the future I will try a different driver.

[dreamquartz]

Thanks for the quick response. Ultimately I ended up editing the source data to fill Nickname with Name if it was null, but in the future I will try a different driver.

Even that you consider the issue closed, this is how I solved it.

Code: Select all

SELECT
	 CASE WHEN "tPerson"."GivenName" IS NULL
		 THEN
			 COALESCE ( "tPerson"."Surname", '' )
		 ELSE
			 COALESCE ( "tPerson"."Surname" || ',', '' )
	 END ||
		 CASE WHEN "tPerson"."Surname" IS NULL
			 THEN
				 COALESCE ( "tPerson"."GivenName", '' )
			 ELSE
				 COALESCE ( ' ' || "tPerson"."GivenName", '' )
		 END ||
			 COALESCE ( ' (' || "tPerson"."Nickname" || ')', '' ) ||
			 COALESCE ( ' ' || "tPerson"."MiddleInitial(s)", '' ) ||
			 COALESCE ( ' ' || "tPerson"."Suffix", '' ) ||
			 COALESCE ( ' (' || TO_CHAR( "tPerson"."DateOfBirth", 'MM/DD/YYYY' ) || ')', '' ) "Person",
	 "PersonID"
 FROM
	 "tPerson"
 ORDER BY
	 "Person" ASC

I come across a lot of names provided, where they use the following as a variation:
Jones, Nick
Jones, Nicholas
Jones, Axel
Mr. jones
Their registered name is actually: Jones, Nicholas Axel.
Where they all are the same person

What ever they provide the first time, I always fill out Surname, GivenName.
There MUST be an entry in GivenName, if anything is provided.
The content of the GivenName can therefore change from Axel to Nick to Nicholas, when the full information is provided.

When updated information comes to light, e.g. the GivenName is actually the NickName, I will update the info.

When is Person provides different information, I am sometimes having to check against Surname-DOB.
Sometimes that does not even work, and I end up getting a response form the database where the EmailAddress or the PhoneNumber is already recorded.
Then I have to investigate.
Sometimes it is even impossible to determine still.

Hope this helps,

Dream