Parameter with % value - showing and records with null values [message #1219585] |
Wed, 04 December 2013 18:38 |
Dejan Vujanic Messages: 67 Registered: October 2011 |
Member |
|
|
Hi,
I suppose this is simple question for many.
Lets say I have this table:
SERVICE:
A
B
C
null
null
I made a SQL data set (not by scripting) query:
SELECT SERVICE FROM TABLE WHERE SERVICE LIKE ?
I created and parameter service which is not required (to be able to select null value)
So If I enter A it shows A which is ok.
If I enter % it will show A,B,C which is not ok, it should show and two others.
I can not put in where clause "service like % or service is null" because if I enter A it will show 3 records in that case which is not good.
I want when "%" is entered to show all records but if specific value is entered to show only that value.
Please can someone give me instructions how to solve this in BIRT?
Thank you
[Updated on: Thu, 05 December 2013 12:37] Report message to a moderator
|
|
|
|
Re: Null Parameter - showing and records with null values [message #1219770 is a reply to message #1219691] |
Thu, 05 December 2013 17:30 |
donino donino Messages: 183 Registered: July 2011 |
Senior Member |
|
|
Hi,
there is a smart and incredibly useful tip to do this, by using 'OR' sql operator! Change your query like this:
SELECT SERVICE
FROM MYTABLE
WHERE (?='%' OR SERVICE LIKE ?)
We make use of the symbol '?' twice , so we need to declare two dataset parameters, both linked to the same report parameter 'service'. When the first condition ?='%' is true, the database won't evaluate the second condition therefore even null values will be returned
[Updated on: Thu, 05 December 2013 17:37] Report message to a moderator
|
|
|
Re: Null Parameter - showing and records with null values [message #1219837 is a reply to message #1219770] |
Fri, 06 December 2013 08:47 |
Dejan Vujanic Messages: 67 Registered: October 2011 |
Member |
|
|
Hi,
thank you very much for information but it seems that this does not work completely.
If I enter as parameter value % it works OK.
But if enter some specific value, for example "Internet" I am getting error.
If I do the regular expression with only one "?" and enter "Internet" I do not have those errors.
Below are errors which I am getting when using your query.
The following items have errors:
Data (id = 367):
- Cannot execute the statement.
SQL statement does not return a ResultSet object.
SQL error #1: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.53.70
Cannot execute the statement.SQL statement does not return a ResultSet object.SQL error #1: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.53.70 ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Cannot execute the statement.SQL statement does not return a ResultSet object.SQL error #1: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.53.70 at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1121) at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1085) at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:88) at org.eclipse.birt.report.engine.executor.DataItemExecutor.execute(DataItemExecutor.java:75) at
[Updated on: Fri, 06 December 2013 08:47] Report message to a moderator
|
|
|
Re: Null Parameter - showing and records with null values [message #1219873 is a reply to message #1219837] |
Fri, 06 December 2013 12:27 |
donino donino Messages: 183 Registered: July 2011 |
Senior Member |
|
|
This is a standard SQL query, there is no reason why it would not work with DB2.
As you describe the problem it seems the second '?' is not mapped to the report parameter so it returns null. Double-check you have mapped it as expected in dataset parameters: oftenly there are confusions between dataset parameters and report parameters.
Any chance you can post the .rptdesign, after removing credential / confidential informations?
[Updated on: Fri, 06 December 2013 12:31] Report message to a moderator
|
|
|
|
Re: Null Parameter - showing and records with null values [message #1219987 is a reply to message #1219978] |
Sun, 08 December 2013 12:24 |
donino donino Messages: 183 Registered: July 2011 |
Senior Member |
|
|
Hi Dejan,
It seems your setup is fine, at first sight i can't find out why it does not work. Though you should set a default value in the 'service' report parameter: it would allow to preview data within the dataset definition, and therefore might help to solve the problem.
A key point would be to know if this very query runs correctly when we hard-code parameter values: for example duplicate the dataset and replace all '?' with possible values, in particular replace the two last '?' with 'Internet', can we preview some data like this?
If so, the problem might be a birt bug. It seems the report is designed with birt 2.3.2, i never used this version may be it was not possible to use twice a same report parameter in a query?
In this case, i don't see any other easy way to apply that filter. A possible workaround would be to use a DB2 stored procedure instead of a regular SQL query.
You could also apply this 'service' filter in a BIRT expression, but this would change significantly the report design and might be less efficient.
[Updated on: Sun, 08 December 2013 12:32] Report message to a moderator
|
|
|
Powered by
FUDForum. Page generated in 0.02713 seconds