How to write large AdvSQL reports with more than 50000 characters?
Dear Report Builder gurus
I have developed a complex report within Report Builder with AdvSQL (for DB2).
Unfortunately Report Builder complains now, when I try to enhance the query, that the SQL query length is exceeding 50000 characters.
DB2 itself allows much longer SQL queries. Why is there a limitation in Report Builder for 50000 characters and is there a way to extend this limit. Or does this need a support case to get a defect against RB?
I‘m blocked now by this limit to implement requested features to the report.
Thanks for any hint
Guido
2 answers
Hi Guido, I am not aware of such restriction. Would be better if you open a case against IBM support.
As a workaround, try to create a view in the database, which could simplify the query and create the Advanced SQL query against the view.
Comments
- I'm in contact with support
- idea with own views is nice, but my select statements have filter parameters in it, so this is not possible with a view, as much as I understand views. Or is there a way to have something like a parameterized view or better call a sub report with filter parameters and use the result set in the parent report select to e.g. join with a db table.
- can we use stored procedure inside RB reports?
The view would select the columns that you would want to filter on. And you write a select against the view and add filters on those columns.
CREATE VIEW V AS SELECT A, B FROM T
SELECT B FROM V WHERE A = 'Bla Bla'
In another post about the not supported CTE functionality you mention, there is a open source syntax checker used.
Could it be possible, this checker is limiting the code to 50000 char?
If yes, whats the name of this component? Can it be customized somewhere in a jar file or so? Fully unsupported of course.