WHERE "Customer" LIKE '%' :CustomerName '%'
WHERE "Customer" LIKE '%' || :CustomerName || '%'
RPG wrote:Hello
I think you have to change this:
- Code: Select all Expand viewCollapse view
WHERE "Customer" LIKE '%' :CustomerName '%'
to:
- Code: Select all Expand viewCollapse view
WHERE "Customer" LIKE '%' || :CustomerName || '%'
Romke
rudolfo wrote:I would say you need
LIKE concat('%', :CustomerName, '%')
or
LIKE ('%' || :CustomerName || '%')
But using parameter placeholders inside functions is an area where the Base parser comes to its limits.
And because you want to use parameters you can't use the "Direct SQL" mode and bypass the Base (frontend) parser (this is the one that is responsible for replacing the placeholders with user input data).
r4zoli wrote:What is the error message?
It is run inside the report as report source, or you tried as independent query?
chrisb wrote:qwertyjjj has failed to disclose the origin of the data.
An Excel spreadsheet.
The report is based on a table linked to a spreadsheet.
The field 'Customer' is a number and not a text field.
Edit: Now I see that you mentioned that the prompt appeared. That means you have at least saved your query. If the Base frontend parser finds something strange it complains when you save the query. If you can successfully save it and the error comes after the prompt when executing the query it is a problem with the database backend (you see what this is in the status line on the main Base window) |
Edit: The query I mean is not only the query but also the filter part. Both you do need in the report. I think that works enough. I think also all macros are there |
chrisb wrote:NO the query is not at fault.
This works perfectly when the source is a genuine a text field.
It does not work when applied to the field 'Customer' contained in the table 'Sheet1' on which your report is based.
The problem therefore is not general but specific to your data and/or the way it is derived.
rudolfo wrote:@qwertyjjj
our aim is not to say that you are wrong. This is not about right or wrong here. It is simply that you have hit the limits of a spreadsheet as database backend. You can either change your database backend (might be not possible because for me it sounds like you and your co-workers/customers,.. are using spreadsheets to exchange data) or you follow the work-arounds for the limitation that Romke pointed out ... or you take more time and try to play around with the spreadsheet to find solutions. After all the last way will surely be the one where you learn the most.
qwertyjjj wrote:I wasn't saying there was a right or wrong, simply that I don;t understand the limitations.
It would seem a fairly obvious thing for the original programmers of the driver to fix. After all, the spreadsheet has a text datatype, the ooo base lists it as a text datatype but I don;t understand why the driver causes an issue.
The documentation says CONCAT(str1,str2,...)
I also tried WHERE "Customer" LIKE CONCAT('%', :CustomerName, '%') but seems to have the WHERE clause limitations as above.
Users browsing this forum: No registered users and 1 guest