>I have a view
>
>SELECT SUM(IIF(.NOT.EMPTY(Subscriber.c_premiumproduct_id),1,0)) AS getpremiumproduct,;
> SUM(IIF(EMPTY(Subscriber.c_premiumproduct_id),1,0)) AS dontgetpremiumproduct;
> FROM subscription!subscriber FULL JOIN subscription!gifts ;
> ON Subscriber.c_id = Gifts.c_id;
> WHERE Subscriber.c_id <> Gifts.c_id;
> AND (Subscriber.i_last_issue_sent=0 = .T.;
> AND Subscriber.l_paid = .T.;
> AND Subscriber.i_renewals = 0;
> AND Subscriber.l_online_subscriber = .F.;
> AND Subscriber.c_region = ?c_region_id;
> AND Subscriber.l_start_with_next_issue = .F.;
> AND Subscriber.l_cancel = .F.)
>
>In this particular case I have two tables a subscriber table and a giver table. I only want to select those records which don't have a matching record in the giver table; therefore where subscriber.c_id <> gifts.c_id. Is this the best way to handle this. Also I am trying to optimize the select in which I have created an index for each of where clause with the exception of the subscriber.c_id <> gifts.c_id. However each of the tables do have a index on the c_id. One thing I haven't done however is to create an index on deleted(). I am just wondering if I have provided enough for this to be optimized and execute as fast as possible.
You can use SYS(3054, 11) to check if the view is optimized.
Erik Moore
Clientelligence