.NULL in fields after SELECT Full Outer Join

[dgrewe]

For the first time I've have to use the full outer join command and the results of the fields have ".NULL" in them.
Any way to remove the ".NULL" ?

SELECT Projects.*, Newgpo.* ;
FROM projects FULL OUTER JOIN newgpo ;
ON Projects.project = Newgpo.project;
ORDER BY Projects.project;
INTO TABLE ibsgpodb.dbf
David W. Grewe
Dave@internationalbid.com

 

[wgcs]

The .NULL. values indicate fields that came from a table that did not have a match for that record. This is intrinsic to what a FULL OUTER JOIN means. There is no other way for SQL to provide that information, so the .NULL.'s can be quite useful.

What are you doing that you need to remove them?

It is better to use the information they provide with the ISNULL() function (EMPTY() doesn't work, because they aren't empty, they aren't anything, they are NULL).

But, if you're intent on getting rid of them, you can do this:

SELECT .... full outer join that produces null's INTO CURSOR cMyJoin
SELECT 0
USE ( DBF('cMyJoin') ) AGAIN ALIAS tMyJoin  && Make it read-write
SCAN
  llRepl = .F.
  SCATTER TO laRec MEMO
  FOR lnI=1 to FCOUNT()
    IF IsNull(laRec[lnI])
      llRepl = .T.
      lcType = Type(Field(lnI))
      DO CASE
        CASE lcType='C'
          laRec[lnI]=''
        CASE lcType='D'
          laRec[lnI]={}
        CASE lcType='N'
          laRec[lnI]=0 && or -1 or whatever you want
        CASE lcType='L'
          laRec[lnI]=.F.  && This is no good, but the best you can get
        CASE lcType='M'
          laRec[lnI]=''
      ENDCASE
    ENDIF
  ENDFOR
  IF llRepl
    GATHER FROM laRec MEMO
  ENDIF
ENDSCAN
* Now both cMyJoin and tMyJoin have no .NULL. values,
*   and instead have empty values where there was
*   no record to join

 

[rgbean]

Dave,
You can also use the NVL() function to "convert" any .null. values - either in the original SELECT, or just when you display the results.

Rick

 

[dgrewe]

All the fields are character fields so the table looks cleaner with white space instead of .NULL. in it.
Select creates a table with 350,000 records so I did not want to use the Scan; nvl(); endscan to clean it up.

I was hoping for an eaier way.

The other way I found to do it is inner join them,
Then relate back into the tables, one at a time, and suck in the ones that do not match.

Thanks for the ideas

 

[dgrewe]

All the fields are character fields so the table looks cleaner with white space instead of .NULL. in it.
Select creates a table with 350,000 records so I did not want to use the Scan; nvl(); endscan to clean it up.

I was hoping for an eaier way.

The other way I found to do it is inner join them,
Then relate back into the tables, one at a time, and suck in the ones that do not match.

Thanks for the ideas


David W. Grewe
Dave@internationalbid.com

 

[rgbean]

Dave,
In that case, as long as there aren't "a lot" of fields, you can just use the NVL() in the SELECT. e.g.
SELECT Projects.*, ;
nvl(Newgpo.field1, space(len(Newgpo.field1)) as Field1, ;
nvl(Newgpo.field2, space(len(Newgpo.field2)) as Field2, ;
nvl(Newgpo.field3, space(len(Newgpo.field3)) as Field3, ;
nvl(Newgpo.field4, space(len(Newgpo.field4)) as Field4, ;
...
FROM projects FULL OUTER JOIN newgpo ;
ON Projects.project = Newgpo.project;
ORDER BY Projects.project;
INTO TABLE ibsgpodb.dbf

Rick

 

[AChiado]

Hi,

if you want a NOT NULL value in your fields there are no other than Rick solution (I think).

But

if you want only DISPLAY your null fields without .NULL. value you can use this:

SET NULLDISPLAY TO "" Andrea C.P.
Italy