Adding self-restricting condition on join 1

[blom0344]

In certain scenario's we want to restrict a 1:n relation into a 1:1 relation. For instance when only information from the first record is of any interest (like an earliest date)
I am used to adding such a restriction to a join like:

table1.field1 = table2.field1 and [COLOR=red]
table2.date = (select min(t2_alias.date) from table2 t2_alias
where table2.key = t2_alias.key) [/color]

However, I cannot get the syntax right when I modify an existing join.

I can create an additional query-subject that stores all min(dates) for each key and then add a join over both key and date from the new query subject to the table2.

That should have the same effect (if an object from this query subject is added to the selection)

What am I missing. Is it at all possible to modify a straight join this way?

Ties Blom

 

[mfgf]

Hi,

The problem here is that you are used to being able to use SQL syntax within a join definition, as this is the way BO does things. Cognos 8 is somewhat different - when you define a relationship as an expression, you are using C8 expression syntax, not SQL.

As I see it, your options are:

a) Create a data query subject which joins table2 to it's alias in the SQL query (based on your date expression as above), then set up a relationship from table1 to this data query subject based on field1 being equal.

b) Create an additional query subject that stores all min dates for each key, add a relationship to table2 based on field1 and date, then merge these two query subjects into a model query subject and add a relationship between table1 and this model query subject.

Happy modelling!

MF.

 

[blom0344]

mfgf,

Will try next morning. In the meantime have a star..

Ties Blom

 

[mfgf]

Thank you so much! It's highly appreciated

 

[blom0344]

I'd say it is highly earned.
Keeping track of this Cognos forum is better than the Cognos Fasttrack course!

Am I right in assuming that Cognos does not have the 'derived table' functionality of the latest BO version?

[small][olive]Derived tables where added to the universe palette, cause developers where then no longer dependant on the cooperation of DBA's for custom views.

The derived table is basically a universe-based view (entirely SQL-based) that can hold any valid SQL statement.[/olive][/small]



Ties Blom

 

[mfgf]

Hi,

I guess you could argue that any data Query Subject in Framework Manager is the equivalent of a BO Derived Table. After all, it simply contains a SQL query - by default 'Select * from <table>', but as a modeller you can modify the query to be any valid SQL query you like (spanning multiple tables, if you wish).

I guess you could also argue that any model Query Subject ends up being the functional equivalent of a BO Derived Table too, as it is simply a list of items assembled from one or more other query subjects, so in a way this is the modellers equivalent of a database view.

Sounds to me like BO are playing catch-up to Cognos here! ;-)

Thanks for the kind comments too - and apologies for the long, boring tomes of late.

Best regards,

MF.

 

[blom0344]

Well, I do not know which one was first , BO or Cognos.

However, a derived table can literally be ANY SQL statement, so it is possible to add virtual (calculated) fields to it.

I guess that this does not apply to model query subjects.
The derived table will let me introduce for instance new virtual grouping objects by using CASE statements.

I somehow seem to miss the quick way to introduce an object that is a new virtual one (like count of the number of orders)

[small]Well actually this I used to do in the project viewer equivalent of BO and now it has to be introduced into query subjects.[/small]



Ties Blom

 

[mfgf]

Hi,

You can code new virtual grouping objects using the 'Add' link in a Model Query subject, which then prompts you for the name of the item and the expression to use - you can use either simple case or search case constructs for the example you quote above.

In a data query subject, you can do the same by adding a calculation on the calculation tab.

For the example of counting the number of orders, just add an item which uses the count summary in its expression.

Seems to be just as quick and easy in Framework Manager as in a BO universe. Or am I missing something else?

Best regards,

MF.

 

[blom0344]

Well, the confusing bit is that you have to first add objects / calculations to query subjects.
These new objects /calculations then show up in the diagram en the project viewer.

In BO the diagram only presents tables/views and derived table definitions.

When I want to add a calculated field I simply copy an object , change the name and alter the syntax.

It is literally virtual, cause it does not appear in the diagram.

Thanks for your guidance...

Ties Blom