Re: Default Values in outer joins

Peter Lancashire <Peter.Lancashire.PL1@bayer.co.uk> offerred: +Volker Fraenkle wrote: +> is it possible to receive default values within an outer join instead of +> null-values? +> +> Example: +> +> Table A (ID INTEGER, NAME CHAR(10)) +> Table B (A_ID, ID, NAMECHAR(20)) +> +> SELECT A.NAME MASTER, B.NAME DETAIL +> FROM A, OUTER B +> WHERE A.ID = B.A_ID +> +> If there is no corrosponding row in table B, I would like to receive +> "NONE" in DETAIL instead of NULL +> +> Thanks, Volker + +Although I haven't tested it, this should be possible with the NVL +expression in version 7.3. Before that, you would have to SELECT into a +temporary table and then UPDATE that. + +The SQL-92 standard has a slightly more general version of NVL: the +COALESCE expression. Does the non-standard NVL come from Orifice? Yes. The NVL() function was among the "Oracle compatability" features added to IDS 7.3. Note that the same functionality and more is available in Informix with the CASE statement, which also encompasses and surpasses the Oracle-compatible DECODE() function (also available in IDS 7.3). To wit: SELECT A.NAME MASTER, CASE WHEN B.NAME IS NULL THEN "NONE" WHEN B.NAME = "Brad" THEN "Bradford" WHEN B.NAME MATCHES "Jo*" THEN "Joseph" ELSE B.NAME END DETAIL FROM A, OUTER B WHERE A.ID = B.A_ID Dave ** Dave Kosenko <davek@summitdata.com> ** Director of Training Services (732) 469-4070 ** Summit Data Group (an Informix Authorized Education Center) ** Find my advice useful? Let me teach you everything I know about ** Informix. Sign up for OFFICIAL Informix training at SDG. ** For details, see http://www.summitdata.com/training