Subject Re: need on some Sql help reponse
From Dirk <non@non.com>
Date Wed, 1 Mar 2023 23:05:43 +0100
Newsgroups dbase.getting-started


Mervin

when i test on the this way
this.VRAAGBAK_CROSSEN = new QUERY(this)
    with (this.VRAAGBAK_CROSSEN)
       left = 161.0
       top = 605.0
       width = 100.0
       height = 37.0
       database = form.databank_klanten
       sql = "select eigenvolgnr, crossvolgnr, crossleverancierid,
segmenten, crossbrandnr, crossproductnr, crossartikelnr,
crossingavedatum from artikelcross where crossvolgnr ='0000047841' "
   //    requestLive = false
   //    params["crossref"] = "%"
       active = true
    endwith

    with (this.VRAAGBAK_CROSSEN.rowset)
       with (fields["crossleverancierid"])
          lookupSQL = "select stammdat.idnummer,name from stammdat order
by name "
       endwith
       with (fields["segmenten"])
          lookupSQL = "select segm.segcode,description from segm order
by description"
       endwith
       with (fields["crossbrandnr"])
          lookupSQL = "select brand.brandnr,brand from brand order by brand"
       endwith
       with (fields["crossproductnr"])
          lookupSQL = "select products.prodref,products from products
order by products"
       endwith
       autoEdit = false
       indexName = "IARTIKELREF"
    endwith

    this.VRAAGBAK_ARTIKEL = new QUERY(this)
    with (this.VRAAGBAK_ARTIKEL)
       left = 105.0
       top = 550.0
       width = 93.0
       height = 37.0
       database = form.databank_klanten
       sql = "select volgnr,brandnr,artikelnr from artikels.dbf"
       active = true
    endwith

    with (this.VRAAGBAK_ARTIKEL.rowset)
       with (fields["brandnr"])
          lookupSQL = "select brand.brandnr,brand from brand order by brand"
       endwith
       autoEdit = false
       indexName = "IDENTARTIKEL"
       masterRowset = form.vraagbak_crossen.rowset
       masterFields = "crossvolgnr"
    endwith

i get in the grid

crossvolgnr  crossartikelnr
  0000047841 456602054171
  0000047841 1363203

that's what i need

when using a parameter i only get the first row,
0000047841 456602054171

but testing on this way

q = new query()
  q.database = d
q.sql = 'select eigenvolgnr, crossvolgnr, '
q.sql +='crossartikelnr,crossingavedatum '
q.sql += 'from artikelcross where crossvolgnr = :volgnr'
q.params["volgnr"] = "0000000000"
//q.requestLive = false // do you really need this?
q.active = true

//To show records for a different cosssvolgnr in the grid datalinked to
the query

q.params['volgnr'] = "0000047841"
  q.requery()

do while not q.rowset.endofset
? q.rowset.fields["crossvolgnr"].value + " " +
q.rowset.fields["crossartikelnr"].value

  Q.rowset.next()

enddo

i get
0000047841 456602054171
0000047841 1363203

i think i got my way using a do while enddo

thanks to guide me

Dirk,




Op 1/03/2023 om 21:13 schreef Mervyn Bick:
> On 2023/03/01 19:27, Dirk wrote:
 >> Hello,
 >>
 >> when i test with .... where crossvolgnr ="0000000789" i get only the
>> crossartikelnr i need in the grid,all with the same crossvolgnr whats
>> ok, but when i use a params i only get 1 crossartikelnr, of course
>> when their is only ono, you got one, but if more crossartikelnr i also
>> only get one ?
 >>
 >> sql = 'select eigenvolgnr, crossvolgnr, crossleverancierid, segmenten,
>> crossbrandnr, crossproductnr, crossartikelnr, crossingavedatum from
>> artikelcross where crossvolgnr =""'
 >>      //  params["test"] = "%"
 >>        requestLive = false
 >>
 >> the purpose is: putting a ref in the searchfield and getting all the
>> crossartikelnr in the grid linked on the crossvolgnr
 >
>
> q = new query()
 > q.sql = 'select eigenvolgnr, crossvolgnr, crossleverancierid, segmenten,
 > q.sql +='crossbrandnr,crossproductnr,crossartikelnr,crossingavedatum '
 > q.sql += 'from artikelcross where crossvolgnr = :volgnr '
 > q.params["volgnr"] = "0000000789"
 > q.requestLive = false // do you really need this?
 > q.active = true
 >
> To show records for a different cosssvolgnr in the grid datalinked to
> the query
 >
>   q.params['volgnr'] = "0000000800"
 >   q.requery()
 >
>
>>
 >> how it's working:
 >>
 >> a main table is populated with an artikelnr
 >>
 >> sql = "select volgnr,brandnr,artikelnr from artikels.dbf"
 >>
 >> by saving the a secondtable artikel cross get the same ref from the
>> main table Artikels volgnr is fK for crossvolgnr, afterwards i can
>> populate the secondtables with more reference
 >>
 >> now i want to filter the artiklecross table with only the
>> crossartikelnnr with the same crossvolgnr
 >>
 >> i should pleased getting a some info about,
 >>
 >> thank you
 >> dirk,
 >
> I assume you want to display the records from artikels.dbf and
> atrikelcross.dbf in two separate grids.  If you select a record in the
> artikels grid you want to show the relevant records from
> artikelcross.dbf in the second grid.
 >
>
> q1 = new query()
 > q1.sql = 'select volgnr,brandnr,artikelnr from artikels.dbf1
 > q1.active = true
 >
>
> q = new query()
 > q.sql = 'select eigenvolgnr, crossvolgnr, crossleverancierid, segmenten,
 > q.sql +='crossbrandnr,crossproductnr,crossartikelnr,crossingavedatum '
 > q.sql += 'from artikelcross where crossartikelnr = :artikelnr '
 > // parameter name is name of field in master rowset to be matched
 > q.masterSource = q1.rowset
 > q.active = true
 >
> Mervyn.
 >
>