Good afternoon Foxperts,

One of the hats I wear involves maintaining a FP2.5/DOS application that was patched together long ago. In spite of it's weaknesses, it does serve the client purposes. Data design is one of those weaknesses.

I've been asked to generate a report based on five sets of fields that I am attempting to normalize with the following statement:

SELECT cust_code, route_no, ;
		cqty1 AS cqty, csize1 AS csize, ctype1 AS ctype ;
	FROM customer ;
	WHERE INLIST(cust_stat,"A ","S ") .AND. cqty1>0 ;
UNION ALL ;
SELECT cust_code, route_no, ;
		cqty2 AS cqty, csize2 AS csize, ctype2 AS ctype ;
	FROM customer ;
	WHERE INLIST(cust_stat,"A ","S ") .AND. cqty2>0 ;
UNION ALL ;
SELECT cust_code, route_no, ;
		cqty3 AS cqty, csize3 AS csize, ctype3 AS ctype ;
	FROM customer ;
	WHERE INLIST(cust_stat,"A ","S ") .AND. cqty3>0 ;
UNION ALL ;
SELECT cust_code, route_no, ;
		cqty4 AS cqty, csize4 AS csize, ctype4 AS ctype ;
	FROM customer ;
	WHERE INLIST(cust_stat,"A ","S ") .AND. cqty4>0 ;
UNION ALL ;
SELECT cust_code, route_no, ;
		cqty5 AS cqty, csize5 AS csize, ctype5 AS ctype ;
	FROM customer ;
	WHERE INLIST(cust_stat,"A ","S ") .AND. cqty5>0 ;
INTO TABLE cont_all

In other words, I am trying to take 15 columns of data and make it 3 columns ignoring the sets where the cqty? is zero. The SQL runs fine, but I get fewer records than I expect: 32 vs 38 in one case where there is something in the second set of fields for three records.

I suspect my weak knowledge of UNION vs JOIN (left right outer inner??) is where the better solution lies, but I can't figure it out.

TIA,