Subject Re: query result
From Mustansir Ghor <mustan31@hotmail.com>
Date Sat, 18 Jul 2020 13:38:35 -0400
Newsgroups dbase.getting-started

Dear Mervyn

Thanks. I did not occur to me that since no summing is required, distinct is better option.

Mustansir

Mervyn Bick Wrote:

> On 2020-07-18 16:32, Mustansir Ghor wrote:
 > > Dear All
 > >
> > I am using following two queries in 2 grids in a similar fashion of seeing invoice no and date in grid 1 and and items details of a particular (a row of grid 1)  invoice in grid2.
 > >
> >   this.QTEMPSAL = new QUERY(this)
 > >     with (this.QTEMPSAL)
 > >        left = 21.0
 > >        database = form.dlocal
 > >        sql = "select sno,sdate from sales where sdate between :mdate1 and
 > >                 :mdate2 group by sno,sdate"
 > >        params["mdate1"] = {  /  /  }
 > >        params["mdate2"] = {  /  /  }
 > >        active = true
 > >     endwith
 > >
> >    
> >     this.QSALES = new QUERY(this)
 > >     with (this.QSALES)
 > >        left = 15.0
 > >        top = 1.0
 > >        database = form.dnov
 > >        sql = "select * from sales where sno=:sno"
 > >        masterSource = form.qtempsal.rowset
 > >        active = true
 > >     endwith
 > >
> > In both grids initial results are fine. Even when I scroll down in grid one, the grid2 displays correctly for each row of grid 1. However after last row when i scroll up , the rows of grid 1 displays last row content in all. And grid 2 does not display based on grid 1
 > >
> > Since duplication of data were only 2 columns, I decided to keep records in one file and avoid using join.
 > >
> > Can anybody point out were the problem lies.
 >
> I can't replicate the problem here.
 >
> I don't know why you found it necessary to use two different database
> objects.  Are the the sales tables different in each database?
 >
>
> For the QTEMPSAL query try the following.
 >
>      sql = "select distinct sno,sdate from sales where sdate between
> :mdate1 and :mdate2"
 >
> To make sure you only see details for the selected date in grid2 you
> will need to change the sql property in the QSALES query
 >
>     sql = "select * from sales where sno=:sno and sdate=:sdate"
 >
>
> If you want to see all the sales for a given customer use the following
> in QTEMPSAL
 >
> sql = "select distinct sno from sales where sdate between :mdate1 and
> :mdate2
 >
> The fields in the WHERE clause do not need to be in the rowset.
 >
> For QSALES add parameters to the query and use
 >
>     sql = "select * from sales where sno=:sno and sdate between :mdate1
> and :mdate2"
 >
> Mervyn.
 >
>
>
>