将报告从COGNOS迁移到SQL Server [英] Migrate report from COGNOS to SQL Server

问题描述

我在Cognos报表中生成了以下查询。

I have the following query generated in a Cognos report.

我的问题是我需要它运行，并且在SQL Server中具有相同的逻辑/过滤器。可以生成下面的过滤器（COGNOS语法）以相同的方式工作，例如SQL Server中的 WHERE 吗？

My problem is I need it to work, with the same logic / filter in SQL Server. Can the filter below (COGNOS syntax) be generated to work in the same way, like a WHERE in SQL Server?

select
      *    
from
       dbo.ia_code 
group by
       client__iacode.ia_code,
       client__iacode.ia_short_descr
 ------ here my problem
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)

任何帮助将不胜感激。

推荐答案

首先尝试更改查询（或数据源）的汇总处理；属性数据库。这应该有助于将扩展的聚合函数（XCOUNT等）转换为本机SQL。还要检查使用SQL参数属性并将其设置为文字并查看这是否有助于使用本机SQL中的参数。完成这些操作后，在此处粘贴新生成的 native 查询。

First try changing the query's (or data source's) "Rollup Processing" property to "Database". That should help converting extended aggregate functions (XCOUNT etc) to native SQL. Also check out "Use SQL parameters" property and set it to "Literal" and see if that will helps with the parameters in native SQL. Once you do those, paste the new generated native query here.

属性窗口的屏幕截图

这是我对以下Stackoverflow问题的回答的摘要。在该处阅读更多详细信息，并关注该问题，因为它也可能对您的问题有所帮助。

This is a snippet from my answer to the following Stackoverflow question. Read more details there and also follow that question as it may help with your question too.

将包含过滤器的IBM Cognos SQL转换为Microsoft SQL Server查询

这篇关于将报告从COGNOS迁移到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！