[Solved] Report from parameterized query?

[qwertyjjj]

I am trying to create a parameter query so I can run a report form that, it says it has a SQL error.
ANy ideas:
SELECT "Customer", "Type", "Num", "Date", "Total", "RealDate" FROM "Sheet1" WHERE "Customer" LIKE '%' :CustomerName '%'

[RPG]

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

[qwertyjjj]

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

error:
LIKE can be used with a string argument only.

[rudolfo]

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).

[qwertyjjj]

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).

Both fail.
Of course I can type %Name% into the input box but that's not exactly user friendly for most people. I can just see them asking "Why do I need to put in percentage signs" - it's all computer speak to most people.

[r4zoli]

What is the error message?
It is run inside the report as report source, or you tried as independent query?

[qwertyjjj]

What is the error message?
It is run inside the report as report source, or you tried as independent query?

Independent query but it is the same error from the report.
LIKE can be used with a string argument only.

[r4zoli]

You use OOo 3.1? The Like work me with text and integer field in parameter query in OOo 3.3.
I not have OOo 3.1 installed.

[rudolfo]

It might be something different. I have tested (query only, no reports) it with OOo 3.1.1 which seems to be your version and also with LibO 3.3 (because I thought I heard somewhere that the base query parameter handling was improved in one of the 3.x versions)
The placeholder together with the || operator works for me in both versions. You might check it with the attached database.

[chrisb]

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.

[qwertyjjj]

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.

?
No, the field Customer is a text field.
Besides, the error is about the SQL query, nothing to do with the data.

I have 3.3.0

The prompt appeared and I put in the text AAACompany

[rudolfo]

Unfortunately we are missing here one of the main parts! As chrisb has pointed out things are totally different if your query is based on data coming from a spreadsheet or csv files. The concatenation operator || is not supported in that case. Check out the list SQL Functions for file based database drivers.

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)

[chrisb]

NO the query is not at fault.

RPG said

Code: Select all   Expand viewCollapse view

WHERE "Customer" LIKE '%' || :CustomerName || '%'

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]

I have tried this now with a spreadsheet based Base document. Though the above mentioned site about SQL functions that are supported for text (based) files says that you can use concat(), the use is limited:

SELECT concat("Customer", '%', "Type") AS "concatenated" ... FROM

works, but if the concat is used in the where clause together with the parameter it reports the "LIKE expects a string" error.

But as chrisb pointed out (and as you can learn from my sample file) this is related with the limited database backend, the spreadsheet.

[RPG]

Hello

I think a solution can be:

Make a query based on a searchbox in a form and pass that query to the report.
A searchbox is a normal textcontrol what I use for input data for the macro.

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

The only link missing for the OP is:
viewtopic.php?f=100&t=29404

Romke

[qwertyjjj]

NO the query is not at fault.

RPG said

Code: Select all   Expand viewCollapse view

WHERE "Customer" LIKE '%' || :CustomerName || '%'

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.

I right clicked on the table and selected Edit. It confirms that it is a text field.
?

[RPG]

Hello

It seems me to you have to study the link or forget it.

Romke

[rudolfo]

@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]

@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.

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.

[r4zoli]

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.

The text driver has such limitations, you can live with it, or use other tools which is good for you.