CODEWITHSUNDEEP
sqldb2
Ananya Gupta
Ananya Gupta

Reputation: 23

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

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 ?

Upvotes: -1

Views: 198

Answers (3)

Mark Barinstein
Mark Barinstein

Reputation: 12314

You must use dynamic sql.
Note, that you can't use "SELECT to nowhere" in a compound statement in Db2. That is, the following code is erroneous.

BEGIN
  SELECT * FROM MYTAB;
END@

This is why you need to store the result of SELECT somewhere. You may use Global Temporary Tables for that presuming, that USER TEMPORARY TABLESPASE is available to use for your user.

--#SET TERMINATOR @
BEGIN
  DECLARE V_STMT VARCHAR (500);
  
  SELECT
     'DECLARE GLOBAL TEMPORARY TABLE SESSION.RESULT'
  || ' AS (SELECT * FROM '
  || MAIN
  || ') WITH DATA WITH REPLACE '
  || 'ON COMMIT PRESERVE ROWS NOT LOOGED'
  INTO V_STMT
  FROM XYZ
  -- place your WHERE clause here if needed
  FETCH FIRST 1 ROW ONLY
  ;
  
  EXECUTE IMMEDIATE V_STMT;
END
@

SELECT * FROM SESSION.RESULT
@

dbfiddle link.

Upvotes: 1

Nate1zn
Nate1zn

Reputation: 242

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

Upvotes: 0

user3408245
user3408245

Reputation:

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]

Upvotes: 0

Related Questions