Cognos FM 关系项：示例用where但自建用join

Cognos Sample中自带gosales的模型工程，查看模型中表之间的关系项测试sql如下：

自己建立FM模型，同样的关系项，测试sql如下图：

[code class="lang-sql" lang="sql"]SELECT
        Query1.BRANCH_CODE AS BRANCH_CODE,
        Query1.ADDRESS1 AS ADDRESS1,
        Query1.ADDRESS1_MB AS ADDRESS1_MB,
        Query1.ADDRESS2 AS ADDRESS2,
        Query1.ADDRESS2_MB AS ADDRESS2_MB,
        Query1.CITY AS CITY,
        Query1.CITY_MB AS CITY_MB,
        Query1.PROV_STATE AS PROV_STATE,
        Query1.PROV_STATE_MB AS PROV_STATE_MB,
        Query1.POSTAL_ZONE AS POSTAL_ZONE,
        Query1.COUNTRY_CODE AS COUNTRY_CODE,
        Query1.ORGANIZATION_CODE AS ORGANIZATION_CODE,
        Query1.WAREHOUSE_BRANCH_CODE AS WAREHOUSE_BRANCH_CODE,
        Query2.INVENTORY_YEAR AS INVENTORY_YEAR,
        Query2.INVENTORY_MONTH AS INVENTORY_MONTH,
        Query1.WAREHOUSE_BRANCH_CODE AS WAREHOUSE_BRANCH_CODE1,
        Query2.PRODUCT_NUMBER AS PRODUCT_NUMBER,
        Query2.OPENING_INVENTORY AS OPENING_INVENTORY,
        Query2.QUANTITY_SHIPPED AS QUANTITY_SHIPPED,
        Query2.ADDITIONS AS ADDITIONS,
        Query2.UNIT_COST AS UNIT_COST,
        Query2.CLOSING_INVENTORY AS CLOSING_INVENTORY,
        Query2.AVERAGE_UNIT_COST AS AVERAGE_UNIT_COST,
        Query1.BRANCH_CODE AS BRANCH_CODE1,
        Query1.ADDRESS1 AS ADDRESS11,
        Query1.ADDRESS1_MB AS ADDRESS1_MB1,
        Query1.ADDRESS2 AS ADDRESS21,
        Query1.ADDRESS2_MB AS ADDRESS2_MB1,
        Query1.CITY AS CITY1,
        Query1.CITY_MB AS CITY_MB1,
        Query1.PROV_STATE AS PROV_STATE1,
        Query1.PROV_STATE_MB AS PROV_STATE_MB1,
        Query1.POSTAL_ZONE AS POSTAL_ZONE1,
        Query1.COUNTRY_CODE AS COUNTRY_CODE1,
        Query1.ORGANIZATION_CODE AS ORGANIZATION_CODE1,
        Query1.WAREHOUSE_BRANCH_CODE AS WAREHOUSE_BRANCH_CODE2,
        Query2.INVENTORY_YEAR AS INVENTORY_YEAR1,
        Query2.INVENTORY_MONTH AS INVENTORY_MONTH1,
        Query2.WAREHOUSE_BRANCH_CODE AS WAREHOUSE_BRANCH_CODE11,
        Query2.PRODUCT_NUMBER AS PRODUCT_NUMBER1,
        Query2.OPENING_INVENTORY AS OPENING_INVENTORY1,
        Query2.QUANTITY_SHIPPED AS QUANTITY_SHIPPED1,
        Query2.ADDITIONS AS ADDITIONS1,
        Query2.UNIT_COST AS UNIT_COST1,
        Query2.CLOSING_INVENTORY AS CLOSING_INVENTORY1,
        Query2.AVERAGE_UNIT_COST AS AVERAGE_UNIT_COST1
FROM
        ( SELECT
                BRANCH.BRANCH_CODE AS BRANCH_CODE,
                BRANCH.ADDRESS1 AS ADDRESS1,
                BRANCH.ADDRESS1_MB AS ADDRESS1_MB,
                BRANCH.ADDRESS2 AS ADDRESS2,
                BRANCH.ADDRESS2_MB AS ADDRESS2_MB,
                BRANCH.CITY AS CITY,
                BRANCH.CITY_MB AS CITY_MB,
                BRANCH.PROV_STATE AS PROV_STATE,
                BRANCH.PROV_STATE_MB AS PROV_STATE_MB,
                BRANCH.POSTAL_ZONE AS POSTAL_ZONE,
                BRANCH.COUNTRY_CODE AS COUNTRY_CODE,
                BRANCH.ORGANIZATION_CODE AS ORGANIZATION_CODE,
                BRANCH.WAREHOUSE_BRANCH_CODE AS WAREHOUSE_BRANCH_CODE
        FROM
                gosales_self..GOSALES.BRANCH BRANCH
        ) Query1 INNER JOIN ( SELECT
                INVENTORY_LEVELS.INVENTORY_YEAR AS INVENTORY_YEAR,
                INVENTORY_LEVELS.INVENTORY_MONTH AS INVENTORY_MONTH,
                INVENTORY_LEVELS.WAREHOUSE_BRANCH_CODE AS WAREHOUSE_BRANCH_CODE,
                INVENTORY_LEVELS.PRODUCT_NUMBER AS PRODUCT_NUMBER,
                INVENTORY_LEVELS.OPENING_INVENTORY AS OPENING_INVENTORY,
                INVENTORY_LEVELS.QUANTITY_SHIPPED AS QUANTITY_SHIPPED,
                INVENTORY_LEVELS.ADDITIONS AS ADDITIONS,
                INVENTORY_LEVELS.UNIT_COST AS UNIT_COST,
                INVENTORY_LEVELS.CLOSING_INVENTORY AS CLOSING_INVENTORY,
                INVENTORY_LEVELS.AVERAGE_UNIT_COST AS AVERAGE_UNIT_COST
        FROM
                gosales_self..GOSALES.INVENTORY_LEVELS INVENTORY_LEVELS
        ) Query2 ON Query1.BRANCH_CODE = Query2.WAREHOUSE_BRANCH_CODE

[/code]

这里没有允许使用with as格式的查询。

想问下为什么呢？怎么样才能生成图片里那种格式的查询呢？