Hi.

I have two tables:

LOTES

CODIGO    ARTICULO                 EXIST
000001      01                             10
000002      01                             15
000003      01                             20

LINEAS

CODIGO    ARTICULO                CANTIDAD
000001      01                             5
000001      01                             3
000002      01                             10

and this query:

UPDATE LOTES
SET EXIST=EXIST+SELECT SUM(CANTIDAD) FROM LINEAS WHERE LOTES.CODIGO=LINEAS.CODIGO AND LOTES.ARTICULO=LINEAS.ARTICULO

I execute this query and this is the result in table

LOTES

CODIGO    ARTICULO                 EXIST
000001      01                             18
000002      01                             25
000003      01                             NULL

I want that record with field CODIGO='000003' must result 20 instead of NULL, what must I do?

Thank's

Francisco


You problem is that your subselect is returning null (I think) and NULL plus anything = NULL

Never tried it with a subselect but COALESCE should work. Something like


UPDATE LOTES
SET EXIST=EXIST+COALESCE(SELECT SUM(CANTIDAD) FROM LINEAS WHERE LOTES.CODIGO=LINEAS.CODIGO AND LOTES.ARTICULO=LINEAS.ARTICULO,0)


Roy Lambert [Team Elevate]

Roy.

Great.

Thank's and regards.

Francisco

For all SUMs in a SQL database which uses NULLs it is important to know that NULL + SomeValue = NULL.

This is annoying behaviour ... but it is the standard.

The way round which I use is:

SELECT
 IF(SomeField IS NULL THEN 0 ELSE SomeField) + IF(SomeOtherField IS NULL THEN 0 ELSE SomeOtherField) as FirstResultField

... it is tiresome but it works. As Roy showed COALLESE also works, but doesn't allow complex calculation, i.e. subtraction, multiplication, division, etc. which you may need.

Adam

>COALLESE also works, but doesn't allow complex calculation, i.e. subtraction, multiplication, division, etc. which you may need.

Why not?

From the manual

<<The COALESCE function returns the first non-NULL value from a list of expressions. There is no limit to the number of expressions that can be passed as parameters, and the expressions can be of any type.>>

Roy Lambert

Adam,

<< This is annoying behaviour ... but it is the standard. >>

It's also cropping up in .NET languages and Javascript, so get used to it.


--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< This is annoying behaviour ... but it is the standard. >>
>
>It's also cropping up in .NET languages and Javascript, so get used to it.
>

Used to it: yes
Like it: NO

Roy Lambert

Adam.

Thank's for your response but COALESCE works in this query:

UPDATE LOTES
SET EXIST=EXIST+(SELECT Coalesce(Sum(CANTIDAD), 0) FROM COMPRASLINEAS WHERE ARTICULO=ARTICULO AND LOTES.CODIGO=COMPRASLINEAS.TRAZABIL
AND COMPRASLINEAS.FECHA>=DATE '2011-07-02') WHERE ALMACEN='03'

Regards.