Hi Michael,

Don't use function on left right, it's very, very slow.
Should problem if state_cd is type VARCHAR/VARCHAR2 - it's bad idea for code fields - and save in DB ADDR_DTL:"USA" and STATE:"USA ".

MartinJ

>Hi All,
>
>I'm having trouble with the WHERE clause of this Oracle querry:
>
>SELECT ADDR_DTL.addr_no,
> ADDR_DTL.addr_line,
> ADDR_DTL.city,
> ADDR_DTL.zip_cd,
> ADDR_DTL.state_cd,
> ADDR_DTL.cntry_cd,
> NVL(STATE.state_desc, 'N/A') AS state_desc,
> NVL(COUNTRY.cntry_desc, 'N/A') AS cntry_desc
>FROM ADDR_DTL,
> STATE,
> COUNTRY
>WHERE ADDR_DTL.addr_no = 5637
> AND ADDR_DTL.state_cd = STATE.state_cd (+)
> AND ADDR_DTL.cntry_cd = COUNTRY.cntry_cd (+)
>
>This works only when there are no BLANKS in either of the 'state_cd' or 'cntry_cd' fields.
>
>WHERE ADDR_DTL.addr_no = 5637
> AND RTRIM(ADDR_DTL.state_cd) = RTRIM(STATE.state_cd)
> AND RTRIM(ADDR_DTL.cntry_cd) = RTRIM(COUNTRY.cntry_cd)
>
>This works if 'ADDR_DTL.state_cd' & 'ADDR_DTL.cntry_cd' have matching values.
>
>How can I make this work in all cases?
>
>TIA
>Mike
>
>P.S. VFP6 SP5 Oracle 8i