Eclipse Community Forums: BIRT » "dynamic table selection"

Home » Archived » BIRT » "dynamic table selection"

"dynamic table selection" [message #88921]

Tue, 08 November 2005 16:07

Eclipse User

Hi,

I have the following problem. Im making reports from data that comes from
the Baan ERP package. Within baan you can define isolated environments
called companies. Each company has the same set of tables. The only
difference is the last 3 characters of the table name.

So you have a table "ttcibd001" This tabel used for company 100 can be
found in the database as "ttcibd001100". For company 200 this table will
be called "ttcibd001200".

In order to be able to write 1 report that can be used for all companies,
i need to dynamically (besed on a parameter) add the company number to the
select statement. I tried using parameters in the data set and the query
would look like
select *
from ttcibd001?

This is not working because the "?" is considered part of the table name
and not replaced by the variable. If i add a space between ttcibd001 and
the ? i get of course an SQL syntax error

How can i solve this. It should be fairly straight forward as there are
2500 tables per company.

Re: "dynamic table selection" [message #88955 is a reply to message #88921]

Tue, 08 November 2005 16:57

Eclipse User

Hello Gunter;
You will not be able to use parameter markers for anything other than
values in your WHERE clause or parameters to a stored proc. This is due to
how JDBC works - not a limitation of BIRT. You may want to consider stored
procedures that take the company suffix as a parameter and build your
select statement dynamically. BIRT seems to deal with stored proc results
sets quite well (at least for me using DB2 UDB v8+).

Hope this helps,

Pete H

gunter rombauts wrote:

> Hi,

> I have the following problem. Im making reports from data that comes from
> the Baan ERP package. Within baan you can define isolated environments
> called companies. Each company has the same set of tables. The only
> difference is the last 3 characters of the table name.

> So you have a table "ttcibd001" This tabel used for company 100 can be
> found in the database as "ttcibd001100". For company 200 this table will
> be called "ttcibd001200".

> In order to be able to write 1 report that can be used for all companies,
> i need to dynamically (besed on a parameter) add the company number to the
> select statement. I tried using parameters in the data set and the query
> would look like
> select *
> from ttcibd001?

> This is not working because the "?" is considered part of the table name
> and not replaced by the variable. If i add a space between ttcibd001 and
> the ? i get of course an SQL syntax error

> How can i solve this. It should be fairly straight forward as there are
> 2500 tables per company.

Re: "dynamic table selection" [message #89188 is a reply to message #88955]

Wed, 09 November 2005 08:06

Eclipse User

Hi,

I am not familiar with stored procedures. How would this work for me?
Wat is required to do this for "select * from ttcibd001<company>"
Would it be possible to send me an example?

Thanks

Re: "dynamic table selection" [message #89282 is a reply to message #89188]

Wed, 09 November 2005 11:03

Eclipse User

Hello Gunter;
Each DBMS (Oracle, SQLServer, DB2) has its own syntax. Attached is a DB2
example. In Oracle, you would use PL/SQL and in SQLServer TSQL. Hope this
helps:
-----------------------
drop procedure dynSQL;
create procedure dynSQL (in companyCode char(3))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
READS SQL DATA
INHERIT SPECIAL REGISTERS

begin
declare strStatement varchar(254);

--Dynamic cursor
BEGIN
declare cur1 cursor WITH RETURN TO CALLER for stmt;

set strStatement = 'select * from ttcibd100' || companyCode;
prepare stmt from strStatement;

-- leave cursor open for return of results to client
open cur1;

END;
end;
-----------------------
gunter rombauts wrote:

> Hi,

> I am not familiar with stored procedures. How would this work for me?
> Wat is required to do this for "select * from ttcibd001<company>"
> Would it be possible to send me an example?

> Thanks

Previous Topic:	How to access chart elements by scripting?
Next Topic:	Metadata for dataset creation

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Apr 30 00:32:18 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter