>Hello,
> this sql statement works in v5, but it doesn't work in v3. what can I do to make it work in V3 OR is there a command I can use in V3 that gives me the same result?
>
> SELECT BROKERS.AG_ID, ALLTRIM(BROKERS.AG_NAME) AS AG_NAME,;
> ALLTRIM(BROKERS.AG_STREET) AS AG_STREET, ALLTRIM;
> (BROKERS.AG_STREET2) AS AG_STREET2, ALLTRIM;
> (BROKERS.AG_CITY) AS AG_CITY, BROKERS.AG_STATE, ;
> BROKERS.AG_ZIPCODE, BROKERS.EOEXPDATE,IIF(ISNULL;
> (CONTACTS.PRETITLE), "NO REC", CONTACTS.PRETITLE);
> AS PRETITLE FROM BROKERS LEFT OUTER JOIN CONTACTS ;
> ON ( BROKERS.AG_ID = CONTACTS.AG_ID ) into cursor temp
>thanks.
>sherry

---

Sherry,
You could do a union :

select ... ,contacts.pretitle as "pretitle", .. ;
 from brokers ;
 where BROKERS.AG_ID = CONTACTS.AG_ID ;
union all ;
 select ... ,padr("NO REC",fsize("pretitle","contacts")) as "pretitle", .. ;
 from brokers ;
 where BROKERS.AG_ID not in (select distinct AG_ID from contacts) ;
into cursor temp

Cetin