Re: Default Values in outer joins

Volker Fraenkle wrote: > > Hello to everyone, > > 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? -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/