Subject Re: query result
From Mervyn Bick <invalid@invalid.invalid>
Date Sat, 18 Jul 2020 17:34:13 +0200
Newsgroups dbase.getting-started

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.