CODEWITHSUNDEEP
sqloracle11goracle-sqldeveloper
wviana
wviana

Reputation: 1957

How to select the using table name into a select?

I'm using this command to take the C_DIV column from all these tables.

SELECT DISTINCT C_DIV FROM TBL_TASK UNION
SELECT DISTINCT C_DIV FROM TBL_DATAMART_SUMMARY UNION
SELECT DISTINCT C_DIV FROM MMS_DIVI

and a lot of more others tables.

from this output I get just the C_DIV column, with all the information that I needed.

I would like to know if there is some way to get a column with the of each using table, so instead of getting just the column C_DIV, I would get C_DIV and TABLE_FROM

Exemple

C010    TBL_TASK 
C017    TBL_TASK 
C020    TBL_TASK 
C025    TBL_DATAMART_SUMMARY 
C027    TBL_DATAMART_SUMMARY 
C028    TBL_DATAMART_SUMMARY 
C034    TBL_DATAMART_SUMMARY 
C035    TBL_TASK 
C037    TBL_TASK
C050    MMS_DIVI

Upvotes: 0

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Yes, you can just add it into the union:

SELECT DISTINCT C_DIV, 'TBL_TASK' as which FROM TBL_TASK UNION ALL
SELECT DISTINCT C_DIV, 'TBL_DATAMART_SUMMARY' FROM TBL_DATAMART_SUMMARY UNION ALL
SELECT DISTINCT C_DIV, 'MMS_DIVI' FROM MMS_DIVI;

Note that I also changed the union to union all. There cannot be duplicates between the different subqueries, so there is no reason to incur the overhead of removing the duplicates. Union all is more efficient.

Upvotes: 1

Twelfth
Twelfth

Reputation: 7190

Just the table name? I'd hard code it in each union:

SELECT DISTINCT C_DIV , 'TBL_Task' as tablename FROM TBL_TASK UNION
SELECT DISTINCT C_DIV , 'TBL_DATAMARTSUMMARY' FROM TBL_DATAMART_SUMMARY UNION
SELECT DISTINCT C_DIV , 'MMS_DIVI' FROM MMS_DIVI

Upvotes: 0

Related Questions