>All fields are character.
>
>Table1 fields:
>
>aid
>aagcy
>adescriptn
>
>Table2 fields:
>
>bidv (same value as aid in table1)
>bname
>bvalue
>
>I want to join the above two tables and do the following:
>
>1. Create a single record for every distinct or unique aid record in table1
whether or not a match exists in table2.
>2. Include the fields: table1.aid, table1.aagcy, table2.bvalue
>3. If there is no matching record in table2, then I want to populate the bvalue field in the joined cursor with an empty string value ''.
>
>I am stuck on a simple sql statement which amazes me. I just can't concentrate today :o( Maybe it was the only 2 hours of sleep I got last night? :o)
Or:
SELECT DISTINCT table1.aid, table1.aagcy, NVL(table2.bvalue,SPACE(xxxx)) AS bValue;
FROM Table1;
LEFT JOIN Table2 ON Table1.aid == Table2.bidv
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.