sql - 将包含过滤器的 IBM Cognos SQL 转换为 Microsoft SQL Server Query

Question

我正在尝试将包含过滤器的 IBM Cognos SQL 转换为 Microsoft SQL Server Query。

我尝试在 Microsoft SQL Server 查询中应用过滤器，但它不起作用。

这里是 IBM Cognos 查询：

IBM Cognos SQL

select
       rank() over ( at client__iacode.ia_code order by XCOUNT(client_document.client_document_id  for client__iacode.ia_code ) desc nulls last)  as  Rank_IA,
       client__iacode.ia_code  as  IA_Code,
       client__iacode.ia_short_descr  as  IA_Short_Descr,
       XCOUNT(client_document.client_document_id  for client__iacode.ia_code,client__iacode.ia_short_descr )  as  Doc_Count,
       XCOUNT(client.client_code  at client__iacode.ia_code,client__iacode.ia_short_descr,client.client_id  for client__iacode.ia_code,client__iacode.ia_short_descr )  as  Client_Count,
       XSUM(XCOUNT(client_document.client_document_id  for client__iacode.ia_code,client__iacode.ia_short_descr )  at client__iacode.ia_code,client__iacode.ia_short_descr )  as  Total_Doc_Count_,
       XSUM(XCOUNT(client.client_code  at client__iacode.ia_code,client__iacode.ia_short_descr,client.client_id  for client__iacode.ia_code,client__iacode.ia_short_descr )  at client__iacode.ia_code,client__iacode.ia_short_descr )  as  Total_Client_Count_
from
       (
           dbo.client client
           join
           dbo.broker client__broker
           on (client.broker_id = client__broker.broker_id)
           join
           dbo.ia_code client__iacode
           on (client.ia_code_id = client__iacode.ia_code_id)
           join
           dbo.client_document client_document
           on (client.client_id = client_document.client_id)
           join
           dbo.client_status client_status
           on (client.client_status_id = client_status.client_status_id)
           join
           dbo.provider client__provider
           on (client__provider.provider_id = client.provider_id)
       )
       left outer join
       dbo.branch client__iacode__branch
       on (client__iacode.branch_id = client__iacode__branch.branch_id)
where
       (client_document.requested_date is not NULL) and
       (client_document.received_date is NULL) and
       (client__iacode__branch.branch_descr = CAST(:PQ1 AS varchar(255))) and
       (client__broker.broker_code = 'CCC') and
       (client_status.client_status_code = 'A') and
       ((client__provider.provider_code <> 'PRS-R') or (client__provider.provider_code is NULL))
group by
       client__iacode.ia_code,
       client__iacode.ia_short_descr
filter
       (rank() over ( at client__iacode.ia_code order by XCOUNT(client.client_code  at client__iacode.ia_code,client.client_id  for client__iacode.ia_code ) desc nulls last) <= 25) and
       (RCOUNT(rank() over ( at client__iacode.ia_code order by XCOUNT(client_document.client_document_id  for client__iacode.ia_code ) desc nulls last)  at client__iacode.ia_code  order by rank() over ( at client__iacode.ia_code order by XCOUNT(client_document.client_document_id  for client__iacode.ia_code ) desc nulls last) asc,client__iacode.ia_code asc,client__iacode.ia_short_descr asc ) <= 25)
order by
       Rank_IA asc,
       IA_Code asc,
       IA_Short_Descr asc

这里是来自 IBM Cognos 报告工作室的 Native SQL Query：

select "client__iacode"."ia_code" AS "C0", "client_documen

    t"."client_document_id" AS "C1", "client__iacode"."ia_short_descr" AS "C2", "client"."client_id" AS "C3", "client"."client_code" AS "C4"
    
    from ((((("dbo"."client" "client" INNER JOIN "dbo"."broker" "client__broker" on "client"."broker_id" = "client__broker"."broker_id") INNER JOIN "dbo"."ia_code" "client__iacode" on "client"."ia_code_id" = "client__iacode"."ia_code_id") INNER JOIN "dbo"."client_document" "client_document" on "client"."client_id" = "client_document"."client_id") INNER JOIN "dbo"."client_status" "client_status" on "client"."client_status_id" = "client_status"."client_status_id") INNER JOIN "dbo"."provider" "client__provider" on "client__provider"."provider_id" = "client"."provider_id") LEFT OUTER JOIN "dbo"."branch" "client__iacode__branch" on "client__iacode"."branch_id" = "client__iacode__branch"."branch_id"
    
    where  NOT "client_document"."requested_date" is null and "client_document"."received_date" is null and "client__iacode__branch"."branch_descr" = CAST( @BRANCH AS VARCHAR( 255 ) ) and "client__broker"."broker_code" = 'CCC' and "client_status"."client_status_code" = 'A' and ("client__provider"."provider_code" <> 'PRS-R' or "client__provider"."provider_code" is null)

这里是我到目前为止创建的 Microsoft SQL Server 查询，但它没有向我显示预期的结果：

