SqlServer outer join error 1

[naga5566]

Hi All,
I am having problem using outer join in BO(SQlserver database).
I have 3 table say A,B and C and the joins between them are

A left outer join B
and B inner join C

This is throwing error in BO when i run the query.
table b is a member of outer join and cannot participate in
regular join.

This is not a free hand sql report,it is deisgned at the universe evel.Is there any setting(Outerjoin_generation) that need to be made in the .prm file of the sqlserver ? However this kind of join works perfect in Oracle.

Thanks
Naga

 

[skrandel]

You need to edit the universe parameters. If you are on V6.x you can do this in File Parameters. On the Parameters tab, set ANSI = Yes.


This should help:

http://www.forumtopics.com/busobj/viewtopic.php?t=25083

Steve Krandel
VERITAS Software

 

[naga5566]

Hi Steve
i am working on 517 ver of BO.Do i need to enter ANSI=Yes in prm file or modify it ? I mean i could nt find any parameter "ANSI" in the prm file.
Thanks for your help.

 

[skrandel]

For 5.1.x, find the appropriate PRM file. Make sure you have lines like:

OUTERJOINS_GENERATION=FULL_ODBC
OUTERJOINS_COMPLEX=Y

Your outerjoins will do what you want.

Steve Krandel
VERITAS Software

 

[naga5566]

Steve,
It is generating the Sql properly to an extent.The only thing i dont understand is it is also generating "*" along with the outer join syntax and the result is the query returns below error.
"
[Microsoft][ODBC SQL Server Driver][SQL Server]Outer join operators cannot be specified in a query containing joined tables.1016 "

Below is the SQl it is generating(Line 7 in the below Sql
is the *)

SELECT
A.ColX,
B.ColY,
C.ColZ
FROM
{ oj A LEFT OUTER JOIN B ON (
A.col1 *=B.col1 )
INNER JOIN C ON ( B.Col2=C.Col2 )
}


Thanks Once again
Naga

 

[naga5566]

Steve,
I got it.
I removed the lines
LEFT_OUTER=$*
RIGHT_OUTER=*$ and it is working perfect.
thanks for all the help
Naga