[reordered to chronological]
At 16:45 2014-07-29, "Fernando D. Bozzo" <[email protected]> wrote:
2014-07-30 1:41 GMT+02:00 Sytze de Boer <[email protected]>:
> Fernando, this is really helpful, but I'm stuck on the .NULL. matter.
> i.e. I get the error message, field Qty does not accept null values
In this case, something like this:
UPDATE stocktable SET qty = ( select SUM( *NVL(qty,0)* ) as ttl FROM
invtable where invtable.stockcode=stocktable.code )
I'm not sure if I'm using NVL apropiately in this case (because of the SUM
function), but NVL() is the function to intercept NULLs and convert to 0 or
any value you want.
If qty can have nulls, then yes, BUT ...
SQL is weird in that the sum of zero items is .null. To deal
with that, you need a nvl() around the select. I think that would be:
UPDATE stocktable SET qty = (>>>nvl(<<<select SUM( *NVL(qty,0)*
)>>>,0)<<< as ttl FROM
invtable where invtable.stockcode=stocktable.code )
My changes are bracketed with ">>>" and "<<<". (I might have
the parens wrong. The point is that the result of the select might
be .null., and you have to deal with that.)
Sincerely,
Gene Wirchenko
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