select L1.*
from
(
select        L.Rank_IA,
              L.IA_Code,
              L.IA_Short_Descr,
              L.Doc_Count,
              L.Client_Count,
              L.[Rank],
              (count   (L.Condition) over( order by L.Condition asc, L.IA_Code asc, IA_Short_Descr asc )) as Condition
              from
              (
select
       rank() over (  order by COUNT(client_document.client_document_id    ) desc  )  as  Rank_IA,
       client__iacode.ia_code  as  IA_Code,
       client__iacode.ia_short_descr  as  IA_Short_Descr,
       COUNT( client_document.client_document_id   )  as  Doc_Count,
       COUNT(client.client_code   )  as  Client_Count,
      (rank() over (  order by COUNT( client.client_id   ) desc ) )  as [Rank],
      rank() over (  order by COUNT(  client_document.client_document_id   ) desc ) as Condition 
      --XSUM(XCOUNT(client_document.client_document_id  for client__iacode.ia_code,client__iacode.ia_short_descr )  at client__iacode.ia_code,client__iacode.ia_short_descr )  as  Total_Doc_Count_,
      --XSUM(XCOUNT(client.client_code  at client__iacode.ia_code,client__iacode.ia_short_descr,client.client_id  for client__iacode.ia_code,client__iacode.ia_short_descr )  at client__iacode.ia_code,client__iacode.ia_short_descr )  as  Total_Client_Count_
from
       (
           dbo.client client
           join
           dbo.broker client__broker
           on (client.broker_id = client__broker.broker_id)
           join
           dbo.ia_code client__iacode
           on (client.ia_code_id = client__iacode.ia_code_id)
           join
           dbo.client_document client_document
           on (client.client_id = client_document.client_id)
           join
           dbo.client_status client_status
           on (client.client_status_id = client_status.client_status_id)
           join
           dbo.provider client__provider
           on (client__provider.provider_id = client.provider_id)
       )
       left outer join
       dbo.branch client__iacode__branch
       on (client__iacode.branch_id = client__iacode__branch.branch_id)
where
       (client_document.requested_date is not NULL) and
       (client_document.received_date is NULL) and
       (client__iacode__branch.branch_descr = CAST(@BRANCH AS varchar(255))) and
       (client__broker.broker_code = 'CCC') and
       (client_status.client_status_code = 'A') and
       ((client__provider.provider_code <> 'PRS-R') or (client__provider.provider_code is NULL))
group by
       client__iacode.ia_code,
       client__iacode.ia_short_descr)L)L1
--filter
--      (rank() over ( at client__iacode.ia_code order by XCOUNT(client.client_code  at client__iacode.ia_code,client.client_id  for client__iacode.ia_code ) desc nulls last) <= 25) and
--      (RCOUNT(rank() over ( at client__iacode.ia_code order by XCOUNT(client_document.client_document_id  for client__iacode.ia_code ) desc nulls last)  at client__iacode.ia_code  order by rank() over ( at client__iacode.ia_code order by XCOUNT(client_document.client_document_id  for client__iacode.ia_code ) desc nulls last) asc,client__iacode.ia_code asc,client__iacode.ia_short_descr asc ) <= 25)
where L1.[Rank] <=25
and L1.Condition<=25
order by
       Rank_IA asc,
       IA_Code asc,
       IA_Short_Descr asc

您能帮我在 Microsft SQL Server 代码中应用该过滤器吗？在此先感谢您的帮助。

标签： sqlsql-servercognoscognos-10cognos-bi

Answer 1

Cognos BI 默认使用“动态查询模式”（从 10.2.1 开始）。它基本上是 Cognos 的查询引擎。引擎计划执行可能包括在从数据源接收到数据之后在本地处理数据。你可以在这里阅读 IBM 的免费红皮书

http://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf

这是引擎的快速概述

因此，这意味着本机查询并不是全部。Cognos 在接收到来自 MS SQL 的数据后可能会做更多的处理。话虽如此，如果您想在 MS SQL 中复制相同的查询，您将需要自己完成缺失的逻辑。如果 Cognos 中的多维模型以 1:1 的比例映射到 MS SQL 关系数据库，那应该很容易（自己将 Cognos SQL 中缺失的部分转换为 MS SQL）。很多时候，Cognos 中的模型可能不会 1:1 映射到 MS SQL 表/列，这并不是那么直接。在这种情况下，在将 Cognos 查询转换为 MS SQL 查询 b/c 时，您需要了解 Cognos 内部的模型，这是本机查询和 Cognos 查询之间发生的事情的一部分。

我对您的模型部分没有任何帮助，但对于其余部分，您可以先尝试将查询（或数据源）的“汇总处理”属性更改为“数据库”。这应该有助于将扩展聚合函数（XCOUNT 等）转换为本机 SQL。还要检查“使用 SQL 参数”属性并将其设置为“文字”，看看这是否有助于本机 SQL 中的参数。

属性窗口的屏幕截图

rank() 函数语法与 MS SQL 中的语法几乎相同，因此一旦处理了扩展函数，您应该能够轻松处理它。但我会先看看新的原生查询。

RCount（运行计数）也可以在 MS SQL 中使用窗口函数轻松处理（参见下面的链接）。但同样，首先生成新的本机查询并查看 RCount 是否消失。

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15