Subject Re: sql where statement not working correctly
From Charlie <tm@tc.com>
Date Sat, 14 Jul 2018 14:15:32 -0400
Newsgroups dbase.getting-started

Hi Mervyn.. Thanks for the help.

I tried this:

sql = "select sum(qty) as qqty from SPDDATAARCH.DBF where namedate=:spname"
                params['spdname'] = trim('spdname')

But I get an error parameter not defined: spdname

So not sure what is going on.  I'm curious, why didn't it work the way I had it?  I've never worked with parameters before so have no clue on this!!

I have spddataarch1.rowset filtered to namedate="spdname"

I tried the same thing with spddatarch2.rowset and it will not allow me to filter.  

Thanks again for your help...



Mervyn Bick Wrote:

> On 2018-07-14 12:03 PM, Charlie wrote:
> > this.SPDDATAARCH2 = new QUERY(this)
> >     with (this.SPDDATAARCH2)
> >        left = 40.0
> >        width = 10.0
> >        height = 1.0
> >        database = form.train1
> >        sql = \"select sum(qty) as qqty from SPDDATAARCH.DBF where namedate=trim(\'spdname\')\"
> >        active = true
> >     endwith
> >
> > for some reason the sql statement doesn\'t seem to work for me.  It works without the where clause, but does not with it in.
> >
> > namedate is a field in the table, spdname is a variable.
> >
> > without the where clause I get a whole number that is not filtered.  If I try to filter it with the where clause I get a decimal point.  The number should actually be 16.
> >
> > Can anyone help?
> >
> > Thanks much!
> >
>
>
> You need to pass the value in as a parameter
>
> this.SPDDATAARCH2 = new QUERY(this)
>     with (this.SPDDATAARCH2)
>        left = 40.0
>        width = 10.0
>        height = 1.0
>        database = form.train1
>        sql = \"select sum(qty) as qqty from SPDDATAARCH.DBF where
> namedate=:spname\"
>        params[\'spdname\'] = trim(\'spdname\')
>        active = true
>     endwith
>
>
> To find a qqty for a different namedate
>
>     form.spddataarch2.params[\'spdname\'] = trim(form.entryfield1.value)
>     form.spddataarch2.requery()
>
> If you are going to need to find values for more than one value of
> spdname an alternative is to create a rowset of sums for all spdnames.
>
> this.SPDDATAARCH2 = new QUERY(this)
>     with (this.SPDDATAARCH2)
>        left = 40.0
>        width = 10.0
>        height = 1.0
>        database = form.train1
>        sql = \"select namedate,sum(qty) as qqty from SPDDATAARCH.DBF
> group by namedate\"
>
>
> This will give you a rowset with a record containing namedate and qqty
> for each value of namedate.
>
> You can then fetch the value for any namedate by using oReef.applyLocate()
>
> Mervyn
>
>
>
>
>
>
>
>
>
>
>        active = true
>     endwith