CODEWITHSUNDEEP
db2cognoscognos-10cognos-bicognos-tm1
VIRAL_ZONE
VIRAL_ZONE

Reputation: 43

Cognos report (gererate query in Query explorer)

I have data item Dealer Code in the report which I fetching from D_DEALER Table. But here when I perform generate sql it gives the below query.

"select distinct
  "D_DEALER"."I_CURR" "Dealer_Code"
from
  "DEALER"."D_DEALER" "D_DEALER"          ,
  "SRVCTRCT"."PGM_OPTIONS" "PGM_OPTIONS"  ,
  "SRVCTRCT"."OPTION_INDEX" "OPTION_INDEX",
  "SRVCTRCT"."L_DLR_PROFL" "D_SC_DLR_PROFILE"
where
  "D_DEALER"."L_DLR_CURR_REC" = 'Y'
  and "PGM_OPTIONS"."C_PGM_ATTR" NOT in ('U')
  and "D_SC_DLR_PROFILE"."I_DLR_DIM" = "D_DEALER"."I_DLR_DIM"
  and "OPTION_INDEX"."C_OPT_KEY"     = "D_SC_DLR_PROFILE"."C_KEY_OPT"
  and "OPTION_INDEX"."I_SC_OPT_SAN"  = "PGM_OPTIONS"."I_SC_OPT_SAN" FOR FETCH ONLY"

There are certains level of joins applied in Cognos FM. But ideally it should fetch record from only one table (D_DEALER) – why are other tables (PGM_OPTIONS, OPTION_INDEX, D_SC_DLR_PROFILE) coming into the picture?

Upvotes: 0

Views: 263

Answers (2)

somesingsomsing
somesingsomsing

Reputation: 3350

FM has the joins but when you pull a column from one query subject then the SQL should only show one table as you expected but what is most likely is that the table D_DEALER has embedded filters in FM with the values 

"D_DEALER"."L_DLR_CURR_REC" = 'Y'
 "PGM_OPTIONS"."C_PGM_ATTR" NOT in ('U')

That is my guess

Upvotes: 0

Paul Vernon
Paul Vernon

Reputation: 3901

This is really a Cognos question, but from a Db2 point of view, if you have Foreign Keys on the joins between the tables the the optimizer will remove any redundant joins.

However, looking at your query, all the joins need to be done so that the "PGM_OPTIONS"."C_PGM_ATTR" NOT in ('U') filter can be applied. I.e. you are asking only for Dealers that do not have a C_PGM_ATTR of 'U'. If you remove that requirement, you might find that Db2 and/or Cognos will remove the joins to the other tables.

Upvotes: 1

Related Questions