[Solved] Report from parameterized query?

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

[Solved] Report from parameterized query?

Post 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 '%'
Last edited by qwertyjjj on Wed Nov 23, 2011 3:38 pm, edited 1 time in total.
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: report from paramterized query?

Post by RPG »

Hello

I think you have to change this:

Code: Select all

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

Code: Select all

WHERE "Customer" LIKE '%' || :CustomerName ||  '%'
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: report from paramterized query?

Post by qwertyjjj »

RPG wrote:Hello

I think you have to change this:

Code: Select all

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

Code: Select all

WHERE "Customer" LIKE '%' || :CustomerName ||  '%'
Romke
error:
LIKE can be used with a string argument only.
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: report from paramterized query?

Post 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).
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.
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: report from paramterized query?

Post 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.
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: report from paramterized query?

Post by r4zoli »

What is the error message?
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
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: report from paramterized query?

Post 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.
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: report from paramterized query?

Post 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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: report from paramterized query?

Post 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.
Attachments
test-db.odb
(5.01 KiB) Downloaded 486 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.
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: report from paramterized query?

Post 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.
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
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: report from paramterized query?

Post 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
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: report from paramterized query?

Post 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) 
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.
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: report from paramterized query?

Post by chrisb »

NO the query is not at fault.
RPG said

Code: Select all

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.
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
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Report from paramterized query?

Post 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.
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.
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Report from parameterized query?

Post 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:
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
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: report from paramterized query?

Post by qwertyjjj »

chrisb wrote:NO the query is not at fault.
RPG said

Code: Select all

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
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Report from parameterized query?

Post by RPG »

Hello

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

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Report from parameterized query?

Post 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.
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.
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: Report from parameterized query?

Post 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.
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Report from parameterized query?

Post 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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Post Reply