将包含过滤器的IBM Cognos SQL转换为Microsoft SQL Server查询 [英] Convert IBM Cognos SQL which contains a filter to Microsoft SQL Server Query
问题描述
我正试图将包含过滤器的IBM Cognos SQL转换为Microsoft SQL Server查询。
我试图在Microsoft SQL中应用过滤器服务器查询，但是没有用。
此处是IBM Cognos查询：
IBM Cognos SQL
选择
rank（）over（在client__iacode.ia_code上按XCOUNT排序（client_document.client_document_id for client__iacode.ia_code）desc为空）作为Rank_IA ，
client__iacode.ia_code作为IA_Code，
client__iacode.ia_short_descr作为IA_Short_Descr，
XCOUNT（client__iacode.ia_code的client_document.client_document_id），Doc_Count作为$ Doc $ Count，
XCOUNT（client.client_iacode.ia_short_descr）。 client__iacode.ia_code的client_code，client__iacode.ia_short_descr的client_code，client__iacode.ia_code，client__iacode.ia_short_descr的client.client_id）作为Client_Count，
XSUM（XCOUNT（client__iacode.ia_cod的client_document.client_document_id e，client__iacode.ia_code上的client__iacode.ia_short_descr），total_Doc_Count _，
XSUM（XCOUNT（client__iacode.ia_code上的client.client_code，client__iacode.ia_short_descr，client.client_id），client__iacode。在client__iacode.ia_code，client__iacode.ia_short_descr处作为Total_Client_Count_
从
（
dbo.client客户
加入
dbo.broker client__broker
上（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
上（client.client_id = client_document.client_id）
加入
dbo.client_status client_status
上（client.client_status_id = client_status.client_status_id）
加入
dbo.provider client__provider
on（client__provider.provider_id = client.provider_id）
）
左外连接
dbo.branch client__iacode__branch
on（client__iacode。 branch_id = client__iacode__branch.branch_id）
其中
（client_document.requested_date不为NULL）和
（client_document.received_date为NULL）和
（client__iacode__branch.branch_descr = CAST（：PQ1 AS varchar（255））和
（client__broker.broker_code ='CCC'）和
（client_status.client_status_code ='A'）和
（（client__provider.provider_code<> 'PRS-R'）或（client__provider.provider_code为NULL））
分组
client__iacode.ia_code，
client__iacode.ia_short_descr
filter
（rank（）over （在client__iacode.ia_code上按XCOUNT排序（client__iacode.ia_code上为client__iacode.ia_code的client.client_code，client__iacode.ia_code为client.client_id）desc为空）< = 25）和
（RCOUNT（rank（）over（at client__iacode。 ia_code按XCOUNT排序（client_document.client_document_id for client__iacode.ia_code）最后为空）at client__iacode.ia_code按rank（）超过（按client__iacode.ia_code排序为XCOUNT（client__iacode.ia_code的client_document.client_document_id为最后））asc， client__iacode.ia_code asc，client__iacode.ia_short_descr asc）< = 25）
由
排序Rank_IA asc，
IA_Code asc，
IA_Short_Descr asc
这里是来自IBM Cognos Report Studio的本机SQL查询：
选择 ; client__iacode。 ia_code AS C0&， client_documen
t。 client_document_id AS C1， client__iacode。 ia_short_descr AS C2，客户端， client_id。 AS C3，客户端。 client_code AS C4来自（（（（（（（（（（（（（``dbo``.client''``client''``client''''client''INNER JOIN``dbo''。``broker''``client__broker''在``client''上的
。 broker_id = client__broker。 broker_id）INNER JOIN dbo。 ia_code client_iacode在 client上。 ia_code_id = client_iacode __ ID NER。在客户端上加入 dbo。 client_document client_document。 client_id = client_document。 client_id）INNER JOIN dbo。 client_status client_status在客户端上的 client_status_id = client_status。 client_status_id）INNER JOIN dbo。提供商 client_provider在 client_provider上。 provider_id ;。 provider_id）LEFT OUTER JOIN dbo t;。 client__iacode__branch client_iacode上的 branch_id。 = client__iacode__branch。 branch_id
，其中不是 client_document。 requested_date为null和 client_document。 received_date为null且为 client__iacode__branch。 branch_descr ＝ CAST（@BRANCH AS VARCHAR（255））和 client_broker。 = CCC和 client_status。 client_status_code ='A'和（'client_provider'。'provider_code'<'PRS-R'或'client__provider'。'provider_code'为空）
这里是我到目前为止创建的Microsoft SQL Server查询，但是没有显示预期的结果：
选择L1。*
从
（
选择L.Rank_IA，
L.IA_Code，
L.IA_Short_Descr，
L. Doc_Count，
L.Client_Count，
L. [Rank]，
（计数（L.Condition）以上（由L.Condition asc，L.IA_Code asc，IA_Short_Descr asc排序））条件
从
（
选择
rank（）以上（按COUNT（client_document.client_document_id）desc排序）作为Rank_IA，
client__iacode.ia_code作为IA_Code，
client__iacode.ia_short_descr作为IA_Short_Descr，
COUNT（client_document.client_document_id）作为Doc_Count，
COUNT（client.client_code）作为Client_Count，
（rank（）over（由COUNT（client.client_id）desc排序）为[Rank]，
rank（）over（由COUNT排序） （client_document.client_document_id）desc）作为条件
--XSUM（XCOUNT（client__iacode.ia_code，client__iacode.ia_short_descr的XCOUNT（client_iacode.ia_code，client__iacode.ia_short_descr）的XCOUNT）为Total_Doc_CountX，$ UM $ b （XCOUNT（client__iacode.ia_code，client__iacode.ia_short_descr，client__iacode.ia_code，client__iacode.ia_short_descr的client.client_id的XCOUNT（client__iacode.ia_code，client__iacode.ia_short_descr的client.client_id））为Total_Client_Count_
从
$ b dbo.client客户端
加入
dbo.broker客户端__经纪人
上（client.broker_id = client__broker.broker_id）
加入
dbo.ia_code client__iacode
在（client.ia_code_id = client__iacode.ia_code_id）
加入
dbo.client_document client_document
于（client.client_id = client_document.client_id）
加入
dbo.client_status client_status
on（ client.client_status_id = client_status.client_status_id）
加入
dbo.provider client__provider
on（client__provider.provider_id = client.provider_id）
）
左外部加入
dbo.branch client__iacode__branch
on（client__iacode.branch_id = client__iacode__branch.branch_id）
其中
（client_document.requested_date不为NULL）和
（client_document.received_date为NULL）和
（client__iacode__branch.branch_descr = CAST（@BRANCH AS varchar（255）））和
（client__broker.broker_code ='CCC'）和
（client_status.client_status_code ='A'）和
（（client__provider.provider_code<> 'PRS-R'）或（client__provider.provider_code为NULL））
组，由
client__iacode.ia_code，
client__iacode.ia_short_descr）L）L1
--filter
-（按{COUNT（client__iacode.ia_code在client__iacode.ia_code上的client.client_code，client__iacode.ia_code为client__iacode.ia_code）上的（rank（）over（按client__iacode.ia_code的client.client_code））最后降序）≤25）和
-（ RCOUNT（rank（）over（按client__iacode.ia_code的顺序按XCOUNT（client__iacode.ia_code的client_document.client_document_id降序为空））按client__iacode.ia_code的rank（）按XCOUNT（client__iacode.ia_code的顺序按XCOUNT（client_document.client_document_id用于client__iacode.ia_code）desc null null）asc，client__iacode.ia_code asc，client__iacode.ia_short_descr asc）< = 25）
其中L1。[Rank]< = 25
和L1.Condition< = 25
由
排序Rank_IA asc，
IA_Code asc，
IA_Short_Descr asc
您能帮我在过滤器中应用该过滤器吗e Microsft SQL Server代码。
预先感谢您的帮助。解决方案Cognos BI使用动态查询模式；默认情况下（从10.2.1开始）。它基本上是Cognos的查询引擎。引擎计划执行，该执行可以包括在从数据源接收到数据之后本地处理数据。您可以在IBM的免费红皮书中阅读所有相关内容
http://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf
因此，这意味着本地查询并不完整图片。从MS SQL接收数据后，Cognos可能会进行更多处理。话虽如此，如果您想在MS SQL中复制相同的查询，则需要自己完成缺失的逻辑。如果将Cognos中的多维模型按1：1映射到MS SQL关系数据库，那么这应该很容易（将丢失的部分从Cognos SQL自己转换为MS SQL）。很多时候，并不是很直接b / c Cognos内部的模型可能不是1：1映射到MS SQL表/列。在这种情况下，将Cognos查询转换为MS SQL查询b / c时，您将需要了解Cognos内部的模型，这是本机查询和Cognos查询之间发生的事情的一部分。
我可以对于您的模型部分没有任何帮助，但对于其余部分，您可以首先尝试更改查询（或数据源）的汇总处理，属性数据库。这应该有助于将扩展的聚合函数（XCOUNT等）转换为本机SQL。还要检查使用SQL参数属性并将其设置为文字并查看这是否会对本机SQL中的参数有所帮助。
rank（）函数的语法与MS SQL中的语法几乎相同，因此，一旦保重，您应该能够轻松地处理该问题。功能扩展。但是我先来看一下新的本机查询。
RCount（运行计数）也可以使用窗口函数在MS SQL中轻松处理（请参见下面的链接）。但是再次，首先生成新的本机查询，然后查看RCount是否消失。
I'm trying to convert IBM Cognos SQL which contains a filter to Microsoft SQL Server Query.
I tried to apply the filter in the Microsoft SQL Server Query, but it didn't work.
Here the IBM Cognos Query:
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 ascHere the Native SQL Query from the IBM Cognos report studio:
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)Here the Microsoft SQL Server Query that I have created so far but it didn't shows me the expected result:
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 ascCan you please help me to apply that filter in the Microsft SQL Server code. Thanks in advance for the help.
解决方案Cognos BI uses "dynamic query mode" by default (starting with 10.2.1). It's basically Cognos' query engine. The engine plans an execution which may include processing data locally after it is received from the data source. You may read all about it in IBM's free redbook here
http://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf
Here is a quick overview of the engine
So, that means the native query is not the whole picture. Cognos may do more processing after receiving the data from MS SQL. That being said, if you would like to replicate the same query in MS SQL, you will need to complete the missing logic yourself. If the multi-dimensional model in Cognos is mapped to MS SQL relational database on 1:1, then that should be easy (convert missing parts from Cognos SQL to MS SQL yourself). Lots of the time, it is not that straight forward b/c the model inside Cognos may not be 1:1 mapping to MS SQL tables/columns. In that case, you will need to understand the model inside Cognos when converting Cognos query to MS SQL query b/c that's part of what happens between native query and Cognos query.
I can't be of any help on your model part but for the rest, you can 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.
Screenshot of the properties window
The rank() functions syntax is pretty much the same syntax in MS SQL so you should be able to handle that easily once you took care of extended functions. But I would look at the new native query first.
RCount (running count) can also be easily handled in MS SQL using windowing functions (see link below). But again, first generate the new native query and see if RCount is gone or not.
这篇关于将包含过滤器的IBM Cognos SQL转换为Microsoft SQL Server查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
