Convert IBM Cognos SQL which contains a filter to Microsoft SQL Server Query

Question

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 asc

Here 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 asc

Can you please help me to apply that filter in the Microsft SQL Server code. Thanks in advance for the help.

score 1 · Answer 1 · answered Jul 21 '20 at 12:26

1

When generating the SQL on a Cognos query it will display both the Cognos SQL and the Native SQL. The Cognos SQL is what is required for the report and the Native SQL is what is being sent down to the database. If there is no local processing and the two SQL types are identical within reason, you should be able to use the SQL from the Native SQL dialog box without any edits.

answered Jul 21 '20 at 12:26

Daniel Wagemann

741
4
6

The Cognos report use the Filters window. That's why the filters do not appear in the native SQL code. – Newbie SQL Jul 21 '20 at 13:01
1

Not sure what you mean, are you running the report then filtering in the interactive viewer? – Daniel Wagemann Jul 21 '20 at 13:19
Please check the picture that I uploaded few minutes ago. Do you have any idea how to replace the running-count in SQL native query? Please let me know and thanks in advance. – Newbie SQL Jul 21 '20 at 13:36
1

Probably should have asked this earlier, what Cognos product are you using? – Daniel Wagemann Jul 21 '20 at 14:30
I'm using: IBM Cognos Report Studio Version 10.2.2 – Newbie SQL Jul 21 '20 at 14:35
1

The only reason why items would not be in the native SOL is because the product thinks it needs to do it local, possibly due to the fact that the DB doe not support it. I'd have to see the native and cognos SQL for the report side by side. – Daniel Wagemann Jul 21 '20 at 17:00
Do you have any idea how to replace the rcount in SQL native query? – Newbie SQL Jul 21 '20 at 17:05
1

Looking at https://stackoverflow.com/questions/17892574/calculating-a-running-count-running-total-across-customers-with-sql to see if this helps – Daniel Wagemann Jul 21 '20 at 17:19

score 1 · Answer 2 · edited Jul 28 '20 at 00:39

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.

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

score 0 · Answer 3 · answered Jul 21 '20 at 13:29

0

Here where the filters have been created. The filters is not showed in the Native SQL Query.

answered Jul 21 '20 at 13:29

Newbie SQL

122
13

Convert IBM Cognos SQL which contains a filter to Microsoft SQL Server Query

3 Answers3

Linked