Hi Michael!

Michael D. Spence wrote:

>> I've been staring at this problem for some time, but can't seem to
>> make any progress. I'm using FB2. I have this select:
>>
>> Select S1."Acc ID", S1."Serv ID", S1."Last Performed", X.LP FROM
>> sched s1
>> inner join
>> SCHED S on S."Acc ID" = S1."Acc ID"
>> and S."Serv ID" = S1."Serv ID"
>> inner join
>> (select A."Acc ID" as ACCID, A."Serv ID" as SERVID,
>> max(A."Date") as LP
>> from artrans a
>> where A."Code" in (0, 1, 2, 10)
>> group by A."Acc ID", A."Serv ID") X
>> on S."Acc ID" = X.ACCID and S."Serv ID" = X.SERVID
>> where S."Last Performed" <> X.LP

Isn't this equivalent to:

Select S."Acc ID", S."Serv ID", S."Last Performed", max(A."Date")
FROM sched S
join artrans A
on S."Acc ID" = A."Acc ID"
and S."Serv ID" = A."Serv ID"
where A."Code" in (0, 1, 2, 10)
group by 1, 2, 3
having S."Last Performed" <> max(A."Date")

>> What I really would like to do is this:
>>
>> update SCHED Set "Last Performed" = (X.LP from the select above)
>>
>> I tried this:
>>
>> update sched s1 set s1."Last Performed" = (select X.LP
>> from SCHED S
>> inner join
>> (select A."Acc ID" as ACCID, A."Serv ID" as SERVID,
>> max(A."Date") as LP
>> from artrans a
>> where A."Code" in (0, 1, 2, 10)
>> group by A."Acc ID", A."Serv ID") X
>> on S."Acc ID" = X.ACCID and S."Serv ID" = X.SERVID
>> where S."Last Performed" <> X.LP
>> and S."Acc ID" = S1."Acc ID"
>> and S."Serv ID" = S1."Serv ID" )

This is simpler, but hopefully what you want to do:

update SCHED S
set "Last Performed" = (select max(A."Date")
from artrans A
where S."Acc ID" = A."Acc ID"
and S."Serv ID" = A."Serv ID"
and A."Code" in (0, 1, 2, 10))
where S."Last Performed" <> (select max(A2."Date")
from artrans A2
where S."Acc ID" = A2."Acc ID"
and S."Serv ID" = A2."Serv ID"
and A2."Code" in (0, 1, 2, 10))

> 1) Is there a way I can avoid creating the View and still use the
> indices.

I expect my answer above to use indexes for artrans."Acc ID" and
artrans."Serv ID" if they are available. An index on artrans."Code"
could also be used if it is selective enough.

> 2) Is it safe to drop and create Views or are they subject to the
> same Table ID restrictions as a real table?

Well, I don't know - I would expect views to be subject to the same
rules as tables, but that is just a hunch.

HTH,
Set