How to pass the output of the subquery into the main query so it could count the items from a separate table

Question

Below is what I am trying to achieve. I have a database 'CARS_DATABASE', wherein there are the following tables:

Lorries Vans Buses Convertibles City_cars Stretch_limos Rescue_vehicles Double_decker_buses

First of all, I am going to run the following query to get the list of the tables:

SELECT TableName
 FROM 
 (SELECT TableName
 FROM DBC.TablesV
 WHERE DatabaseName = 'CARS_DATABASE';

Now, I would like to incorporate it into one query which would look as follows:

Select count(*) from CARS_DATABASE.**TableName**
 WHERE TableName = (SELECT TableName
 FROM 
 (SELECT TableName
 FROM DBC.TablesV
 WHERE DatabaseName = 'CARS_DATABASE') Q1);

I wonder how I can make this dynamic and pass into FROM clause the names of all tables. Essentially, I would like it to select from all those tables dynamically, e.g.

Select count(*) from CARS_DATABASE.Lorries
Select count(*) from CARS_DATABASE.Vans
Select count(*) from CARS_DATABASE.Buses

and so on.

Do I have to write a macro or stored procedure to achieve that?

Many thanks in advance for your suggestions.

Cheers Andy

I'd consider one common table, `vehicles`, for lorries, vans, and buses. And cars. — jarlh, Aug 20 '20 at 12:59
Or at least create a view that UNION ALLs the separate tables. — jarlh, Aug 20 '20 at 13:10
Only way I know of to use dynamic sql in Teradata is a stored procedure. — Andrew, Aug 20 '20 at 13:29
See https://stackoverflow.com/q/18135793/2527905 or https://stackoverflow.com/a/28638552/2527905 — dnoeth, Aug 20 '20 at 14:18

score 0 · Answer 1 · answered Aug 20 '20 at 13:13

Have a derived table (the subquery), where you SELECT and UNION ALL the different vehicle tables. GROUP BY the result.

select vehicle_type, count(*)
from
(
    select 'lorries' as vehicle_type from CARS_DATABASE.Lorries
    union all
    select 'vans' as vehicle_type from CARS_DATABASE.vans
    union all
    ...
    select 'Double_decker_buses' as vehicle_type from CARS_DATABASE.Double_decker_buses
) vehicles
GROUP BY vehicle_type

Note: I'd store all vehicles in one common table. If you're stuck with separated tables, you could create a view to UNION ALL the different tables.

How to pass the output of the subquery into the main query so it could count the items from a separate table

1 Answers1