将包含筛选器的IBMCognosSQL转换为MicrosoftSQLServer查询

nom7f22z  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(669)

我正在尝试将包含过滤器的ibmcognossql转换为microsoftsqlserver查询。
我尝试在microsoftsqlserver查询中应用过滤器，但没有成功。
下面是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 report studio的本机sql查询：

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服务器代码中应用这个过滤器吗。事先谢谢你的帮助。

sqlsql-servercognoscognos-10cognos-bi

3条答案

按热度按时间
nhn9ugyo

nhn9ugyo1#

在cognos查询上生成sql时，它将同时显示cognos sql和本机sql。CognosSQL是报表所需的，而本机sql是发送到数据库的。如果没有本地处理，并且这两种sql类型在合理的范围内是相同的，那么您应该能够在不进行任何编辑的情况下使用本机sql对话框中的sql。

赞(0）分享  回复(0）举报  2021-07-24
ntjbwcob

ntjbwcob2#

cognosbi默认使用“动态查询模式”（从10.2.1开始）。它基本上是cognos的查询引擎。引擎计划执行，该执行可以包括在从数据源接收到数据之后在本地处理数据。您可以在ibm的免费红皮书中阅读所有关于它的内容
http://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf
下面是发动机的简要概述
因此，这意味着本机查询并不是全部。从mssql接收数据后，cognos可能会做更多的处理。也就是说，如果您想在mssql中复制相同的查询，您需要自己完成缺少的逻辑。如果cognos中的多维模型以1:1的比例Map到mssql关系数据库，那么这应该很容易（自己将cognossql中缺少的部分转换为mssql）。很多时候，cognos中的直接b/c模型可能不是1:1Map到mssql表/列。在这种情况下，当您将cognos查询转换为mssqlquery b/c时，您需要了解cognos内部的模型，这是本地查询和cognos查询之间发生的事情的一部分。
我对您的模型部分没有任何帮助，但是对于其他部分，您可以首先尝试将查询的（或数据源的）“rollup processing”属性更改为“database”。这将有助于将扩展聚合函数（xcount等）转换为本机sql。还要检查“usesqlparameters”属性并将其设置为“literal”，看看这是否有助于处理原生sql中的参数。
“属性”窗口的屏幕截图
rank（）函数的语法与mssql中的语法几乎相同，因此在处理扩展函数之后，应该能够轻松地处理它。但我会先看看新的本机查询。
rcount（running count）也可以在mssql中使用窗口函数轻松地处理（参见下面的链接）。但同样，首先生成新的本机查询并查看rcount是否消失。
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

赞(0）分享  回复(0）举报  2021-07-24
dgenwo3n

dgenwo3n3#

这里是创建过滤器的地方。筛选器不会显示在本机sql查询中。

赞(0）分享  回复(0）举报  2021-07-24
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com