Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
2 replies Latest Post - ‏2013-06-13T09:56:53Z by Venk@tVelisetty
Venk@tVelisetty
Venk@tVelisetty
2 Posts
ACCEPTED ANSWER

Pinned topic look up query for Cognos

‏2013-05-31T13:30:56Z |

Hi All,

i have the one querie regarding report studio, could please try to understand and suggest .

For example, This is data showing the the table emp,

Empno      Ename    dept

100            Bob           IT

100                              IT

101           Pra            Admin

101                             Admin

102           Biju           Hr

102                             Hr

 

My expected output is :

Empno      Ename    dept

100            Bob           IT

100            Bob           IT

101           Pra            Admin

101           Pra            Admin

102           Biju           Hr

102           Biju           Hr

 

Emp Query :

 In the Emp query contains these three columns and applied filters like (dept in('it','admin','hr) and ename is not null)

Look info query :

In these query i taken these three query items and applied filter dept in('it','admin','hr) only. In these query, Ename  contains null values and not null values.

 

Final qyery :

I joined these two query based on the emp code and dept and assigned these query to final report. For this output i already tested the data for different types joins (1:1-1:1,1:n-1:n,0:n-0:n...)

However i did't get the correct output.Please you guys have any ideas regarding this please advise .

 

 

 

 

 

 

 

 

  • This reply was deleted by BVNChowdary 2013-06-04T09:11:46Z.
  • BVNChowdary
    BVNChowdary
    3 Posts
    ACCEPTED ANSWER

    Re: look up query for Cognos

    ‏2013-06-04T09:22:20Z  in response to Venk@tVelisetty

    Hi Venkat,

    do a self join to the table and bring emp name from sub query where emp name is null..

    SEL T1.EMP_NO, COALESCE(T1.ENAME,T2.ENAME) AS ENAME, T1.DEPT_NAME FROM EMP_TABLE T1

    INNER JOIN ( SEL * FROM EMP_TABLE WHERE ENAME IS NOT NULL ) T2

    ON T1.EMP_NO=T2.EMP_NO

    AND T1.DEPT_NAME=T2.DEPT_NAME

    Updated on 2013-06-04T09:26:42Z at 2013-06-04T09:26:42Z by BVNChowdary
    • Venk@tVelisetty
      Venk@tVelisetty
      2 Posts
      ACCEPTED ANSWER

      Re: look up query for Cognos

      ‏2013-06-13T09:56:53Z  in response to BVNChowdary

      If you don't mind ,

      Could you please elaborate more ?

      In database , may be  we can get the output, I already tried this way in cognos. However i did't get the correct output.