Subject |
Re: Using Field Named DATE in Where clause |
From |
Mervyn Bick <invalid@invalid.invalid> |
Date |
Wed, 14 Feb 2024 14:50:41 +0200 |
Newsgroups |
dbase.getting-started |
On 2024/02/14 14:08, Craig wrote:
> In terms of the fieldName being a reserved word. I am trying to get new reports done in the current version of dBase as the original database is from a very early version which only works on Windows XP so the choice was not mine.
>
> Here is the Where clause that I am trying to use:
> where DATE>"(currentDate()-30)" order by DUEDATE, CUSTOMER
>
> This is what I saw online when I tried to look up how to do it
Although it is not a good idea to use reserved words as fieldnames it
can be dealt with. Wrap the fieldname in single quotes and qualify it
by using the tablename or a table correlation name (alias).
select * from whatever where whatever.'DATE' > currentDate() ...
or
select * from whatever w where w.'DATE' > currentDate() ...
The problem is that current_Date() (or curdate() ) is "proper" SQL and
is not available in localSQL.
The first thing to do is to use a parameter driven query in your report.
Something like
this.WHATEVER1 = new QUERY(this)
with (this.WHATEVER1)
left = 14.0
width = 1.0
sql = "select * from whatever w where "
sql += "w.'date' > :ddate - 30 "
sql += "order by duedate,customer"
requestLive = false
params['ddate'] = date() //Today
active = true
endwith
The sql property is built up here to avoid line-wrap.
Unfortunately, when the report designer streams out the .rep file,
date() will be evaluated and will be streamed out as today's date i.e
today's date is effectively hard-coded in the report.
To get round this create a little "launch" form for the report with a
pushbutton to actually run the report. Create an instance of the report
in memory. Once the instance is available you can change ANY of its
properties before rendering it.
In the form's onOpen event handler
function form_onOpen
set procedure to whatever.rep
form.rep = new whateverReport() //use classname in report
//this creates an instance of the report in memory
return
In the pushbutton's onClick event handler
function PUSHBUTTON1_onClick
form.rep.WHATEVER1.params['ddate'] = date()
//Force the value in the report to today's date
form.rep.WHATEVER1.requery()
//Fetch the required records
form.rep.render() //run the report
return
Mervyn.
|
|