Subject Re: sql code not working
From Charlie <tm@tc.com>
Date Fri, 15 Jun 2018 10:42:00 -0400
Newsgroups dbase.getting-started

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!

Thanks!!

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
> 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.
>