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

Hi Akshat... Thanks so much for your explanation.  Very helpful!!!

Akshat Kapoor Wrote:

> On 14/07/2018 23:45, Charlie wrote:
> > 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...
>
> I know your problem has been solved but in this response I am just
> trying to clear your doubts about the use of params (If any are still left).
>
> More often then not we would like to filter our rowset based on certain
> criteria. There are multiple ways, the three most used are.
> 1.Filter
> 2.canGetRow
> 3.Where clause in sql. We can easily type a query with where clause
>
> sql = "select sum(qty) as qqty from SPDDATAARCH.DBF where namedate='ABC'"
>
> but the problem arises when we want to change the value in the where
> clause. The tough solution is to change the sql statement. The easier
> solution is to use params.
>
> When we use
> sql = "select sum(qty) as qqty from SPDDATAARCH.DBF where namedate=:spname"
> By using : we are telling the database engine that spname is a parameter.
> When we use
> params['spdname'] = trim('spdname')
> we are assigning value to the parameter.
>
> Value to params have to be assigned before the query is set active.
>
> Changing the value afterwards does not cause the rowset already in
> memory to change.
>
> For changing the rowset in memory you have to issue a requery() command.
>
> But this also makes our work as a programmer very easy
> we can easily use
> form.query.params['spdname'] = trim(form.entryfield.value)
> form.query.requery()
>
> And the rowset is updated.
>
> I hope I have been able to explain the use to params correctly.
>
> Regards
> Akshat