Query with some blank fields

samueltmcf · February 23, 2020, 3:36pm

In my SQL database, I have a query that is designed to make display names of the data. It is combining several columns from the table into one field with the following code:

SELECT "ID" || '-' || "Name" || '-' || "Tag Ear" || '-' || "Tag Color" || '-' || "Tag #" FROM "Stock"

It is working well except that not every row in the table has data in the “Name” field. In those cases those rows do not display in the query.

Is there a way to add a space or some sort of placeholder for those instances in order to still display the rest of the information?

Thank you

Ratslinger · February 23, 2020, 9:20pm

Hello,

Please note that SQL is not a database but a language used to communicate with a database. Base has available as a default ether HSQLDB embedded or Firebird embedded databases. Each database may have variations of SQL but most follow a standard. Unfortunately the HSQLDB embedded version (v1.8) included with Base is very old and lacks many features available in newer databases.

samueltmcf · February 23, 2020, 9:46pm

That is a good distinction to recognize. I appreciate your explanation.

Ratslinger · February 23, 2020, 9:15pm

Hello,

When dealing with NULL fields you can use the Coalesce function. It will use the first non-Null value found:

....|| Coalesce("Name",'none provided') ||...

You can substitute 'none provided' with whatever you wish including a space - ' '. Do this whereever you have a problem.

samueltmcf · February 23, 2020, 9:46pm

This worked. Thank you very much!

Ratslinger · February 24, 2020, 12:25am

Hello,

Please help others to know the question has been answered by clicking on the in upper left area of answer which satisfied the question.