[Solved] Report from parameterized query?
[Solved] Report from parameterized query?
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 '%'
ANy ideas:
SELECT "Customer", "Type", "Num", "Date", "Total", "RealDate" FROM "Sheet1" WHERE "Customer" LIKE '%' :CustomerName '%'
Last edited by qwertyjjj on Wed Nov 23, 2011 3:38 pm, edited 1 time in total.
Re: report from paramterized query?
Hello
I think you have to change this:
to:
Romke
I think you have to change this:
Code: Select all
WHERE "Customer" LIKE '%' :CustomerName '%'
Code: Select all
WHERE "Customer" LIKE '%' || :CustomerName || '%'
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: report from paramterized query?
error:RPG wrote:Hello
I think you have to change this:
to:Code: Select all
WHERE "Customer" LIKE '%' :CustomerName '%'
RomkeCode: Select all
WHERE "Customer" LIKE '%' || :CustomerName || '%'
LIKE can be used with a string argument only.
Re: report from paramterized query?
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).
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).
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
Re: report from paramterized query?
Both fail.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).
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?
What is the error message?
It is run inside the report as report source, or you tried as independent query?
It is run inside the report as report source, or you tried as independent query?
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
Re: report from paramterized query?
Independent query but it is the same error from the report.r4zoli wrote:What is the error message?
It is run inside the report as report source, or you tried as independent query?
LIKE can be used with a string argument only.
Re: report from paramterized query?
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.
I not have OOo 3.1 installed.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
Re: report from paramterized query?
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.
The placeholder together with the || operator works for me in both versions. You might check it with the attached database.
- Attachments
-
- test-db.odb
- (5.01 KiB) Downloaded 488 times
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
Re: report from paramterized query?
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.
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.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: report from paramterized 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.
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
Re: report from paramterized query?
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) |
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
Re: report from paramterized query?
NO the query is not at fault.
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.
This works perfectly when the source is a genuine a text field.RPG saidCode: Select all
WHERE "Customer" LIKE '%' || :CustomerName || '%'
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.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: Report from paramterized query?
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.
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.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
Re: Report from parameterized query?
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.
The only link missing for the OP is:
http://user.services.openoffice.org/en/ ... 00&t=29404
Romke
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 |
http://user.services.openoffice.org/en/ ... 00&t=29404
Romke
Last edited by RPG on Wed Nov 23, 2011 1:59 pm, edited 1 time in total.
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: report from paramterized query?
I right clicked on the table and selected Edit. It confirms that it is a text field.chrisb wrote:NO the query is not at fault.
This works perfectly when the source is a genuine a text field.RPG saidCode: Select all
WHERE "Customer" LIKE '%' || :CustomerName || '%'
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 parameterized query?
Hello
It seems me to you have to study the link or forget it.
Romke
It seems me to you have to study the link or forget it.
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: Report from parameterized query?
@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.
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.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
Re: Report from parameterized query?
I wasn't saying there was a right or wrong, simply that I don;t understand the limitations.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.
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?
The text driver has such limitations, you can live with it, or use other tools which is good for you.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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin