BusinessObjects Board

Why two SQL queries??

building semantic layer
#1

Hi,

I have a Class- COA. In that i have 5 objects. 4 objects are coming from one table (Tab-1) and 1 object from another table(Tab-2).

I joined both of them based on proper columns values.

When i drag 5 objects into report it showing data in two tables in report and preparing two SQL statements for the same. Why??

I don’t know where i went wrong, please teach me…

Thanks

BOCP (BOB member since 2007-07-02)

#2

Are Tab-1 and Tab-2 related in any way?
Are they joined?
Are they joined in the same context more specifically?

#3

This sounds like incompatible dimensions.

BOCP - do you have common dimensionality in your tables?
And have you set up the contexts?

SandiR :uk: (BOB member since 2004-12-20)

#4

Hi

Its look the object from the second table belongs to different context and its generating different sql for that object.

Regards
Siva.M

looksmee :uk: (BOB member since 2006-02-08)

#5

It could also be that they have Generate a seperate SQL statment for each measure, under universe properties?

Mak 1 :uk: (BOB member since 2005-01-06)

#6

2 sql queries isn’t a big issue, but incompatible dimension would cause the data to be split into 2 tables.

SandiR :uk: (BOB member since 2004-12-20)

#7

Always wondered why it was the default :shock: !

Agreed - the two tables bit is actually the key, in this instance - Doh!

Mak 1 :uk: (BOB member since 2005-01-06)

#8

Thank you all…

  1. I joined those two tables.
  2. No Context defined
  3. No where they were used(In other contexts also), am sure, beacuse i took aliases for those tables and changed object definitions and joined properly. (Because of that doubt).

Incompatable objects?? Why??

Tables Description:

Tab-1 has:
1.Acc No
2.Description
3.Class
4.Type
5.Curreny (Not used as Object)

Tab-2 has:
1.Code
2.Description

Join: Tab-1.Curreny=Tab-2.Code
To get description for all currency/codes from Tab-2.

Object Description:

Acc No ==> Tab-1.AccNo
Description ==> Tab-1.Description
Class==> Tab-1.Class
Type==>Tab-1.Type

Currency==>Tab-2.Description

My problem:

After aliasing them and repointing the changed tables in object definitions also am getting two SQL statements, why??

Please help me out…

Thanks

BOCP (BOB member since 2007-07-02)

#9

Can you copy the generated sql into here please?

SandiR :uk: (BOB member since 2004-12-20)

#10

Hi Sandi…,Thanks!

Direct queries only. See below…

Select1:

Select
Tab-1 AccNo,
Tab-1 Description,
Tab-1 Class,
Tab-1 Type
From Tab-1

Select2:
Select
Tab-2 Description
from
Tab-2

What went wrong?? Please…,

Thanks

BOCP (BOB member since 2007-07-02)

#11

After adding the join have you re-exported the universe and come out of the report and gone back in to pick up the universe changes? :?:

In terms of the SQL you just posted, is that what was generated when you simply included all five objects in the query results?

#12

So what does this mean - are there contexts in the Universe?

dessa :madagascar: (BOB member since 2004-01-29)

#13

The actual sql as generated cut & pasted may be better to understand.

But as Mark said - have you re-exported your universe and refreshed your query.

If you have aliased your tables - can you double check that you have changed your objects to reference the alias tables.

SandiR :uk: (BOB member since 2004-12-20)

#14

@ Mark:
–> Re-exported… YES
–> In terms of SQL… YES

@Dessa:
–> YES, other contexts are there.

@Sandi:

–> Posted query is same as generated only. Unfortunately, i can’t do copy & paste from remote desktop.

–> Re-exported and tried on new query only.

–> I repointed all object definitions to aliased tables.

Still am facing the same…

Is other Contexts making problem??

Thanks

BOCP (BOB member since 2007-07-02)

#15

The reason I asked about C&P the SQL is that the code you posted doesn’t seem to refer to an alias table.

I would have expected to see
select … from

SandiR :uk: (BOB member since 2004-12-20)

#16

I think one golden rule has been forgotton :).

Every join must belong to a context, once you start using them, this isn’t the case here, is it?

Try the FAQ:-

https://bobj-board.org/t/15227/3

Mak 1 :uk: (BOB member since 2005-01-06)

#17

Are all the five objects dimensions?

#18

I would say so. and as suggested read the FAQ.

Sorry, actually the other contexts aren’t making the problem, the fact that you have joins not in a context is making the problem.

dessa :madagascar: (BOB member since 2004-01-29)

#19

Yes, Isolated join was the problem.
I added them to new context and now i can see one SQL select.

Yes, all are dimensions. Any issues ??

Thank You All.

BOCP (BOB member since 2007-07-02)

#20

No, just checking :smiley: