The goal is to list the most recent calls from each contact in a particular period. Basically the tables are: contact: id, first, last call: id, c_id, date --- where c_id is the link to the contact The following query should do the job. However it runs without prompts and error message. If I replace the prompts by date strings, then everything works as expected. SELECT T.first, T.name, T.id, last_call FROM ( SELECT DISTINCT contact.first, contact.name, contact.id, last_call FROM contact INNER JOIN (select MAX(call.date) last_call, call.c_id from call GROUP BY call.c_id) call_list ON ( contact.id = call.c_id ) ) T WHERE (last_call between :from_date AND :to_date) ORDER BY T.name
Could you pleas add an attachment with example-database? Could help to confirm a buggy behavior here. Did I understand right: No dialog appears, which asks for from_date and to_date? Please set Status back to "Unconfirmed" when you have attached the information I have asked for.
Created attachment 128295 [details] Sample DB When I created this sample DB to demonstrate the effect, using non personal data, column and variable names, I realized, that I was not able to reproduce the error any more. I did some testing and stumbled across a strange behaviour. The variable names have an effect on the result. If you replace for example the variable "R" with a "T" I get a SQL syntax error. Looks like "T" is somehow a reserved identifier. Unfortunately I lost the original query, which only differs in the identifiers and was tested on a productive database. Hence I can't reproduce the error at the moment, but I will continue to search for it. At the moment I believe it depends on identifier names or on a particular status of the SQL engine. I make the latter assumption, because I had to "Run Query" twice after each change as the first run always produced a runtime error. Thanks
Can't find any error with the attached document. Have changed, as described, the table-alias from "R" to "T" and got the same result. No error. So let us see if you could reproduce the error with another example or could find the reason of the error in your original database. I have set this bug back to "Unconfirmed".
When I enter the following simple query in the sample DB, it produces the same syntax error related to the "T" identifier. SELECT "contact"."name", "contact"."id" , "call"."date" FROM "contact" INNER JOIN "call" ON ( "contact"."id" = "call"."c_id" ) WHERE ("call"."date" < :T) ORDER BY "call"."date" The error message is: SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE If you replace :T by for example :DX it works fine.
(In reply to Tony from comment #4) > When I enter the following simple query in the sample DB, it produces the > same syntax error related to the "T" identifier. > > SELECT "contact"."name", "contact"."id" , "call"."date" FROM "contact" > INNER JOIN "call" ON ( "contact"."id" = "call"."c_id" ) WHERE ("call"."date" > < :T) ORDER BY "call"."date" Seems T (also t, d and D) are reserved for SQL. Don't know where Base could find these characters as reserved for SQL. But this isn't your original bug, or do you think this is the solution for the query you reported, where no prompt appears.
Since I was not able to reproduce the original error anymore, I could not find a relation to the syntax error. I conclude, that I must have passed the query to the backend inadvertently, which does of course not process the ":enter" prompts. Sorry for that! What shall I do with the syntax error? can the bug report be converted? Thank you for your support
You couldn't reproduce the bug you described. So let us set this in Status: Resolved > Worksforme (In reply to Tony from comment #6) > > What shall I do with the syntax error? can the bug report be converted? > SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE This error-message appears every time Base couldn't get a better reaction. Seems to be the last solution to give a hint where to search for something, which could solve problems with the code. I don't know where "T" or "D" where needed for internally. You could report a new bug for this. Could be there is somebody, who could resolve this problem for us. I will set this bug to Resolved > Worksforme.
@Tony : FWIW, I seem to recall that the LO sql parser used in the GUI reserves a number of letter / letter combinations : https://wiki.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures
And see also : https://bz.apache.org/ooo/show_bug.cgi?id=121492#c6
Hi Alex, Thank you for the links. Unfortunately I was not able to find in these or any other sources a hint why a single letter identifier like "T" or "D" should be a reserved word.
(In reply to Tony from comment #10) > Thank you for the links. Unfortunately I was not able to find in these or > any other sources a hint why a single letter identifier like "T" or "D" > should be a reserved word. Hi Tony, the link of comment9 gives the answer: D or d: {D '2016-11-11'} T or t: {t 'HH:MI:SS[.SS]'} Regards Robert
Hi Robert, Those are examples of date specifications, which are valid only inside the specification for the date inside the curly braces. As this is not the case in my example, this looks like a parser error. Thanks Tony