[SOLVED] Cognos report (gererate query in Query explorer)

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?

Solution

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.