How to define an object

[Former Member]

Hi all,

I am trying to all 2 new tables into my universe. The 2 tables are joined by the column1 and column2. I want a condition, say if table1.column1=table2.column2, for the newely added objects from these 2 tables. So, I have written the satement in the where clause of the object.

But if I use the objects as report objects the SQL has where clause twice It looks like

select column3 from table1 where table1.column1=table2.column2(table1.column1=table2.column2).

Nevertheless, if I remove the where clause from the object definition the the SQL in the query panel has no where clause. The SQL just looks like

select column3 from table1

Regards

[Former Member · Accepted solution]

Hi Ash,

Since the two tables are joined by column1 and column2, it is not required to write same join condition i.e table1.column1=table2.column2 in the objectu2019s where clause.

Assume there are 2 objects, object1 based on table1.column3 and object2 based on table2.column4

u2022 When only object1 is selected in report then the join definition table1.column1=table2.column2 wonu2019t come in SQL. The SQL will be like :

Select column3 from table1

u2022 When only object2 is selected in report then again the join definition table1.column1=table2.column2 wonu2019t come in SQL. The SQL will be like :

Select column4 from table2

u2022 When object1 and object2 both are selected in report then the join definition table1.column1=table2.column2 Will come in SQL. The SQL will be like :

Select column3, clumn4 from table1, table2 where table1.column1=table2.column2

To force u201Ctable1.column1=table2.column2 u201C condition when only u201Cobject1u201D is selected , follow steps :

u2022 Open the Edit Properties window of the Object1

u2022 Click the u201CTablesu201D button, table list will appear

u2022 Select both the tables, table1 and table2 and click ok.

u2022 Save the object1.

u2022 Now when only object1 is selected the SQL will be :

Select column3 from table1, table2 where table1.column1=table2.column2

Hope this information will help you.

Thanks,

Sushil

[Former Member · Accepted solution]

Hi Sushil,

Thankyou it really helped

Edited by: Ash Pitt on Jul 24, 2008 1:15 AM