Author Topic: SQL query into Cognos report  (Read 516 times)

Offline chinnucognos

  • Community Leader
  • *****
  • Join Date: Nov 2013
  • Posts: 92
  • Forum Citizenship: +0/-0
SQL query into Cognos report
« on: 02 Aug 2017 03:33:50 am »
Hello Techies,

I have a query which actually looks like
SELECT c1,c2,c3,c4,(select max(date_column) from T5 where T5.F1=T1.F1 AND T5.F2=T1.F2) as c5, c6
from T1 INNER T2 T1.F1=T2.F1 AND  T1.F2=T2.F2
            LEFT JOIN T2.F1=T3.F1 AND T2.F2=T3.F2
            LEFT JOIN T3.F1=T4.F1 AND T3.F2=T4.F2
I can able to do necessary joins in report studio but I have struck how to apply/add column which consists of select stmt as given above [b](i.e. select max(date_column) from T5 where T5.F1=T1.F1 AND T5.F2=T1.F2 as c5)[/b][/color]


Please give your thoughts on this

Thanks in advance
« Last Edit: 02 Aug 2017 03:41:58 am by chinnucognos »
Deal with it!

Offline angro

  • Associate
  • **
  • Join Date: Aug 2017
  • Posts: 3
  • Forum Citizenship: +0/-0
Re: SQL query into Cognos report
« Reply #1 on: 03 Aug 2017 01:29:12 am »
Hi, I will use WITH clause

WITH a as (select max(date column) c5 from T5, T1 where T5.F1=T1.F1 AND T5.F2=T1.F2)
SELECT c1,c2,c3,c4, c5, c6
from T1 INNER T2 T1.F1=T2.F1 AND  T1.F2=T2.F2
            LEFT JOIN T2.F1=T3.F1 AND T2.F2=T3.F2
            LEFT JOIN T3.F1=T4.F1 AND T3.F2=T4.F2

Offline chinnucognos

  • Community Leader
  • *****
  • Join Date: Nov 2013
  • Posts: 92
  • Forum Citizenship: +0/-0
Re: SQL query into Cognos report
« Reply #2 on: 03 Aug 2017 01:49:53 am »
Hi Angro,

thanks for your response .
Here I am looking to convert select query into cognos report using components like query,joins in report studio.
 Now, my question is how to convert sub query into column in below existing query

SELECT c1,c2,c3,c4,(select max(date_column) from T5 where T5.F1=T1.F1 AND T5.F2=T1.F2) as c5, c6
from T1 INNER T2 T1.F1=T2.F1 AND  T1.F2=T2.F2
            LEFT JOIN T2.F1=T3.F1 AND T2.F2=T3.F2
            LEFT JOIN T3.F1=T4.F1 AND T3.F2=T4.F2
Deal with it!

Offline angro

  • Associate
  • **
  • Join Date: Aug 2017
  • Posts: 3
  • Forum Citizenship: +0/-0
Re: SQL query into Cognos report
« Reply #3 on: 03 Aug 2017 03:21:59 am »
Hi, you have to build some Queries.
query_1 for table T5 , Query 2 for table T2 (from Toolbox) and then join  them (as query_3) with 'Join  relationsship'. Pay attention on cardinality. To Query_3 drag only date_columnn from query_1 and for this in Properios choose for Aggregation Function - Max

Then You have to create the other queries from T3, T4 and one by one join tchem


Offline chinnucognos

  • Community Leader
  • *****
  • Join Date: Nov 2013
  • Posts: 92
  • Forum Citizenship: +0/-0
Re: SQL query into Cognos report
« Reply #4 on: 09 Aug 2017 08:29:50 am »
Hi Angro, I am pretty confused in your logic.

 Let me explain, what and where I struck

SELECT DISTINCT  f1,
                 f2,
             f3,
                (SELECT MAX( f_date ) FROM p_table
                                 WHERE p_table.c1 = q_table.c1 AND
                         p_table.c2 = q_table.c2 AND
                         p_table.c3 = q_table.c3 AND
                         p_table.c4 = q_table.c4 AND  AS f4,
             f5,
             f6
FROM q_table INNER JOIN r_table ON  q_table.c2 = r_table.c2 AND    -- created joins using query explorer(Q1 join Q2=Q3)
             INNER JOIN s_table ON  q_table.c2 = s_table.c2 AND    -- created joins using query explorer (Q3 join Q4= Q5)
             LEFT OUTER JOIN  t_table ON  q_table.c2 = t_table.c2  -- created joins using query explorer (Q5 join Q6='Final Report Query')
             
Now i struck where to join/create 'f4' column ?
Deal with it!

Offline Invisi

  • Community Leader
  • *****
  • Join Date: Sep 2016
  • Posts: 208
  • Forum Citizenship: +4/-2
    • Invisi - Vision on Information
Re: SQL query into Cognos report
« Reply #5 on: 17 Aug 2017 04:50:16 am »
Consider you can speak from the desired result, not from how you retrieve the data to get to your result. Are you going to show data in a list, or is it for a graph of some sort?
Few can be done on Cognos | RTFM for those who ask basic questions...

 


       
Twittear