How to keep values from not being filled with .NULL.?

[ontsjc]

Hello all,

This is probably pretty simple but I can't seem to find the answer. When I run the following select statement, fields from the parties.dbf that don't have a corresponding record in Case.dbf fill in with .NULL. Is there anyway to just have them empty? Neither dbf structure supports null values.

SELECT DISTINCT * FROM case left JOIN parties ON case.ivjc = parties.ivjc;
WHERE BETWEEN(case.in_date,(dStartDate),(dEDate)) AND ALLTRIM(case.county) = ALLTRIM(cCounty) AND 'Community' $ type;
INTO CURSOR main readwrite

 

[bborissov]

NO, when you use * as a field lists.
SELECT-SQL ALWAYS returns NULL values for non matching fields.
But if you use proper field list value (I hardly recommend this to you) then you could use NVL() function:

SELECT DISTINCT Field1, Field2,;
                NVL(Parties.Num10_2_Field,0000000.00) AS PartField1,;
                NVL(Parties.Char_10_Field,SPACE(10)) AS PartField2
...
FROM ...
LEFT ...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Mike Lewis]

Borislav,

I'm curious to know why you don't recommend it. I've always used a field list and NVL() in these circumstances.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[ontsjc]

Thanks, I appreciate the response. Another funny thing, is that the values don't seem to uniformially evaluate as a null value. For instance, event though a particular value reads .NULL., ISNULL() returns .F. any thoughts on why this is?

 

[Olaf Doschke]

JOINS create NULL values in result cursor fields, even if source tables have no nulls whatsoever. This is to mark fields that could NOT be joined. Either you want a RIGHT join of parties or an INNER join, which then would only give you results with matching records from both source tables.

I doubt that ISNULL does return .F. on a real .NULL. value. That would be a bug. If you do ISNULL(fieldname) make sure you are on a record and not at EOF(). It may look you are on a record with NULL but in fact you are at EOF(). At that point ISNULL(field) will return .F.

Bye, Olaf.

 

[TamarGranor]

Mike - I suspect that Borislav meant that he "heartily" recommended, not that he "hardly" recommended it.

Tamar

 

[ontsjc]

Think I was just tired when the ISNULL wasn't working for me. Seem to work fine now that I've gotten back to it. Thanks all.

 

[Mike Lewis]

Tamar,

I suspect that Borislav meant that he "heartily" recommended, not that he "hardly" recommended it.

Ah, yes. Very perceptive.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[bborissov]

I don't recommend to use:

[SELECT * FROM]

but ALWAYS use

SELECT (field list) FROM

My mistake
Both words are so close

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.