JOIN without .NULL. 3

[linousa]

Is it possible to change this query to have blanks instead of .NULL. values:

Select t1.*, t2.ends, t2.imbc, t2.ord, t2.csz;
	from table1 t1 ;
	left Join table2 t2 ;
	on t1.unirec=t2.unirec ;
	into Table currentfile2.dbf

Or may be even a better way to accomplish this, what I am trying to do is to add four fields(ends,imbc,ord,csz) from table2 to table1 on unirec key and I need to have blanks instead of .NULL.

 

[Olaf Doschke]

NULLs are blanks. Yes VFP has that concept, but it's legacy. SET NULLDISPLAY TO ''.

You can only prevent NULLs with an INNER join, but then don't extend table1, you only get the intersection of data with a match.

A way to get any value you want for new fields: Extend table1 with the new fields, set default values (if you can, i.e. the table is a database table), fill in data from table2 via UPDATE or REPLACE or SEEK/SCATTER/GATHER, whatever you're most familiar with.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TamarGranor]

If by blanks, you mean empty fields, you can use NVL to make that happen:

[pre]
Select t1.*, NVL(t2.ends,''), NVL(t2.imbc,''), NVL(t2.ord,''), NVL(t2.csz,'');
from table1 t1 ;
left Join table2 t2 ;
on t1.unirec=t2.unirec ;
into Table currentfile2.dbf[/pre]

I've assumed all the fields from T2 are character. If that's not the case, specify the appropriate empty value instead of ''.

Tamar

 

[linousa]

SET NULLDISPLAY TO '' did the trick and UPDATE might be a better choice, because REPLACE and SEEK/SCATTER/GATHER will be a bit slower as both tables around 500K records.

Thank you Olaf and as always you've been of great help!

 

[Olaf Doschke]

At least UPDATE is the most modern form. A scan with SEEK or SET RELATION and SCATTER/GATHER is not very different in timing, if you have the indexes, indeed it forces you to have the index that also boosts a correlated UPDATE.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Dan Freeman]

Nulls are NOT empty. They have meaning in truly relational databases.

SET NULLDISPLAY was introduced early in the FB implementation of NULL to help us "cheat" the use of actual nulls.

It's better to change your query (as Tamar showed) to avoid null in the result than to alter your data or the simple display of your data to mask the presence of nulls. Nulls still exist when you tell VFP to display them as "".

 

[Olaf Doschke]

NULL are not empty and blank isn't empty, too.
NULL has the meaning of an absence of values and that's the case here, if the join condition doesn't have something.

It's not just a matter of taste, I think it's much better to use NULLs or indeed keep the data separate in 2 tables and only join them in queries.

But forcing empty string values (or wheever, depending on type is empty or blank) is working against the NULL concept, that perfectly fits and describes the data situation.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Besides that SET NULLDISPLAY most often fills the knowledge gap how to handle the display, besides I offered a solution to extend the table to have any values you like as the new defaults and then merge in data of table2 where it exists.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

This example shows "the evil" about blank values:

Create Cursor crsBlank (cText C(10), lBool L)
Set Fields Global
Set Fields To All
Set Fields To textisblank=IsBlank(cText), textisempty=Empty(cText), boolisblank=IsBlank(lBool), boolisempty=Empty(lBool)
Append Blank
Append Blank
replace cText with '', lBool with .f.
Browse

The subtle difference of empty vs blank shows what a trap this VFP exclusive concept is. The whole database world lives with NULL as the absence of data and you should get used to embracing this to indicate precisely what it is. Yes, you have to also know NVL when you need to handle expressions with terms, which can be NULL, and then also mention ISNULL() for checking and field IS NULL within SQL queries, even if you offer a solution to change NULL to something you want in your result.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Dan Freeman]

NULL actually has meaning, depending on the circumstances.

In a system logging results of medical tests, an empty numeric field will evaluate as numeric 0. What if the test was not administered that day? The 0 skews averages. A NULL in the field indicates no result for that day. This was pointed out to me while working on a system making "quality of life" decisions over whether to continue specific treatments for terminal patients.

NULL is not just a quirky empty.

 

[Olaf Doschke]

NULL is not just a quirky empty.

Yes, that's what I'm saying:

NULL has the meaning of an absence of values

I earlier said NULLs are blanks, and to me, that means the same, to VFP the blank concept even differs from empty and is closest to NULL but handles the value of fields as if they are empty(ß for numeric, .f. for bools, etc)

VFPs blank (as ISBLANK() returns) is a quirky empty, not NULL. It is almost meant the way NULL is meant, as ISBLANK() returns .T. for any field, that wasn't set in an INSERT or APPEND BLANK, but its value is the same as that of an empty field.


You have to know how to deal with NULLs, as NULL is "contageous". A NULL in an expression makes the whole expression NULL.

VFPs biggest weakness about NULL is executing an else branch, which treats NULL as FALSE:

Create Cursor nulltest (avalue int NULL)
Insert into nulltest Values (1)
Insert into nulltest Values (10)
Insert into nulltest Values (100)
Insert into nulltest Values (.null.)

Clear
Scan 
   If avalue>Pi()
      ? "avalue="+Transform(avalue)+" is > Pi"
   Else
      ? "avalue="+Transform(avalue)+" is not > Pi"
   EndIf
   If avalue<Pi()
      ? "avalue="+Transform(avalue)+" is < Pi"
   Else
      ? "avalue="+Transform(avalue)+" is not < Pi"
   Endif
EndScan

That is by design, so keep in mind the ELSE branch doesn't state the IF expression is .F. (false) if the else branch executes you only know the IF expression is not .t. (not true). Other programming languages are strict about that and NULL gives you a NULL exception or no branch executes. It's not even wrong but has to be kept in mind.

NULL is handled as in database theory in SUM()s and AVG():

Create Cursor nulltest (avalue int NULL)
Insert into nulltest Values (1)
Insert into nulltest Values (10)
Insert into nulltest Values (100)
Insert into nulltest Values (.null.)

* notice avg is computed as 111/3:
Select Sum(avalue) as ntotal, Avg(avalue) as naverage, Count(*) as ncountall, Count(avalue) as ncountvalues from nulltest

Use
Create Cursor nulltest (avalue int NULL)
Insert into nulltest Values (1)
Insert into nulltest Values (10)
Insert into nulltest Values (100)
Insert into nulltest Values (0)

* notice avg is computed as 111/4:
Select Sum(avalue) as ntotal, Avg(avalue) as naverage, Count(*) as ncountall, Count(avalue) as ncountvalues from nulltest

As already said you have ISNULL() and NVL() to treat NULLs guiding IF what you actually want to test and handling NULL as a replacement value. And you CAN decide, if you know that's right, your new table doesn't have NULL in new columns. as I already said, rather extend a table and give fields a default, then only update rows with a match and you have that result. And if you only need that in a query result, I'd not query INTO TABLE, making it a permanent DBF file, but anyway you choose Tamar's NVL() expressions then are what prevents NULLs for you, too.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name