Taking the latest version

building semantic layer
1

Hello,

I am new to BO, sorry if the question is a bit silly;

There are 2 tables: tb_project and tb_contract, linked one-to-many (each contract having a foreign key to project). Every contract has a version (integer), so for one project there can be multiple contracts having the same value of FK (project id) and different versions

When desiging my universe, I need to take into account only the latest versions of contracts.

In SQL, I have a ‘where’ clause smth like 'where contracts1.version= select max(version) from contracts2 where contracts1.project_id=contracts1.project_id)

But so far I could not find a way to achieve the same result in BO - to make sure that in my universe only the latest versions of the contract will appear?

Thanks in advance!

heleona :belgium: (BOB member since 2009-05-14)

2

ok, I found a solution myself :)) First workaround that I used I created a view in the DB to select the latest version and then added this view to BO.

Another way would be to create a similar custom filter in BO (I’m playing with this now, cause I am very new to BO :slight_smile: but I think it should work fine);

heleona :belgium: (BOB member since 2009-05-14)

3

Your on the right track…

tb_contract.version 
IN (select max(a.version) 
from
tb_contract a where a.project_id = tb_contract.project_id)

Place that code in a Predefined filter in your universe.

dessa :madagascar: (BOB member since 2004-01-29)

4

Hello,

try this: Edit the join between the two tables and use

contracts1.version = contracts2.version AND 
contracts2.version = (SELECT MAX(version) FROM contracts2 WHERE contracts2.project_id = contracts1.project_id)

or whatever you need, thus creating a complex join.

Zeitgeist (BOB member since 2008-11-21)