challeges after union , in identifying the tables that used for union..

[venkys4u_]

challeges with union and identifying the tables..

hi, i have table1,table2 with same structure.. i need
(table1 union table2 = table3 )


table 1
ID  ename
1   bil   
2   kil
3   nil


table 2
ID  ename
6   sil   
7   til
9   pil

after union i need to identify which records comes from which table.. for this i created a calculted data item that generates table_ID and tried
and expected like

table1
ID  ename  table_id
1   bil   1
2   kil   1
3   nil   1


table2
ID  ename  table_id
6   sil    2
7   til    2
8   pil    2



table 1 union table 2 = table 3 (expected result)

table3
ID  ename  table_id
1   bil     1
2   kil     1
3   nil     1
6   sil     2
7   til     2
8   pil     2

where the table_id is data item created in report studio
but even the table2 doesnt have data I am getting result like,

table3
ID  ename table_id
1   bil   1
2   kil   1
3   nil   1
1   sil   2
2   til   2
3   pil   2

It means even the table 2 doesnt have data, after union the resultant table 3 gives
records of table 1 with both the table ID 1 and 2.. simply to say data issue..
any other ways to identify which records come from which table after union?

thanks in advance!
-venky

[jive]

Did you check the option of the union. I replicate your table here and everything works find.
I create two table
I create two query on on each table test it
Create a union adding the previous query in the source select for the union
Add the column name I want to see in the table resulting form the union
In the union properties I choose preserver duplicate, it's like a 'union all'

Everything works find in the first, I can replicate your problem. sorry