Subject Re: sql code
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 13 Jun 2018 21:48:56 +0200
Newsgroups dbase.getting-started

On 2018-06-13 8:43 PM, Charlie wrote:
> Hi... what is wrong here?  I am trying to filter this sql statement with where using the variable talname, but I can't seem to get this to work for some reason.  I've gotten so many different error messages my head is spinning.
>
> Really appreciate any help!
>
> sql = "select lname,sum(qty) as cnt, sum(qty*cost) as tcost, sum(qty*sell) as tsell from taldata where lname ="+talname+" group by lname"
>

In a different thread I've suggested a different approach.  You don't
really need to filter the totals rowset each time you want the values
for a specific vendor.  If you create the rowset with the totals for all
vndors you only need to point to the correct record for the vendor you want.

Still, if you want to do it this way then you should use a parameter
driven query.

form.q = new query()
form.q.sql =  "select lname,sum(qty) as cnt, sum(qty*cost) as tcost,
sum(qty*sell) as tsell from taldata where lname = :tname group by lname"
form.q.params['tname']= ""
form.q.active = true.


To get the values for a specific vendor

form.q.params['tname'] = form.talname1.rowset.fields['talname'].value
form.q.requery()

or perhaps, if you have the vendor's name in a memory variable named,
say, talname

form.q.params['tname'] = talname
form.q.requery()

dBASE is not normally case sensitive but here the case of the parameter
in the select statement must match the case of the params entry.

The third alternative is to use the rowset's filter property.

form.q.rowset.filter = [lname = ']+talname+[']

Mervyn.