SELECT s_house,s_post_dir,s_street,s_suffix FROM parcels WHERE s_pin
= '1201703303520'; s_house | s_post_dir | s_street | s_suffix ---------+------------+----------------+---------- 34643 |
| FIG TREE WOODS |
So to get "34643 FIG TREE WOODS" what do I do ?
SELECT s_house || ' ' || s_post_dir || ' ' || s_street || ' ' ||
s_suffix FROM parcels WHERE s_pin = '1201703303520'; ?column? ----------
(1 row)
I have tried all manner of COALESCE and various trickeries. Nothing works. In Informix this works exactly as I think it should. Is Informix totally whack, or what ?
If the blank fields are null then Informix is wrong. String concatenated with null gives null.
SELECT coalesce(s_house,'') || ' ' || coalesce(s_post_dir,'') ...
To be honest, if the address fields are blank then they should be set to the empty string. They're not "unknown" they're empty.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
