||Re: sql code not working
||Fri, 15 Jun 2018 10:42:00 -0400
Hi Mervyn... OK I am still struggling with this but do understand more.
I used the filter only because the sql statement didn't seem to work.
sql = 'select talname,sum(qty) as cnt, sum(qty*cost) as tcost,
sum(qty*sell) as tsell from taldata group by talname'
But possibly you didn't know exactly what I was doing. And I didn't understamd tje group by name is actually a filter of it's own (I think)
Talname is actually a variable so I changed that in the sql statement to lname which should work!!!
I am not using this in the grid but instead to three entryfields which I linked to the appropriate fields in form.taldata2.
I must be doing something wrong as I get blank data when I have this set up in each entryfield.
At that point before I understood the 'group by' I assumed I needed a filter.
Your first suggestion seems to be the easiest but I don't know what is wrong and why I'm not seeing the data in each entryfield.
Hope this makes sense. Please let me know if there is a resolve!
Mervyn Bick Wrote:
> 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
> or perhaps, if you have the vendor's name in a memory variable named,
> say, talname
> form.q.params['tname'] = talname
> 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+[']