[Solved] Query - Type of empty column

[Hypno]

I want to make a union of several tables and other SQL queries, there are date fields in these tables. However, some parts from this union have blank fields created because there are no dates in some parts. When I do a union of those queries where the fields have dates it's OK, when I do a union separately where there are no dates it's also OK, but I can't union both those with dates and those without. I get a type mismatch error. Is it possible to force an empty field in a query to be typed as a date? Something like
SELECT '' as "Date1" as DateType from "table1"

Something like null would be nice but for the date type something like NullDate

[Hypno]

I solved the problem by creating a table with one record containing a blank date field and appending this table to the query. The solution works but is light years away from elegance and common sense.
More and more I come to the conclusion that OpenOffice Base is for masochists.

[eremmel]

More and more I come to the conclusion that OpenOffice Base is for masochists.

OO Base is just a front end to a database. By default it uses a rather old database (HSQLDB 1.8) internally. You are free to connect to a external database of your choice. An external database will have for sure better capabilities.

Most of time there are many solutions for a problem. My experience is that my own knowledge is most of the time the limiting factor not a tool.

[keme]

Not sure exactly what causes your issue, but I think UNION should not present data type issues with identically named (or labeled) fields, unless you have group operations or use your query to update other tables. With more detail provided, the cause/solution may dawn upon us

Have you looked into COALESCE? That may be relevant if intermittent null values cause error.

I guess it is not supported with AOO Base "embedded" (old HSQLDB version), but your sig says LibreOffice. I think all the "native" LO DB drivers support COALESCE. All current standalone DB services should also work fine.

Edit: Memory failed me. I stand corrected. Thanks Charlie.it!

[charlie.it]

I guess it is not supported with AOO Base "embedded" (old HSQLDB version) ...

COALESCE is supported in the embedded HSQLDB 1.8.

[keme]

SQL explicit typecasting sprang to mind. If you are having data type issues, this may be what you are looking for.
https://www.sqlite.org/lang_expr.html#castexpr

[dreamquartz]

Hello,

If a record must be part of a UNION ALL, but is not part of a certain table/view or it is not needed/wanted in a UNION ALL, it can be excluded, but it still needs to be identified in the UNION ALL, because all parts of the UNION must be equal.
An example is in the top part of the UNION ALL, where I am using NULL "NickName".
"NickName" is a valid record, but I did not want/need it in the top part for trying to find 'double' entries in the table "tPerson". I did need it in the bottom part.

As indicated by others, all the records must be of the same type to prevent mismatches as well.

Code: Select all

SELECT
	 "a"."PersonID",
	 CASE WHEN
		 "a"."GivenName" IS NULL
		 THEN
			 COALESCE ( "a"."Surname", '' )
		 ELSE
			 COALESCE ( "a"."Surname" || ',', '' )
	 END ||
		 CASE WHEN
			 "a"."Surname" IS NULL
				 THEN
					 COALESCE ( "a"."GivenName", '' )
				 ELSE
					 COALESCE ( ' ' || "a"."GivenName", '' )
		 END "Person",
	 "a"."Surname",
	 "a"."GivenName",
	 NULL "NickName",
	 NULL "DateOfBirth",
	 COUNT( * ) "Rank"
 FROM
	 "tPerson" "a"
		 LEFT JOIN "tPerson" "b" ON
			 "a"."PersonID" >= "b"."PersonID" AND
			 "a"."Surname" = "b"."Surname" AND
			 "a"."GivenName" = "b"."GivenName"
 GROUP BY
	 "a"."Surname",
	 "a"."GivenName",
	 "a"."Nickname",
	 "a"."DateOfBirth",
	 "a"."PersonID"
UNION ALL
SELECT
	 "a"."PersonID",
	 CASE WHEN
		 "a"."Nickname" IS NULL
		 THEN
			 COALESCE ( "a"."Surname", '' )
		 ELSE
			 COALESCE ( "a"."Surname" || ',', '' )
	 END ||
		 CASE WHEN
			 "a"."Surname" IS NULL
				 THEN
					 COALESCE ( "a"."Nickname", '' )
				 ELSE
					 COALESCE ( ' ' || "a"."Nickname", '' )
		 END "Person",
	 "a"."Surname",
	 NULL "GivenName",
	 "a"."Nickname",
	 NULL "DateOfBirth",
	 COUNT( * ) "Rank"
 FROM
	 "tPerson" "a"
		 LEFT JOIN "tPerson" "b" ON
			 "a"."PersonID" >= "b"."PersonID" AND
			 "a"."Surname" = "b"."Surname" AND
			 "a"."GivenName" = "b"."Nickname"
GROUP BY
	 "a"."Surname",
	 "a"."GivenName",
	 "a"."Nickname",
	 "a"."DateOfBirth",
	 "a"."PersonID"

Hopes this helps as well

Dream

[Cazer]

Good to hear you found a workaround, even if it's not the most elegant solution! As dreamquartz suggested, using 'UNION ALL' and 'NULL' placeholders for fields that are not part of certain queries could be a cleaner alternative. It would let you keep the structure of your UNION intact, without resorting to extra tables.

Also, 'COALESCE' is a good tool to remember for handling NULL values. It returns the first non-null value in a list, which can be useful when dealing with intermittent null values.