Parameter query with date input

Creating tables and queries

Parameter query with date input

Postby ponley » Thu Jan 24, 2013 8:54 pm

I have a parameter query that accepts several inputs one of which is a date range, the user can input start and end dates. I would like to detect if the user has left these parameters blank and substitute defaults in that case. However when I try to do this the user parameter popup seems to handle the input differently.

For simplicity I am only working with the code for handling the date at the moment and below is what I am trying to use. If the user leaves both :After_Date and :Before_Date blank the query returns all records in the default range. If the user inputs a date for either or both variables the code will work as long as the user inputs in the format YYYY-MM-DD but MM/DD/YY causes an error. If I remove the CASE statement, and thus the ability to supply a default value, forcing the user to enter a date in each parameter then any OpenOffice date syntax will work.

Aparently the CASE statement is insulating the parameter popup from seeing that the parameter is destined for use as date type and changing the way it is handled. If I press the TAB key after entering the date string in the popup WITHOUT the CASE statement the input expanded to #MM/DD/YYYY# right in the popup text box regardless of the format the user entered the date (YY-MM-DD or DD/MM/YY or any other format all return #MM/DD/YYYY#). However WITH the CASE statement the user input is simply single quoted exactly as input. I realize that date format is very country dependent but that is why I anticipate many errors if I force the use of YYYY-MM-DD in this country.

The popup seems to know how to look at the SQL code and decide how to format the user input accordingly but the CASE is confusing this mechanism. Is there a way for ME to tell the popup to handle the user input as a date so that I can use the CASE statement?



This works only with date input in the format YYYY-MM-DD or with no input by using a coded default

Code: Select all   Expand viewCollapse view



SELECT "Company", "City", "Position", "Date"
FROM "Search_Record"
WHERE ( 

"Date" BETWEEN CASE WHEN :After_Date  = '' THEN '1999-01-01'  ELSE :After_Date END AND CASE WHEN :Before_Date  = '' THEN '2999-01-01' ELSE :Before_Date END

)


