COGNOiSe.com - The IBM Cognos Community

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]


Please give your thoughts on this

Thanks in advance

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

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

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

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 ?

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?