OuterJoin of Cognos 6

[Henrychen]

Question about Outerjoin:

I set outjoin in both Access db and oracle db, when I check the sql of both Query, it looks difference.

In Access 2000:
select T1.`Alert_Level`, T1.`Report_ID`, T1.`Subject`, T2.`Report_Type`
from (`NewMessage` T1 LEFT OUTER JOIN `ReportList` T2 on T1.`Report_ID` = T2.`Report_ID`)

In Oracle 8.1.7:
select T1."MANDT" "c1", T1."KUNNR" "c2", T2."SPART" "c3"
from "SAPR3"."KNA1" T1, "SAPR3"."TSPAT" T2
where T1."MANDT"=T2."MANDT"(+) and T1."SPRAS"=T2."SPRAS"(+)

So why in access it use the words Left outer join, but in oracle it use (+) as the signal of outer join?

Pls help

 

[RVLF]

The Left outer join word is the ANSI SQL syntax

so what's your problem ?

 

[Henrychen]

My situation is:

I am use oracle 7.3 as backend database and cognos 6 before.
When I upgrade cognos to version 7, I found those reports with outerjoin change the syntax to 'Left Outer join'. But oracle 7.3 could not understand that words!

When I use cognos 6. It use '(+)' instead of 'Left outer join'.


So my question is:
how to assign cognos 7 use the syntax as the previous version?

 

[astra1]

Hi,

By default the tool generates the sql which is for that perticular RDBMS. What you wanna say is that it is not generating the correct SQL (for Oracle) sounds peculiar.

Why dont you check the Database connection in 'Database Defination Manage' and check whether the connectivity is thru 'Oracle' and not by 'ODBC'. Sometimes if you are connected to the database from ODBC then these kinds of absurd things happen.

If however, the connection is perfect then you may try the 'edit query' option and edit the query

Vish

 

[Henrychen]

Hi,

I use connectivity 'Oracle'instead of 'ODBC', but the problem still occur.

Any other suggestion.

 

[astra1]

Hi,

What you can do for time being is to use the 'Edit Query' option

Vish

PS : Can you send the default query generated by the tool

 

[Henrychen]

Hi

But my customer could not accept the solution of edit query one by one.

The query in oracle 7.3:
select T1.`Alert_Level`, T1.`Report_ID`, T1.`Subject`, T2.`Report_Type`
from (`NewMessage` T1 LEFT OUTER JOIN `ReportList` T2 on T1.`Report_ID` = T2.`Report_ID`)

 

[sln007]

What your SQL will do is give you every row in the T1 table and it's related T2.'Report_Type'....if no related T2.'Report_ID' row is found in the T2 table, then the COGNOS condition of "(T2.'Report_ID' is missing)" would be TRUE.
So you would have an 'empty' cell in the row-column intersect where T1.'Report_ID' is not-found in the T2 table.
If you did not have the "LEFT OUTER JOIN" box checked under the T1 table in the catalog, COGNOS would only return rows where T1.'Report_ID' is found in the T2 table.

 

[Henrychen]

I know.

But why SQL sometimes use "LEFT OUTER JOIN", sometimes it use "(+)"?

 

[astra1]

Are you using the same catalog for both access and Oracle, and changing the DB whenever you need it??? If so, then please use different catalogs for different DBs

Vish

 

[Henrychen]

astra1,

I have mentioned the situation at Apr 2, 2003. Do you have any other idea about it?

 

[griffindm]

Henrychen,

Cognos dropped support for Oracle 7.3 a while back. It is definitely not in the listed supported environments for Series 7. It starts with Oracle 8.1.7. Support for Impromptu outer-join syntax is specified in the Cognos oracle support file, cogdmor.ini, found in the same bin directory that the application executable resides.

Looking at the contents of this file for both version 6 and 7, it looks like Impromptu should still use the (+) syntax for both though, all the way through Oracle 8.1. The ini file still has sections to support Oracle back to version 7.0, though Cognos does not officially support them.

What I would do in your position is to strongly encourage the client to upgrade to Oracle 8 or 9, as product support for 7.3 is rapidly becoming problematic. If you had to try to make this work in the meantime, I would experiment with modifying a copy (keep a clean backup) of the ini file above and perhaps using a more recent Oracle client side dll to see if you can get the behavior back to what it was before. No guarantees of success though.

Good luck.

Dave Griffin The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[Henrychen]

Thanks a lot, Dave.

So what you mean is: I try to install oracle client 8 on my pc and access to server oracle 7.3.

Do you have any other suggestion about change the ini file?

 

[benbotektips]

Sorry this is late, I just got to this forum.
In short, outer joins work by themselves in Oracle 7.3 and 8. Impromptu 6 worx fine with Oracle 7.3 outer joins. Impromptu 6 also worx fine with Oracle 8i outer joins. Impromtu 7 worx fine with Oracle 8i outer joins. So, the last permutation is where the problem is. Oracle 7.3 outer joins will simply not work on Impromptu 7 , because of the very problem of generated sql in words instead of (+).

Having said this, I just found this on Cognos's site. I dunno, my department just gave up and kept on Impromptu 6, until we upgraded to Oracle 8i:

Title: ORA-00928: missing SELECT key word
Product: Impromptu
Version: 7.0
Machine: PC
Document ID: 119319
Replaces:

[Go To Best Hit]
Oracle 9.0.1 client
Oracle 7.3.4 database

Description:

Unable to perform an outer join between two tables and then run a report that queries both these tables.

Error Message -

Error 239 DMS-E-General A general exception has occurred during operation prepare request with option.
ORA-00928 Missing select keyword


Solution:

Add the following entries to the [Exceptions Joins] section of the cogdmor.ini file.

Outer_Syntax=F
Inner_Col_Post="(+)"

Restart Impromptu. The query along with the outer join should now be working.

 

[bcking]

benbotektips,
We are currently using Oracle 7.3.4 and wanting to upgrade to Impromptu 7 (though some of our reports are being upgraded from Impormptu 4).

Taking your tip onboard I set about the task and have managed to upgrade some of our reports and catalogs so that they run fine with Imrpomptu 7 and Oracle 7.3.4

The changes that I made are below.

Cogdmor.ini [Exceptions General]
ADD Case_To_Decode = F
ADD Expression_In_In = T

Cogdmor.ini [Exceptions General ORACLE 7.3]
CHANGE Case_To_Decode =F

Cogdmor.ini [Exceptions Joins]
ADD Outer_Syntax = F
ADD Inner_Col_Post = "(+)"

Impromptu.ini [Query Options]
ADD Use automatic Oracle Decode = 0

The [Exception Joins] addition takes away the SELECT error, the other cahnges and additions help overcome some decoding differences between the Oracle versions.

Hope this helps.

Brett

 

[benbotektips]

Well, we're on oracle 8 and impromptu 6, so this ain't an issue for me anymore. We're going to Impromptu 7 by end of year.