At 19:06 -0400 5/23/03, David Granger wrote:
>Hi:
>
>This is my first time using mySQL lists so please be gentle if I am
>not following proper protocol.
>
>My problem is with the following SQL script. I'm using mySQL ver.
>3.23.51 running on Win 98SE and Win XP Pro.
>
>insert into tblInvoiceDetail (orderID, lineNumber, partID,
>partDescription, unitCost, extension, orderDate, clientID, quantity,
>PSTValue)
>select orderID, lineNumber, partID, workItemDescription, unitCost,
>(partsNet+xenonNet), orderDate, clientID, qtyShipped, PSTValue
>from tblClientSummary
>where transaction= 'Sales Order' and Status <> 'Invoiced'
>
>The script works perfectly on the Win XP machine but does not on the
>Win98 machine.
>
>The problem on the 98 system is that the (partsNet+xenonNet)
>equation in the 'select' statement returns a null value to the
>extension field in tblInvoiceDetail table, even when there is data
>in one of the fields in the tblClientSummary table. If I edit the
>equation to just select either partsNet or xenonNet, with no
>equation, then the value is inserted.
>
>I should also point out that partsNet or xenonNet will NEVER have
>values in both fields. One of the two fields will always be 0.
But 0 *is* a value. Do you mean that one of the two fields will always be
NULL? If so, you might want to replace (partsNet+xenonNet) with
COALESCE(partsNet,xenonNet)
COALESCE() returns the first non-NULL values from among its arguments,
or NULL if they are all NULL.
You don't want to use (partsNet+xenonNet) if either of the values are NULL.
Arithmetic involving NULL values always results in a NULL result.
>
>I will be upgrading both systems to the newest version of mySQL over
>the weekend, but I thought I would post the question and hope that
>the problem can be explained or the error of my ways pointed out to
>me. Thanks in advance for any help.
>
>Kindest regards,
>
>
>David.
>
>
>
>
>
>
>Website Design, Management & Hosting
>Database Solutions for Small Business
>
>Granger HighTech Inc.
>108 Hiltz Avenue
>Toronto, ON, Canada
>(416)-778-7040
>www.ght.ca
>david@stripped
--
Paul DuBois
http://www.kitebird.com/
sql, query
