Cognos报表Query Explorer生成SQL不符预期问题咨询

I’ve dealt with this exact headache in Cognos before—let’s walk through the most likely causes and fixes:

• Unintended auto-included table joins from the data model
  Cognos relies heavily on relationships defined in Framework Manager (or your underlying data model). If D_DEALER has predefined relationships to PGM_OPTIONS, OPTION_INDEX, and L_DLR_PROFL, Cognos will automatically pull those tables into your query even if you didn’t add them manually. This often happens if the model enforces referential integrity or has joins set to "auto-include" by default.
  Fix: Head to your Query Explorer, open the Relationships tab for your query, and delete any unwanted joins to those SRVCTRCT tables. If the joins are baked into the model, you’ll need to adjust the relationship settings in Framework Manager—set the join to not auto-include when building reports.

• Accidental field selection or model-level alias mix-up
  You created a "Dealer Code" data item, but the SQL is pulling I_CURR instead. This usually boils down to two things: either you picked the wrong field when setting up the data item (easy to do if column names are unclear), or there’s a model-level data item labeled "Dealer Code" that’s actually mapped to I_CURR by mistake.
  Fix: Edit your "Dealer Code" data item in the query. Double-check the source column—make sure it’s the correct dealer code field from D_DEALER (like DLR_CODE or similar). If it’s a model-defined data item, verify its definition in Framework Manager to correct the mapping.

• Composite query subject in the model
  If the D_DEALER query subject in your model isn’t a direct reference to the table, but a composite that includes hidden joins to SRVCTRCT tables, those tables will show up in the generated SQL no matter what. For example, if someone built D_DEALER as a combined subject that joins to dealer profile or option tables behind the scenes, Cognos will include those dependencies.
  Fix: Check the D_DEALER query subject in Framework Manager. If it has embedded joins, create a new standalone query subject that points only to the D_DEALER table (without any extra joins), then use this new subject in your report.

• Quick check: The distinct clause
  The generated SQL uses select distinct—this is probably intentional if you’re trying to eliminate duplicate dealer codes, but just confirm it’s what you want. You can toggle this setting in the query’s properties if you don’t need it.

内容的提问来源于stack exchange，提问作者VIRAL_ZONE