>Greetings,
>
>To integrate with our UPS system requires using ODBC to our VFP databases and specifying the query required to pull sales order and customer data to populate the shipping address.
>
>The "simple" query that works looks like this...
>
>SELECT somast.sono, somast.orddate, arcust.company, arcust.contact, arcust.address1, arcust.address2, arcust.city, arcust.state, arcust.zip, arcust.phone from arcust, somast where arcust.custno = somast.custno and sono = ?vp_sono
>
>
>Now here comes the rub... When the somast record contains an "X" in the field "tosw", the customer address is pulled from an entirely different table, soaddr, instead of the arcust table. All the fields in the other table are the same except that the soaddr table also contains the sono so each sales order can have a different shipping address. I'm just not sure how to get a single query (as required by the UPS system through ODBC) that will return the correct address depending on the "X" (or no "X") in the "tosw" field in somast.
>
>Any suggestions?
>
>Greg

To simplify (becuase I don't know what fields you must get from arcust and what from soaddr) I will use only Address1 field, all others yuou can get the similar way

SELECT somast.sono,;
       somast.orddate,;
       IIF(somast.tosw == [X], arcust.address1, somast.address1) AS address1;
from somast;
LEFT JOIN arcust ON somast.custno = arcust.custno
LEFT JOIN soaddr ON somast.custno = soaddr.custno
where  put_alias_here.sono = ?vp_sono