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