I’m now on project to convert from BO 3.1 to BO 4.1 and have unclear question.
My Universe after convert have no error and didn’t change anything.
I create new report (in BO 4.1) base on that universe and check the report SQL and find out that SQL had changed like below:
3.1: SELECT TBL1.field1, TBL2.field2 FROM TBL1 LEFT OUT JOIN TBL2 ON tbl1.field1 = tbl2.field2.
4.1: SELECT TBL1.field1, TBL2.field2 where tbl1.field1 = tbl2.field2.
I don’t know what is the main course of this problem. help me please.
Have a look at the universe properties in Designer. To get joins in LEFT OUTER format, you need the ANSI92 property set. It may be that converting the universe has lost this setting.
@VSD: Universe are same (parameters, tables, joins definition…).
I were checked parameter ANSI92 in 3.1 and 4.1 and both of them are “No”.
When I change ANSI92 value to “Yes” in BO 4.1 then report’s SQL has changed from SELECT TBL1.field1, TBL2.field2 where tbl1.field1 = tbl2.field2 to SELECT TBL1.field1, TBL2.field2 from tbl2.field1 RIGHT JOIN tbl1.field2. a little change with BO 3.1 but logic doesn’t change.
I don’t know why, maybe it is database involve because we change database too: from DB2 to Oracle. But I still don’t understand this situation when I have to change parameter for same join in difference BO. If it must to done then I have to re-check all universe I had converted
Please check if there are any configurations while creating connection to oracle e.g. while creating odbc DSN to SQL Server, we have option to use ANSI.
The difference hgas always been that the parameters in the universe only affect that universe.
Conversely, the prm will affect all universe on the RDBMS you are changing it for, Oracle etc.
Unsure whether one would cancel the other one out.
In any case it is something worth checking.
The universe level parameter for ANSI_92 generation was introduced later than the prm level option. I would expect the universe level parameter to overrule the prm file but I’m happy to be proved wrong.
I read that with ANSI not setting (= “No”?) then joins will be created using Oracle-specific syntax. that maybe what Mark said.
But as I said, in Universe joins setting hasn’t change from 3.1 to 4.1 so why report in 3.1 has join with correct outer join but in 4.1 it doesn’t? It’s only thing now I still not understand. I can’t take a hand on database setting so I will ask DB team for more detail.
OK, I think we misunderstand here, I convert universe from 3.1 to 4.1 but still keep using unv file not convert to unx file so your suggest thread not work for me Mark :).
and I do understand setting parameter ANSI92 will work (actually it maybe the only way I have now). What I don’t understand here is why in BO 3.1 I don’t need to set that parameter but outer join still correct while BO 4.1 don’t
If you don’t set the parameter in the universe, it will take the parameter in the prm for the connection type - if you’re connecting to Oracle in 3.1 and have ANSI_92 set in the universe it will use the universe parameter. If you don’t have the parameter set in 4.1 then it will use the prm file.
The thing is 3.1 database is DB2 :D. and prm file are keep default setting when installed.
I look at some onther universe in 4.1 and find out that if it is simple join then (+) symbol will show up in right way but with complex join it is not. I think when I create a complex join, although outer join check box are disable but 3.1(DB2) still understand, 4.1(Oracle) don’t.
Anyway, if prm file setting not work I have to set ANSI92 = Yes for every universes
@Mark P: the old system(BO3.1) is using DB2 and new one(BO4.1) using Oracle. I have trouble in new system where universe’s SQL generated without Outer Join.
@Mak 1: Yes, I were restarted connection servers .
(BOB member since 2013-01-11)
(BOB member since 2005-03-01)
(BOB member since 2003-11-27)
(BOB member since 2002-09-13)
.