cancel
Showing results for 
Search instead for 
Did you mean: 

The order of joining the tables in a query ANSI92

Former Member
0 Kudos

Hi all!

I have three table in Univerce.

I need that those tables joined in next order:

tab1 inner join tab2 right join tab3

When creating a report in WebI they are connected as (regardless of the order of dimentions):

tab1 right join tab3 inner join tab2

How to set the correct order of joining the tables?

-


OR

how to change RIGHT OUTER JOIN on LEFT OUTER JOIN ?

P.S. I use BO XI3.1, which not have parameter OUTERJOINS_GENERATION

Thanks!

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

to Amr Salem

This query is not working properly.

Must be:

SELECT
volume.ID_SUBS,
dat.DT,
volume.VOLUME,
subs.SUBS_NAME
FROM
volume,dat,subs
where volume.DT = dat.DT(+)
and subs.ID_SUBS(+) = volume.ID_SUBS

but I can not do so

look link with my problem

to Simon To

Thanks!

It`s realy change RIGHT JOIN to LEFT JOIN,

but not help me

My issue is http://dl.dropbox.com/u/9343091/Issue.rar

Former Member
0 Kudos

I have next tables:

http://dl.dropbox.com/u/9343091/snap.PNG

I need next queries:

select

dat.dt

, tab.subs_name

, tab.volume

from

(

select volume.dt, volume.volume, subs.subs_name

from volume, subs

where volume.id_subs = subs.id_subs

) tab

, dat

where dat.dt = tab.dt(+)

Thanks!

-


Or how to make a left join of two queries in WEBi?

Edited by: Nikita Galitch on Aug 26, 2010 2:15 PM

amrsalem1983
Active Contributor
0 Kudos

try this

SELECT
volume.ID_SUBS,
dat.DT,
volume.VOLUME,
subs.SUBS_NAME
FROM
volume,dat,subs
where volume.DT = dat.DT(+)
and subs.ID_SUBS = volume.ID_SUBS

Former Member
0 Kudos

I need used only ANSI92

Although, if you tell how to link a table in a subquery, it solved my problem (Derived Table not be used).

Example:

select

..

from

(Select .. from tab1, tab3 where ..) t

, Tab2

where ..

amrsalem1983
Active Contributor
0 Kudos

i dont know much about ANSI92

and also i need more clarification about your last question (linking tables)

what do you want to do exactly, tell me a scenario

thanks

Former Member
0 Kudos

Hi Nikita, if you want to force ANSI SQL and Outer Joins, you can modify or add the values of the following parameters on the universe to accomplish that:

SORT_JOINS_BY_QUERY_OBJECTS=Yes

ANSI_92=Yes

OUTERJOINS_GENERATION=Yes

Hope this helps.

-


If you are using the BusinessObjects tool, you should join [ASUG|www.asug.com]

amrsalem1983
Active Contributor
0 Kudos

it depends on your source system

for example if you are using oracle, you can edit the link between the table and add this

(+)

for example

TAB1.ID = TAB2.ID (+)
AND TAB1.DEPT  = TAB2.DEPT(+)

See the following link for discussing outer and inner joins for oracle

http://www.dba-oracle.com/t_outer_joins.htm

good luck

Amr