[Solved] Report from parameterized query?

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Report from parameterized query?

Postby qwertyjjj » Tue Nov 22, 2011 2:01 pm

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

Re: report from paramterized query?

Postby RPG » Tue Nov 22, 2011 2:14 pm

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
LibreOffice 6.1.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2165
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: report from paramterized query?

Postby qwertyjjj » Tue Nov 22, 2011 2:18 pm

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

Re: report from paramterized query?

Postby rudolfo » Tue Nov 22, 2011 2:19 pm

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

Re: report from paramterized query?

Postby qwertyjjj » Tue Nov 22, 2011 2:48 pm

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

Re: report from paramterized query?

Postby r4zoli » Tue Nov 22, 2011 3:15 pm

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

Re: report from paramterized query?

Postby qwertyjjj » Tue Nov 22, 2011 3:33 pm

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

Re: report from paramterized query?

Postby r4zoli » Tue Nov 22, 2011 4:48 pm

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

Re: report from paramterized query?

Postby rudolfo » Tue Nov 22, 2011 4:59 pm

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

Re: report from paramterized query?

Postby chrisb » Tue Nov 22, 2011 5:29 pm

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.6 & LibreOffice 6.1.5.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 182
Joined: Mon Jun 07, 2010 4:16 pm

Re: report from paramterized query?

Postby qwertyjjj » Tue Nov 22, 2011 5:40 pm

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

Re: report from paramterized query?

Postby rudolfo » Tue Nov 22, 2011 6:00 pm

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

Re: report from paramterized query?

Postby chrisb » Tue Nov 22, 2011 6:27 pm

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.
open office 4.1.6 & LibreOffice 6.1.5.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 182
Joined: Mon Jun 07, 2010 4:16 pm

Re: Report from paramterized query?

Postby rudolfo » Tue Nov 22, 2011 6:44 pm

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

Re: Report from parameterized query?

Postby RPG » Tue Nov 22, 2011 8:35 pm

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
Last edited by RPG on Wed Nov 23, 2011 1:59 pm, edited 1 time in total.
LibreOffice 6.1.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2165
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: report from paramterized query?

Postby qwertyjjj » Wed Nov 23, 2011 11:52 am

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

Re: Report from parameterized query?

Postby RPG » Wed Nov 23, 2011 12:42 pm

Hello

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

Romke
LibreOffice 6.1.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2165
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Report from parameterized query?

Postby rudolfo » Wed Nov 23, 2011 1:14 pm

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

Re: Report from parameterized query?

Postby qwertyjjj » Wed Nov 23, 2011 1:46 pm

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

Re: Report from parameterized query?

Postby r4zoli » Wed Nov 23, 2011 3:12 pm

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


Return to Reporting

Who is online

Users browsing this forum: No registered users and 0 guests