Adding to Nabil's reply, you may also want to move the join condition before the criteria.

create sql view vEntry1 as ;
SELECT e.entrydate, e.entrytype, e.paidto, ;
e.total, o.descript as name, e.datecreate, ;
e.entryref, e.entryid, catlist(e.entryid) as categs, ;
DTOS(e.entrydate) as dtosdate, DTOS(e.datecreate) as dtoscreate;
FROM entry e, owners o ;
--> WHERE E.OwnerID = O.OwnerId ;
--> and E.DateCreate = date() ;
and E.Total > 0

-Michael M. Emmons

>I have two views, one simple and the second more complex. If I ask vEntry to just show the records for today I get different records from vEntry1. I can't work out why!
>
>CREATE SQL VIEW vEntry as ;
> SELECT e.entrydate, e.entrytype, e.paidto, ;
> e.total, o.descript as name, e.datecreate, ;
> e.entryref, e.entryid, catlist(e.entryid) as categs,;
> DTOS(e.entrydate) as dtosdate, DTOS(e.datecreate) as dtoscreate;
> FROM entry e, owners o, entrydet d;
> WHERE o.ownerid = e.ownerid ;
> AND E.entryid = d.entryid;
> AND o.ownerid >= ?cLowerOwnerid and o.ownerid <= ?cUpperOwnerId ;
> and ((d.propertyid >= ?cLowerPropertyId and d.propertyid <= ?cLowerPropertyId) or empty(d.propertyid) ) ;
> having dtosdate >= ?cLowerEntry and dtosdate <= ?cUpperEntry ;
> and dtoscreate >= ?cLowerCreate and dtoscreate <= ?cUpperCreate
>
>create sql view vEntry1 as ;
> SELECT e.entrydate, e.entrytype, e.paidto, ;
> e.total, o.descript as name, e.datecreate, ;
> e.entryref, e.entryid, catlist(e.entryid) as categs, ;
> DTOS(e.entrydate) as dtosdate, DTOS(e.datecreate) as dtoscreate;
> FROM entry e, owners o ;
> where e.datecreate = date() ;
> and o.ownerid = e.ownerid ;
> and e.total > 0
>
>cLowerEntry = "10000101", cUpperEntry = "30000101"
>cLowerCreate and cUpperCreate = "19980628"
>cLowerOwnerId and cLowerPropertyId = "AAA0000000"
>cUpperOwnerId and cUpperPropertyId = "ZZZ0000000"
>
>It's got me stumped, I'd appreciate any help...
>
>Thanks - Sarah