Help with select statement vfp9

[Tom]

I'm trying to write a select statement that will return all orders for
a customer, whether that customer number is in the bill-to, ship-to or
sold-to field of the order (it would only be in one of those for any
given order). The cust table is indexed on custnum. The order table
is indexed on billcust, shipcust and soldcust.

So I have:

SELECT cust.custnum, order.orderno ;
FROM cust ;
LEFT JOIN order ON (what?) ;
WHERE cust.custnum == 12345

I have tried:

LEFT JOIN order ON (cust.custnum == order.billcust OR cust.custnum ==
order.shipcust OR cust.custnum == order.soldcust)

and that syntax is apparently valid but the query is brutally long and
I have bailed on it every time after about 15 minutes and 3% complete.

Any help appreciated.

Thanks,
Tom

[JayB]

1. did you try to create an index for each of those order fields?

2. why bother joining with cust table if no other fields are coming from
there? that will slow it down.

3.also you can use "union"
select distinct custnum,orderno from order where billcust=?lkpcust
union
select distinct custnum,orderno from order where shipcust=?lkpcust
etc...

you can join back to cust on each line above if you had to
jay

[Tom]

On Mar 23, 5:19 pm, JayB <J...@audiman.net> wrote:
> 1. did you try to create an index for each of those order fields?
>
> 2. why bother joining with cust table if no other fields are coming from
> there? that will slow it down.
>
> 3.also you can use "union"
> select distinct custnum,orderno from order where billcust=?lkpcust
> union
> select distinct custnum,orderno from order where shipcust=?lkpcust
> etc...
>
> you can join back to cust on each line above if you had to
> jay

Thanks for your reply.

re 1. It's not my app. I don't have control of the database I am just
programming reports around it. But I could select into a cursor and
then index that.
re 2. - I tried to simplify my example and instead I can see I created
confusion. I need the cust table because I am selecting for all
customers where cust.type = "X" not custnum = 12345.
re 3. I will give this a try and let you know. I'm a rookie at vfp
programming and have never used the union clause.

Thanks again Jay.

[Bernhard Sander]

Hi Tom,

> re 1. It's not my app. I don't have control of the database I am just
> programming reports around it. But I could select into a cursor and
> then index that.

You could create temporary indices:
INDEX ON billcust TAG xBillcust OF extra.cdx
INDEX ON shipcust TAG xShipcust OF extra.cdx

Don't forget to delete them after you are done with the report.
Since they are only known at your instance of the program and not at other
workstations in the network, you have to create them again every time you need them.

Regards
Bernhard Sander

[JayB]

everything depends on the fields you're trying to get out,
and how many records are in each of those tables.
besides orderno, what are you returning from the orders table?

creating temporary indexes as Bernhard showed is one way, but that can
take long if there are a lot of records, and will be done often.
i never do that anymore unless i need it for a relation.

the union may be your best bet.
after you get a table of distinct customers and orders into a temptable
then you can select the customers that match
select distinct * from cust where custno in (select custnum from temptable)
select distinct * from order where ordno in (select ordno from temptable)

this is another way, depending on what you need to do.

[Tom]

Thank you everyone. The UNION worked perfectly and quickly. I have
never used it before and was only peripherally aware of its
existence. So, the first SELECT matched the orders where a customer
was in the bill-to and the second SELECT matched the orders where the
customer was in the ship-to, etc. and UNION joined them all into one
cursor.

Thanks again, I appreciate your help.
-Tom