Jerry,
Which version of Oracle are you using. Oracle did not add support for the 'OUTER JOIN' syntax until version 9.
Prior to that you have to use the (+) on your join statements. For example, the syntax below will return all the rows in the SO table and Nulls if there is no item.
Mace
TEXT to lc_SQL TEXTMERGE NOSHOW PRETEXT 3
SELECT *
FROM myOracle.SO So, myOracle.ITEM Item
WHERE So.ITEM = Item.ITEM (+)
AND So.so = ?lc_so
ORDER BY So.so_line
ENDTEXT
lc_SQL = STRTRAN(lc_SQL, CHR(13) + CHR(10), [ ])
ln_result = SQLEXEC(myconn, lc_sql, 'c_so')
>How can I perform a LEFT or RIGHT join on 2 tables in Oracle from VFP? I can get an inner join to work but not a Left or Right.
>
>
>***Works.
>TEXT to lc_SQL TEXTMERGE NOSHOW PRETEXT 3
>SELECT *
> FROM myOracle.SO So, myOracle.ITEM Item
> WHERE So.ITEM = Item.ITEM
> AND So.so = ?lc_so
> ORDER BY So.so_line
>ENDTEXT
>lc_SQL = STRTRAN(lc_SQL, CHR(13) + CHR(10), [ ])
>ln_result = SQLEXEC(myconn, lc_sql, 'c_so')
>
>
>***Does NOT work. Returns a -1.
>TEXT to lc_SQL TEXTMERGE NOSHOW PRETEXT 3
>SELECT *
> FROM myOracle.SO So LEFT JOIN myOracle.ITEM Item
> ON So.ITEM = Item.ITEM
> AND So.so = ?lc_so
> ORDER BY So.so_line
>ENDTEXT
>lc_SQL = STRTRAN(lc_SQL, CHR(13) + CHR(10), [ ])
>ln_result = SQLEXEC(myconn, lc_sql, 'c_so')
>
>
>Thanks,
>
>Jerry