This works with date input any format such as MM/DD/YYYY or YYYY-MM-DD or YYYY/MM/DD (and many more I'm sure) but obviously with no default REQUIRES an input.

Code: Select all   Expand viewCollapse view



SELECT "Company", "City", "Position", "Date"
FROM "Search_Record"
WHERE ( 

"Date" BETWEEN :After_Date AND :Before_Date

)
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Debian package 1:3.2.1-11+squeeze4
ponley
 
Posts: 3
Joined: Tue Jan 03, 2012 4:13 am

Re: Parameter query with date input

Postby DACM » Thu Jan 24, 2013 9:17 pm

The Base Query Parser may well have some holes in its ability to parse nested SQL properly. I haven't tested your query, but I would try forcing the issue:
1. CAST(:After_Date AS Date)
2. CONVERT(:After_Date,Date)
3. COALESCE(:After_Date,'1999-01-01')
...
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Parameter query with date input

Postby ponley » Thu Jan 24, 2013 10:28 pm

I had tried the CAST statement with my CASE but gotten an error so I just now tried it again with the simple code that does work and I still get the following error if I input the date as MM/DD/YY. If I input the date as YYYY-MM-DD then the CAST works but is unneccessary as inputting the date in that format works with the CASE anyway. Apparently CAST has the same limitations as far as acceptable input for dates where as the popup is doing some translation. Is there something I could do in the SQL, Some kind of useless command that would fool the popup into giving me the date type input I need before I call the CASE statement that would not break with an empty variable?

It seems all three options seem to want the input in the YYY-MM-DD format whereas the Popup seems to have an ability to deal with the many different date formats in the world.


Code: Select all   Expand viewCollapse view
The data content could not be loaded.


Code: Select all   Expand viewCollapse view
SQL Status: 37000
Error code: -16

Wrong data type: java.lang.IllegalArgumentException

Code: Select all   Expand viewCollapse view
The SQL command leading to this error is:

SELECT "Company", "City", "Position", "Date", "Contact", "How_Submitted", "Outcome", "Interview", "Corvel_Lead" FROM "Search_Record" WHERE ( "Date" BETWEEN CASE WHEN CAST( :After_Date AS DATE ) = '' THEN '1999-01-01' ELSE CAST( :After_Date AS DATE ) END AND CASE WHEN CAST( :Before_Date AS DATE ) = '' THEN '2999-01-01' ELSE CAST( :Before_Date AS DATE ) END )
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Debian package 1:3.2.1-11+squeeze4
ponley
 
Posts: 3
Joined: Tue Jan 03, 2012 4:13 am

Re: Parameter query with date input

Postby DACM » Fri Jan 25, 2013 3:15 am

Well I think this is just a shortcoming of the Base Query Parser. To accept anything but an ISO Date format (YYYY-MM-DD) would mean the parser would have to look at the *Office localization in order to determine what you mean by 10/11/12...and I'm certain that the coding of the Base Query Parser is lacking there. I just thought we could trick it above.

Would you be happy with a Form instead of a query popup? I believe Date Controls on Forms do reference the localization, so we could probably get you a Form for the job that supports variable user input for dates to include a mouse-driven pop-up calendar. We'll have to see if the default works as expected in a Date Control.

See also: Date as input variable to a query?
...
Attachments
Date_Input_Query_Forms.odb
Example parameter query replacement Forms
(24.55 KiB) Downloaded 210 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Parameter query with date input

Postby Villeroy » Fri Jan 25, 2013 9:17 pm

SQL always uses ISO dates (what else?)

Base uses the global application locale for anything you enter into the GUI. Each locale includes the ISO format as well.
SELECT "D" FROM "Table" WHERE "D"<:PD
accepts
12/31/10 with US locale
31.12.10 with German locale
31/12/10 with both, UK locale and German locale
2010/12/31 with any of the 150+ locale settings.

One problem with the parameter substitution GUI is that it does not know any NULL values. A missing parameter is treated as empty string. This is different when substituting the same parameters by a parent form where missing values in the main form match with :MyParam IS NULL
Another problem with that dialog is that we do not know how entered strings are converted to other data types (numbers, dates, times etc). CAST(:Input AS DATE) should evaluate the :Input according to the required date type before calling the sql cast function. In fact it seems to pass the literal :Input string directly to the sql cast function which must not make any assumptions about what 10/11/12 means. It needs to be 2010-11-12 or 2012-11-10 or 2012-10-11 depending on which date you really mean.

This is no problem when comparing the input with a date field as in WHERE :Input = "DateField". This adjusts the input string to the type of the compared field type.

Edit:
Same issue with decimals:
WHERE CAST("Value" AS FLOAT) > :Input
WHERE CAST(:Input AS FLOAT) > "Value"
always requires English input with point as decimal separator
WHERE :Input > "Value"
works with comma as decial separator under a German locale.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26117
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Parameter query with date input

Postby DACM » Sat Jan 26, 2013 7:34 am

That's helpful to identify the conditions (locale) and format (ISO) acceptable to the SQL engine.

But ponley has outlined the anomaly with the Base Query Parser (GUI parameter processing) quite clearly. I may have confused the issue by suggesting that we try some alternative SQL functions in order to trick the Base GUI into handling parameters properly and consistently. But those tricks were an attempt to work-around the identified inconsistencies in the GUI.

In this case, this issue is not directly related to SQL, so it doesn't matter what the SQL engine will or will not accept as a date input. This is about the pre-processing of parameters by the GUI. It would certainly be nice if the GUI consistently handled both locale-specific and ISO date formats, just like the underlying SQL engine. And in fact, as ponley discovered, the GUI does handle both formats in some cases. In other cases, the GUI only handles ISO formatted dates properly, while throwing/causing an error with a locale-specific format. The difference is related to the specific SQL-function in play, when processing parameters. The erroneous processing can be seen by pressing tab (or upon toggling mouse focus) in the parameter query popup dialog, even before sending the final query to the SQL engine.

I've concluded that it's a dead-end to pursue default values unless ponley is willing to input ISO format dates exclusively for parameters (popup). But due to the inconsistencies in the GUI, there may be other SQL that avails a solution. Otherwise, it probably comes down to Forms for the flexibility to input both locale-specific, or ISO format, or select the date from a drop-down calendar -- when employing default dates.

So the question is, does anybody see another angle or way to regain locale-specific date input when incorporating default dates?
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Parameter query with date input

Postby Villeroy » Sat Jan 26, 2013 3:04 pm

ponley wrote:For simplicity I am only working with the code for handling the date at the moment and below is what I am trying to use. If the user leaves both :After_Date and :Before_Date blank the query returns all records in the default range. If the user inputs a date for either or both variables the code will work as long as the user inputs in the format YYYY-MM-DD but MM/DD/YY causes an error. If I remove the CASE statement, and thus the ability to supply a default value, forcing the user to enter a date in each parameter then any OpenOffice date syntax will work.


This is how I use to do it: download/file.php?id=14591 and it works with the given input variants of a date control.
Setting up the param query for the data table in the subform is straight forward.
For the filtering main form you need a table of filter criteria where each set of criteria has one distinct row. Start with a new blank row with a row ID, say 1, which takes the criteria for one particular form (other criteria rows may be used for other forms).
The main form is bound to the distinct filter row: SELECT * FROM "F" WHERE ( "F"."FID" = 1 )
The main form has all features disabled except for "edit data" (no navigation bar, no insert, no delete).
The subform is bound to the filtering main form mapping its parameter names to the parent form's field names.
Filter Field<=> Query Parameter
D1 <=> paramD1 (from date)
D2 <=> paramD2 (until date)
INT1 <=> paramP (person ID)
INT2 <=> paramC (category ID)

Because the criteria are stored in a table permanently, the form will always reload with the same criteria and you can create all sorts of reporting and/or calculating queries based on the same filter criteria as entered in the filter form.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26117
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest