Hello,
Using VFP8. I have a table: accountno c( 20 ), otherinfo c( 50 ), type c( 4 )
The accountno field lists account number, this field can have duplicates. The otherinfo field is what I'm trying to return and it can have duplicate data even per each accountno, the type field is a 4 byte field that constists of boolean switches (0000, 1111, 0101, etc). The second byte determines which of the accountno records is the primary one. If it is a 1, this is the primary record. Some accounts may not have a primary designator at all, so would be 0. I need to get a list of all the primary records, and a list of records that do not have a primary record, unique on accountno. I can do this if I SET ENGINEBEHAVIOR 70:
SELECT accountno, otherinfo, type ;
from table ;
into cursor curOtherInfo ;
where SUBSTR( type, 2, 1 ) = '1' ;
UNION ;
SELECT accountno, otherinfo, type ;
from table ;
where accountno NOT in ( ;
SELECT accountno ;
from table ;
where SUBSTR( type, 2, 1 ) = '1' ) ;
group by accountno
How the heck are you supposed to do this without setting enginebehavior to 70? Thanks,
jfh