Skip to Content
0

WebI Rich Client 4.1 SP7: outer joins not generated

Mar 21, 2017 at 02:23 PM

84

avatar image

Env.: BO BI 4.1 SP7 on Windows

Hello,

I don't get the same generated SQL query with Webi applet and Webi Rich Client (WIRC).

Outer joins are well generated with Webi applet but not with WIRC.

The used database is "MS SQL Server 2014" via OLE DB.

Where can I set a parameter to activate outer joins with WIRC ?

I tried to "play" with oledb.prm file on client side but without any success. :(

Any suggestion ?

Thanks.

Regards,

Stéphane.

10 |10000 characters needed characters left characters exceeded

can you share what is the difference in SQL?

0
* Please Login or Register to Answer, Follow or Comment.

5 Answers

PETIT Stéphane Mar 21, 2017 at 05:05 PM
0
WEBI Applet :  OK => GOOD OUTER JOIN
===========

SELECT
  PIB."Année",
  PIB.Trimestre,
  PIB."Code qualité",
  Codes_qualite."Libellé code qualité",
  sum(PIB."PIB-CVS-CJO")
FROM
  Codes_qualite RIGHT OUTER JOIN PIB ON (PIB."Code qualité"=Codes_qualite."Code qualité")
  
GROUP BY
  PIB."Année", 
  PIB.Trimestre, 
  PIB."Code qualité", 
  Codes_qualite."Libellé code qualité"



WIRC 2-tier MODE : BAD JOIN !
================

SELECT
  PIB."Année",
  PIB.Trimestre,
  PIB."Code qualité",
  Codes_qualite."Libellé code qualité",
  sum(PIB."PIB-CVS-CJO")
FROM
  PIB,
  Codes_qualite
WHERE
  ( PIB."Code qualité"=Codes_qualite."Code qualité"  )
GROUP BY
  PIB."Année", 
  PIB.Trimestre, 
  PIB."Code qualité", 
  Codes_qualite."Libellé code qualité"




WIRC 3-TIER MODE : OK => GOOD OUTER JOIN
================

SELECT
  PIB."Année",
  PIB.Trimestre,
  PIB."Code qualité",
  Codes_qualite."Libellé code qualité",
  sum(PIB."PIB-CVS-CJO")
FROM
  Codes_qualite RIGHT OUTER JOIN PIB ON (PIB."Code qualité"=Codes_qualite."Code qualité")
  
GROUP BY
  PIB."Année", 
  PIB.Trimestre, 
  PIB."Code qualité", 
  Codes_qualite."Libellé code qualité"



The example above shows that it's a client side problem because OUTER JOIN is well generated with WIRC in 3-TIER MODE.

I guess there is something to do with "oledb.prm" file.

I already tried but perhaps not with the good one or I have to copy in a specific folder I don't know...

Did anybody meet such a problem ?


Share
10 |10000 characters needed characters left characters exceeded
Mahboob Mohammed Mar 21, 2017 at 05:11 PM
0

Hi Petit,

Do you have the parameter ANSI92 set to Yes in the Universe?

Thanks,
Mahboob Mohammed

Share
10 |10000 characters needed characters left characters exceeded
PETIT Stéphane Mar 21, 2017 at 05:21 PM
0

Hi,

It is set to NO.

Could it be the problem ?

Stéphane (my first name)

Share
10 |10000 characters needed characters left characters exceeded
Mahboob Mohammed Mar 21, 2017 at 05:33 PM
0

Hi Stephane,

Just so you know, if you set ANSI92 = Yes, then you'll be able to use Full Outer Join. Actually, the ANSI92 may be set to Yes, I say that because they joins are spelled out in full as Right Outer Join, Inner Join etc.

So, the issue here, I think is, you didn't import the updated universe from Webi RC. Launch Webi, click on Log in as on top right and login using the credentials, and again, below Log in as, you'll find an option called Import Universe, click that, and in the window (that pops up), refresh the list, select the universe, click import on bottom right. Voila. Let me know if that helps.

Thanks,
Mahboob Mohammed


Share
10 |10000 characters needed characters left characters exceeded
PETIT Stéphane Mar 22, 2017 at 03:33 PM
0

Hello,

Alas, nothing works (ANSI92 in Universe parameters, import Univers inWIRC...). :(

NB: it's a .unv Universe (not .unx) created with UDT..

Moreover, I set to YES all OUTER_JOIN or similar parameters in all "oledb.prm" and "sqlsrv.prm" files under

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\oledb

and subfolders without any success in WIRC in 2-TIER MODE.

It makes me crazy ! :(

Regards,

Stephane.

Share
10 |10000 characters needed characters left characters exceeded