0

My requirement is to have a query that is something like the following:

SELECT * FROM table1
WHERE oid in (
    SELECT * FROM table 2
    WHERE condition )

My problem is that table1 and table2 are on different databases.

I've read around that you can do something like [db1].table but it doesn't work in my case using Eclipse Birt enviroment.

GiLA3
  • 359
  • 1
  • 5
  • 16

1 Answers1

0

You can solve this in two ways:

1) Use something like a database link. There is limited support in MySQL (note: I've hardly ever used MySQL) See Oracle Database Link - MySQL Equivalent?. This way, you move the problem to the database level.

2) Depending on the actual problem (read: the length of the "select oid FROM table2 where condition" results, you could use either a BIRT data cube or

3) Use two DataSets and a layout structure list-table like this:

The two DataSets need different DataSources.

  • DataSet "T2_oids" with a query "select oid from table2 where condition".
  • DataSet "T1_object" with a single parameter param_oid and a query "select * from table1 where oid = ?".

Layout structure: * Outer ListItem "T2_oids" bound to DataSet "T2_oids" * Inner TableItem or ListItem "T1_object" bound to DataSet "T1_object" with the parameter bound to row["oid"] (or row["OID"], use the list box). This item must be placed inside the T2_oids detail section.

Since T1_object will return a single row, you can even use a GridItem instead of a TableItem or ListItem.

hvb
  • 2,484
  • 1
  • 10
  • 13
  • I've used solution 3) for now since it partially solves the problem, it gets the right sets of keys but it sends it one at the time to the other query, so it's working but I have a problem on record sorting, since they are sorted by key while I need them sorted by column "date", even setting sorting filter on the birt output table doesn't solve the problem, any workaround? – GiLA3 Jul 05 '17 at 09:00
  • There's a way around this sorting problem (by writing the outer results into a POJO (e.g. an ArrayList), then using a scripted dataset to finally sort the results. But this is getting too complicated. So you should focus on 1). – hvb Jul 07 '17 at 07:11