How to write a query which selects from a table that is returned by another query-CodePudding

I have table which has basically 2 rows containing the name of failure and the main table i want to write a query such that

Select main 
from xyz

will return the table name like abc.

Now I want to get the data from the abc table

Select * 
from 
    (select main 
     from xyz)

which returns abc.

How can I write it ?

CodePudding user response：

Here is a solution on stack that shows how to get the table names from your database

DB2 Query to retrieve all table names for a given schema

Then you could take your failure table and join into it based off of the table name, that should match your errors to the table that match on the table name. I'm not a 100% sure of your question but I think this is what you are asking.

The inner system query has schema and name. Type is T for table. See IBM link below for column reference. You could run the query wide open in the inner query to look for the tables you want. I would recommend using schema to isolate your search.

https://www.ibm.com/docs/en/db2-for-zos/11?topic=tables-systables

SELECT
ft.*
, st.*
FROM [FailureTable] as ft
INNER JOIN 
(
     select * from sysibm.systables
    where CREATOR = 'SCHEMA'
    and name like '%CUR%'
    and type = 'T'

) st
ON st.[name] = ft.[tablename]

CodePudding user response：

You can try

DECLARE @tableName VARCHAR(50);
SELECT @tableName = main
FROM xyx

EXEC('SELECT * FROM '   'dbo.'   @tableName)

Dont forget to add validation if @tableName doesnt get populated