Observe if you will:
contacts=> select * from person;
lastname|othernames|suffixes|creation |entity_id
--------+----------+--------+----------------------+---------
Clinton |Bill | |1999-09-07 18:39:06-07| 28
|Bob | |1999-09-13 23:43:16-07| 33
Rutledge|Shawn | |1999-09-13 00:51:28-07| 1
(3 rows)
contacts=> select * from company;
name |creation |entity_id
-----------------------+----------------------+---------
Alcoa |1999-09-03 18:41:32-07| 25
Peterbilt |1999-09-03 18:41:53-07| 26
US Government |1999-09-03 18:42:07-07| 27
Acme Manufacturing Inc.|1999-09-12 16:28:31-07| 30
ExpressBill |1999-09-12 19:37:47-07| 31
(5 rows)
contacts=> select othernames || ' ' || lastname || ' ' || suffixes as
name, entity_id from person union select name, entity_id from company;
name |entity_id
-----------------------+---------
Acme Manufacturing Inc.| 30
Alcoa | 25
Bill Clinton | 28
ExpressBill | 31
Peterbilt | 26
US Government | 27
| 1
| 33
(8 rows)
contacts=> select othernames || ' ' || lastname as name, entity_id from
person union select name, entity_id from company;
name |entity_id
-----------------------+---------
Acme Manufacturing Inc.| 30
Alcoa | 25
Bill Clinton | 28
ExpressBill | 31
Peterbilt | 26
Shawn Rutledge | 1
US Government | 27
| 33
(8 rows)
It would appear that when using a sequence of concatenation operators,
if
any of the selected varchars are null, then the entire concatenation
fails.
(Note that in the second union, "Shawn Rutledge" showed up despite not
having a "suffixes" value, but "Bob" did not show up because his entry
was missing the required "lastname". I can only assume that "Bill
Clinton"'s
suffixes field is blank rather than null.)
How can I fix this? I'd like the null info to be left out but the rest
of the operators to work.
--
_______
http://www.bigfoot.com/~ecloud
(_ | |_) ecloud@bigfoot.com finger
rutledge@cx47646-a.phnx1.az.home.com
__) | |
\__________________________________________________________________