Joining Condition in FM

[PRIT AMRIT]

Hi guru's

I have a typical Scenario. Please look at the below SQL's

1.This SQL gives me the Result 1 in attached doc. It gives all the Category evevn if the VOLUME is 'NULL'

Select TRN.DAY_KEY,TRN.START_DATE,TRN.END_DATE,TRN.CATEGORY
,SUM(VOL.WE_ACT_COMM_VOL)
from
        TRN_TIME_CATEGORY TRN join
        MAP_TIME MAP on (MAP.DAY_POT_START >= TRN.START_DATE AND MAP.DAY_POT_START < TRN.END_DATE)
        left outer JOIN TDS_WE_ISELL_ACT_SLS_VOL VOL ON (MAP.DAY_OID = VOL.WE_DAY_OID )
        left outer join MAP_WE_ISELL_SLSORG ORG ON (VOL.WE_ISELL_SLSORG_OID = ORG.BE_ID and ORG.WE_ISELL_SLSORG_NAME='Germany' )
WHERE TRN.DAY_CODE='2012-09-05'
AND (VOL.IN_USE_FLAG <> 'd' OR VOL.IN_USE_FLAG IS NULL)
GROUP BY TRN.DAY_KEY,TRN.DAY_KEY,TRN.START_DATE,TRN.END_DATE,TRN.CATEGORY


2.This SQL gives me the Result 2 in attached doc. It gives the Category ONLY having the VOLUME ----Our current Reporting is based on below query
Select TRN.DAY_KEY,TRN.START_DATE,TRN.END_DATE,TRN.CATEGORY
,SUM(VOL.WE_ACT_COMM_VOL)
from
        TRN_TIME_CATEGORY TRN join
        MAP_TIME MAP on (MAP.DAY_POT_START >= TRN.START_DATE AND MAP.DAY_POT_START < TRN.END_DATE)
        left outer JOIN TDS_WE_ISELL_ACT_SLS_VOL VOL ON (MAP.DAY_OID = VOL.WE_DAY_OID )
        left outer join MAP_WE_ISELL_SLSORG ORG ON (VOL.WE_ISELL_SLSORG_OID = ORG.BE_ID )
WHERE TRN.DAY_CODE='2012-09-05'
AND (VOL.IN_USE_FLAG <> 'd' OR VOL.IN_USE_FLAG IS NULL)
and ORG.WE_ISELL_SLSORG_NAME='Germany'
GROUP BY TRN.DAY_KEY,TRN.DAY_KEY,TRN.START_DATE,TRN.END_DATE,TRN.CATEGORY

My question is, if I want to impliment the SQL 1 login in the current joining condition in FM. When the User would Filter the report by 'Country=Germany', instead of putting it in WHERE CLUSE, can we force cognos to consider it in the Joining statement(as shown in SQL1)?

NOTE: I don't want to put any parameter in the Joining Condition, because I don't want it to Prompt any time user would access this item in Query Studio.

Looking forward to hear from you. Thanks!

Regards,
Prit

[tjohnson3050]

You can define complex join logic in FM.  When you open the relationship, click the ... button to the upper right of the expression box.

[cognostechie]

Putting such a custom SQL in FM defeats the purpose of modelling ! It's better to create seperate query subjects for individual tables and then join them. That way you will not be limiting the result set to some pre-determined report.

For the country filter for Germany, it's better to create standalone filters which the users can drag & drop to filter by Germany. That way, they can also have the report for other country if required.

[PRIT AMRIT]

Hello guys,

I understand what you mean, but I have a different scenario.

MAP_WE_ISELL_SLSORG ORG ON (VOL.WE_ISELL_SLSORG_OID = ORG.BE_ID and ORG.WE_ISELL_SLSORG_NAME=?country? )

In the above case, if you define a Joining condition like this in FM, it would Prompt you to select a 'Country' in the Query Studio.

My rquirement is, User Drags and drop Country Data Item and Volume Measure in the Query Studio. Right Click on Country--Filter-- Germany, in this case the Cognos would write the below SQL:

Select ORG.Country, Sum(VOL.Voulme)
from
       TDS_WE_ISELL_ACT_SLS_VOL VOL
       left outer join MAP_WE_ISELL_SLSORG ORG ON (VOL.WE_ISELL_SLSORG_OID = ORG.BE_ID )
where ORG.WE_ISELL_SLSORG_NAME='Germany'
GROUP BY ORG.Country

But I want cognos to write the SQL like below

Select ORG.Country, Sum(VOL.Voulme)
from
       TDS_WE_ISELL_ACT_SLS_VOL VOL
       left outer join MAP_WE_ISELL_SLSORG ORG ON (VOL.WE_ISELL_SLSORG_OID = ORG.BE_ID and ORG.WE_ISELL_SLSORG_NAME='Germany' )
GROUP BY ORG.Country)

NOTE: I can't create separte Query Subjects becasue of the Model Complexity.

Is it possible? If yes, please let me have the technique. Thanks!

Regards,
Prit

[tjohnson3050]

I don't think there is a way to meet that requirement.  You can set implicit or explicit join types, but that will not put an on the fly filter into the join syntax.

The closest thing I can think of is using a syntax like:

MAP_WE_ISELL_SLSORG ORG ON (VOL.WE_ISELL_SLSORG_OID = ORG.BE_ID and 
(ORG.WE_ISELL_SLSORG_NAME=#prompt('country','text',' ')# or
#prompt('country','text',' ')# = ' ')

This will default the join to just the ID portion by default, not prompt country, but use country in the join when one is selected, but this still wouldn't work with on the fly filtering in Query Studio.

[PRIT AMRIT]

This will default the join to just the ID portion by default, not prompt country, but use country in the join when one is selected, but this still wouldn't work with on the fly filtering in Query Studio.

--- This is where I am stuck. The users do not want anything to be prompted in Query Studio.

Anyway, let's wait if anybody has done something like this before  ;)

Thanks tjohnson3050....

[Greg]

Can you explain why you want the predicate to be included in the join instead of the where clause?  The two sql statements you provided are semantically equivalent.  Do they produce different query execution plans on your DB?

Greg