Beginner Problem. Calculating Difference between two Sums in FM ?

[mixim]

Hello, im in a project (my first, first job after school) where we are converting Business Object universes  to Cognos frameworks one-to-one.  One object (item) in BO is built of a freehand SQL query, where two sums are aggregated and then subtracted:

Code Select Expand

SELECT
( sum(TBL1.QTY) ) - ( sum(TBL2.QTY) )
FROM
INVEST_RESULT_T,
ITEM_BLOCKED_T
WHERE
(TBL3.ID(+)=TBL4.ID)

This displays one row, with one number when running in SQL Dev.

Inserting sum before each item  in the expression definition for the subtraction (as shown below), does the trick while testing in FM. But when trying to validate it throws me an ORA-00937 error:

Code Select Expand

sum([Business Layer].[TBL1].[QTY1]) - sum([Business Layer].[TBL2].[QTY2])


Even though i have specified the two items as fact in the usage and sum in the aggregate... the below definition expression does not sum-aggregate each column for before doing the subtraction. But instead displaying it per row.

Code Select Expand

[Business Layer].[TBL1].[QTY1] - [Business Layer].[TBL2].[QTY2]

Anybody have an idea or input regarding how to calculate difference between two sum-aggregated items, please share! And since im a complete novice in SQL, please tell me if i should add more information!

[blom0344]

FM allows coding SQL subjects within a model with the consequence that this piece of SQL is executed as an inline view.  In you example the result is exactly 1 value (a measure) Question : how is it used within the original universe?  Is it a completely standalone calculation? 

By coding this as a SQL subject in FM it is no longer relevant whether usage is set to fact and the aggregate setting. It does not even generate nothing more than a single value

[mixim]

FM allows coding SQL subjects within a model with the consequence that this piece of SQL is executed as an inline view.  In you example the result is exactly 1 value (a measure) Question : how is it used within the original universe?  Is it a completely standalone calculation? 

By coding this as a SQL subject in FM it is no longer relevant whether usage is set to fact and the aggregate setting. It does not even generate nothing more than a single value

Hello and thanks for the reply!

Unfortunately the universe is mostly used for ad-hoc queries, so i don't have an example of how the object is used. But if I add it in a list together with another identifier column from another table, i get different values on each row also compared to BO. While using SQL for the query and not building it "correctly" in FM would be an option, i would like to avoid that to the full extent that i can... Wondering if it is possible.

Basically, the Cognos generates an SQL doing it with logic like this (this is not the actual generated sql)

Code Select Expand

SELECT
sum(TBL1.QTY -TBL2.QTY)
FROM
...................

But i am after something having the same logic as below.

Code Select Expand

SELECT
( sum(TBL1.QTY) ) - ( sum(TBL2.QTY) )
FROM
......................
Is this possible or do this in FM? Or will I really have to use "freehand" SQL?

[blom0344]

Using regular query subjects and joins between them will result in performing the join prior to performing the calculation which means that the calc is done over matching rows as defined by the join.

The result is the example you gave that does not meet your requirements.

Hardcoding in SQL will enforce things though in your case SQL server is more permissive than Oracle.  Are you also making a swap from SQL server to Oracle?   Performing aggregates AND subtractions used to more problematic in DB2/Oracle than in SQL server

But my question would still be:  Where is it used for? How does it relate to other part of the model.  Surely you can deduce this from the existing universe.
Is it used in a crossjoin with other defined tables/ derived tables