John, If the coalesce function is available then one answer is: select coalesce(s1,0) + coalesce(s2,0) + coalesce(s3,0) as TotalAmount Coalesce takes the first non-null value as the result. It has other uses, but this is a particularly handy one. Note that some implementations of SQL won't have it (MSSQL definitely does, but ISTR Interbase doesn't for example).
Cheers Pedrocelli ----- Original Message ----- From: "John - Pacific Data Systems (NZ)" <[EMAIL PROTECTED]> To: "DUG" <[EMAIL PROTECTED]> Sent: Wednesday, April 21, 2004 8:36 PM Subject: [DUG] simple db query Hi all, I'd like to add multiple fields in a db query. That is not a problem, like: select s1+s2+s3 as TotalAmount However, when one of the field values is null, then the result is zero. How can I get this to work? TIA, John _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi
