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
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