how to calculate when you have NULL fields 2

[axc25]

Hello! i used left join on 2 tables and i obteined a third one with the value NULL where the rows didn`t match:

ID: 1, 2, 3, 4
VALA:2000, 3000, 4000, 5000
RATA:100, 200, NULL, NULL
SOLD: 1900, 2800, 0, 0

(if u don`t understant from the example above i uploded the excel file)

The column SOLD is calculated as (VALA-RATE). For Id=1 and ID=2 it`s calculated ok but for ID=3 and ID=4 it returns 0 and I want to have the value from VALA column.

how can i do this?

 

[TamarGranor]

Use the NVL function:

NVL(VALA-RATE, VALA)

Tamar

 

[JRB-Bldr]

Assuming that you are using VFP SQL Query syntax you can add a criteria line to your SQL Query - something like:

WHERE <whatever>;
   AND !ISNULL(field1) ;
   AND !ISNULL(field2) ;
   AND !ISNULL(field3) ;
   AND !ISNULL(field4)

That should eliminate any record where all fields are NULL.

If you want to change the AND to OR, then it will eliminate any record where any of the fields are NULL.

NOTE - the VFP syntax above will not work in other non-VFP database data tables if you were to attempt to execute it via a SQLEXEC() since those queries need to use the query syntax native to the backend.


Good Luck,
JRB-Bldr

 

[Mike Lewis]

the VFP syntax above will not work in other non-VFP database data tables if you were to attempt to execute it via a SQLEXEC() since those queries need to use the query syntax native to the backend.

That's right.

The standard (ANSI-92) syntax is:

... WHERE ... AND Field2 IS NOT NULL ....

This also works in VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Actually, if you remove records containing NULL, you turn a left or outer join into an inner join. Like Tamar suggested, NVL(field,0) is your friend here, if you want to convert NULL to 0.

As axc25 used a left join the case of the first two fields to be NULL may never to be expected, if those fields are not nullable, NULLs in the third and fourth column indeed can come into the result by finding no match, even if the second table also has not nullable fields, that's simply the nature of a left join.

Bye, Olaf.