0

Recently started working on Cognos and I am facing quite a few challenges. Though some of them I was able to rectify, with some of them I'm just not able to understand the problem. I am creating a report in Cognos where I used a SQL code to join 4 tables. Two of the data items in that query are Primary_Officer_ID and Relationship_Officer_ID Have another table, with field name Emp_ID and Emp_Names. Note that the Emp_ID is same as the officer’s numbers in the Primary_Officer_ID and Relationship_Officer_ID. I want a report which gives the Names of the officers in place of their IDs. Below are the 4 queries used

  • Query1: SQL code
  • Query2: Emp_ID, Emp_Names
  • Query3: Left outer join for Primary_Officer_ID
  • Query4: Left outer join for Relationship_Officer_ID

The final dataset used is Query 4. As it includes all the joins and SQL code When I run the report, it only gives me names for Primary_Officer_ID and not for Relationship_Officer_ID. Please suggest what shall I do differently to make the report run successfully. Attached is the snapshot of Query Explorer which will help to understand the scenario:

Snapshot of Query Explorer

Excited_to_learn
  • 361
  • 3
  • 11
  • 25
  • This all should be taken care of in the framework manager model. You will add significantly to your development time by writing custom SQL and doing joins in the reports. – toddsonofodin May 01 '14 at 12:16
  • I agree...But I don't have access to framework manager model. and will have to do it manually to get it working :( – Excited_to_learn May 01 '14 at 20:18

1 Answers1

0

I suppose you should create another query EmpNames1 (copy of EmpNames) and use it in second join. You can check out generated SQL for "Relationship Officer Join" to see the what's wrong.

Alexey Baturin
  • 1,173
  • 1
  • 7
  • 11
  • I tried that option as well...But doesn't work. I want the code to look like this ' FROM Main_Query left outer join Primary_Officer_Join on (Main_Query.Primary_Officer_ID = Emp_ID) left outer join Relationship_Officer_Join on (Main_Query.Relationship_Officer_ID = Emp_ID)' But it doesn't generate this code. Can I edit the generated code by adding this query? Or what else I can do to get two left outer join for two ID's? – Excited_to_learn May 01 '14 at 20:24
  • Also, pls note that I am using two different data source.Is that the reason I am not able to join queries. Main query has one data source and EmpNames has a different data source. Please suggest the possible solution. Appreciate your help – Excited_to_learn May 01 '14 at 20:44
  • You can't edit generated SQL. And your code ' FROM Main_Query left outer join Primary_Officer_Join on (Main_Query.Primary_Officer_ID = Emp_ID) left outer join Relationship_Officer_Join on (Main_Query.Relationship_Officer_ID = Emp_ID)' means 'Main_Query.Primary_Officer_ID = Main_Query.Relationship_Officer_ID'. – Alexey Baturin May 02 '14 at 02:24
  • oh...I tried writing that code in SQL server and it did give me the desired output. After performing left outer join for both the tables, I tried using Union as well...but didn't work :( – Excited_to_learn May 02 '14 at 19:23