Excluding "empty" rows

[#1]

I have a table which has 7 fields and it is getting additional rows where the first field is filled but the fields 2-6 are blank.

I need to load (resident) ONLY the rows where any one of fields 2-6 are are complete.

I have tried (and had no success) with "LOAD * Resident CSRelated ...

     where not isnull(Field1) and

	(not isnull(Field3) or
	not isnull(Field4) or
	not isnull(Field5) or
	not isnull(Field6) or
	not isnull(Field7))

     ;

or

       where len(Field2&Field3&Field4&Field5&Field6&Field7)>0;

or any number of other permutations.

Suggestions please. Thanks

@excluderow

[hemhund2016]

Hi Derek,

You can use like below.

CSRelated_New:

LOAD * Resident CSRelated

where not isnull(Prod_INSTANCE_ID)

or Prod_INSTANCE_ID<>'-';

Drop table CSRelated;

Thanks,

Hemanth.

[swuehl]

You could try to check against LEN(TRIM(FIELD)) instead of NOT ISNULL(FIELD), but as far as I interpret your screen shot, that should not perform differently here.

Besides that, take care to do a NOCONCATENATE LOAD when you do a LOAD * RESIDENT ..., to avoid auto-concatenation of records.