>>>I am doing a sql select with LEFT OUTER to a child table. If the child table does not have a record in the master table I still get the record returned (that's what I want), but the values for the fields of the child record are filled with nulls. I've tried SET NULL OFF but no luck. The problem is that I am inserting these records into another table that does not allow null values. Is there a way to do this query without returning nulls for those records?
>>>
>>>Thanks.
>>>
>>>John
>>John,
>>Maybe there is an easier way but this is what I would do. :
>>- select nvl(child.field1,0) as fied1,nvl(child.field2,"") as field2 ...
>>- or after select blank nulls
>>

for ix = 1 to fcount()
>>  cField = field(ix)
>>  blank fields &cField for isnull(&cField)
>>endfor

Cetin
>
>Hi Cetin,
>
>I think your second idea will work for me here. I just don't remember nulls populating fields from a select before. Is this a new feature in 5.0a? hmmm...I did just start using the new sql syntax (LEFT OUTER etc.)...Does this have anything to do with the returned nulls?
>
>Thanks for your help!
>
>John.
John,
Wellcome to 5.0. Yes you guessed well, it's a result of outer join. Before there was only "inner join" (directly with one simple SQL w/o workarounds I mean-where table1.id = table2.id), each table had corresponding records for join. Now with outer joins, a table might have no corresponding records so the values are .null.
Cetin