CODEWITHSUNDEEP
sqloracle-database
user2762553
user2762553

Reputation: 19

Oracle Query fetching table name alongwith column name

select trx_id,refernce number from
(select * from abcd_1_txt union
select * from abcd_2_txt union
select * from abcd_3_txt union
select * from abcd_4_txt)
where trx_id in (123,321,1234)

In the query all the tables are of same format, same column names and same number of columns. After running this query, surely i will get some data. My question --- is there any way to know from which of these tables, i am getting the output.

Upvotes: 1

Views: 350

Answers (1)

Robert
Robert

Reputation: 25753

Try to add a column with number of query as below 

select qrynum, trx_id,refernce number from
(select 1 as qrynum,* from abcd_1_txt union
select 2,* from abcd_2_txt union
select 3,* from abcd_3_txt union
select 4,* from abcd_4_txt)
where trx_id in (123,321,1234)

as Joe W said in the comment below you can also use name of the table instead of query number, short example:

select tabname, trx_id,refernce number from
(select 'abcd_1_txt' as tabname,* from abcd_1_txt union
...
where trx_id in (123,321,1234)

but both ways don't eliminate duplicates, so you can use union all instead of union. Other way to do that is to run quires separately with the condition 

 select * from abcd_1_txt where trx_id in (123,321,1234)
 select * from abcd_2_txt where trx_id in (123,321,1234)
  .
  .
  .

Upvotes: 2

Related Questions