Subject Re: [ib-support] Please help. View is empty, but the same SELECT works!
Author Svein Erling Tysvaer
Well, beats me - the only thing I could think of was your use of USER in
the last join and possibly moving that to the WHERE clause, but you say
that doesn't influence anything. My only recommendation would be to keep
trimming things down and then start adding once you find a damaging part.
Then ask again highlighting the line of code causing you problems. By the
way, you do not have any entries with p.rate = 0 (shooting in the dark)?
And have you verified that the SELECT actually return all rows?


{create view vDepResources
> (DepID, Descr, TotalIn, TotalOut) as}
>select d.DepID,
> cast(d.Bank||' '||d.AccountNo as varchar(64)) as Descr,
> cast(
> (select sum( from pays p where p.dstid = d.subjid and
>p.depid = d.depid)
> as float) as TotalIn,
> cast(
> (select sum( from pays p where p.srcid = d.subjid and
>p.depid = d.depid)
> as float) as TotalOut
>from DepRights r
> inner join departments d on r.DepID = d.DepID
> inner join employees e on r.SubjID = e.SubjID and e.Login = USER
>BTW: last join has no effect on empty view.