Bug with () and date fields ?

rbuser

With the Query designer in RB 7.02 if you put parens around a date field and
check ShowAll the following error occurs when you hit "OK"

Invalid SQL Statement
ORA-00936: missing expression


I'm trying to get an 'OR' statement into the designer. The end result would
look something like:

SELECT TABLE WHERE ((STARTDATE IS NULL) OR (STARTDATE <= "12/31/2003"))

At times I don't need the date condition so this is an autosearch field that
I set the Showall by code. Works great for other fields, just not a date
field. It works fine too without the parens but then I can't get the
compound where clause.


Thanks! Jon Gray

nicocizik

Jon,

I tried to recreate the issue you are having on an Oracle 9i database and
DOA. I found that the error is caused by the date format you are entering
in as the value for the search criteria. RBuilder relies on Delphi, which
in turn relies on Windows to define which date format it can use at runtime.
Go to your 'Regional Settings' located in your Windows Control Panel and
check what date format you have set for your local computer. If you use
this date format in your search criteria, the problem should be solved. As
an end-user, you will have to enter the same date format that the local
machine is configured for in order to pass our query wizard's criteria type
validation.

--

rbuser

I found that to be true as well.

However, by checking ShowAll and providing a valid date produces the error
if you have more than one condition inside the parens.Similarly, putting
parens around a single field and checking showAll also fails.

With ShowAll checked - no date should be required, correct?

Thanks!

rbuser

Hi Nico,

the problem isn't date format depending (nor database depending). The operator 'OR' should be removed when the expression will be removed by 'Show All'.

Cheers,
Chris Ueberall;

nicocizik

Jon,

You will need to make sure that the search criteria with the autosearch
and ShowAll components selected is shown first in the 'where' clause. This
way the 'or' statement is omitted when ShowAll is selected. In the DADE
Query Designer, the search criteria will look something like this...

begin
Table.StartDate <= 2002/03/29 AutoSearch [x] Mandatory [] ShowAll [x]
OR
Table.StartDate Blank
end

The corresponding SQL should look like this (if the 'ShowAll' property is
selected)

WHERE ((Table.StartDate IS NULL))

- if you unselect the 'ShowAll' property, the SQL will look very similar to
the SQL you gave in the first message of this thread.

--

rbuser

Hi Nico,


that did the trick, thankyou.

Cheers,
Chris Ueberall;