Page 1 of 1

[Solved] Report from parameterized query?

PostPosted: Tue Nov 22, 2011 2:01 pm
by 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 '%'

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 2:14 pm
by 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

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 2:18 pm
by qwertyjjj
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


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

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 2:19 pm
by 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).

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 2:48 pm
by qwertyjjj
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).


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.

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 3:15 pm
by r4zoli
What is the error message?
It is run inside the report as report source, or you tried as independent query?

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 3:33 pm
by qwertyjjj
r4zoli wrote: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.

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 4:48 pm
by 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.

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 4:59 pm
by 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.

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 5:29 pm
by 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.

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 5:40 pm
by qwertyjjj
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.


?
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
Image

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 6:00 pm
by 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) 

Re: report from paramterized query?

PostPosted: Tue Nov 22, 2011 6:27 pm
by 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.

Re: Report from paramterized query?

PostPosted: Tue Nov 22, 2011 6:44 pm
by 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.

Re: Report from parameterized query?

PostPosted: Tue Nov 22, 2011 8:35 pm
by 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

Re: report from paramterized query?

PostPosted: Wed Nov 23, 2011 11:52 am
by qwertyjjj
chrisb wrote: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.
?
Image

Re: Report from parameterized query?

PostPosted: Wed Nov 23, 2011 12:42 pm
by RPG
Hello

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

Romke

Re: Report from parameterized query?

PostPosted: Wed Nov 23, 2011 1:14 pm
by 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.

Re: Report from parameterized query?

PostPosted: Wed Nov 23, 2011 1:46 pm
by qwertyjjj
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.


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.

Re: Report from parameterized query?

PostPosted: Wed Nov 23, 2011 3:12 pm
by r4zoli
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.

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