Executing query (multiple/ subquery) in HIVE is giving Vertex Failed

I have a basic understanding of HIVE and SQL Query. So the query that i have has multiple individual query or you can say sub query. However, if i run 5-6 query together it is working fine but if i run the whole query (99 individual query) it is giving me Vertex Failed Error

Query:

Select "TABLE_NAME_2" as TBL_NAME,	(Select count(*)  from a_t2 where BDATE='2023-05-31') as  a,	(Select count(*)  from b_t2 where BDATE='2023-05-31') as  b,	(Select count(*)  from c_t2 where BDATE='2023-05-31') as  c 	UNION ALL
Select "TABLE_NAME_3" as TBL_NAME,	(Select count(*)  from a_t3 where BDATE='2023-05-31') as  a,	(Select count(*)  from b_t3 where BDATE='2023-05-31') as  b,	(Select count(*)  from c_t3 where BDATE='2023-05-31') as  c 	UNION ALL
Select "TABLE_NAME_4" as TBL_NAME,	(Select count(*)  from a_t4 where BDATE='2023-05-31') as  a,	(Select count(*)  from b_t4 where BDATE='2023-05-31') as  b,	(Select count(*)  from c_t4 where BDATE='2023-05-31') as  c 	UNION ALL
Select "TABLE_NAME_5" as TBL_NAME,	(Select count(*)  from a_t5 where BDATE='2023-05-31') as  a,	(Select count(*)  from b_t5 where BDATE='2023-05-31') as  b,	(Select count(*)  from c_t5 where BDATE='2023-05-31') as  c 	UNION ALL
Select "TABLE_NAME_6" as TBL_NAME,	(Select count(*)  from a_t6 where BDATE='2023-05-31') as  a,	(Select count(*)  from b_t6 where BDATE='2023-05-31') as  b,	(Select count(*)  from c_t6 where BDATE='2023-05-31') as  c 	UNION ALL
.
.
.
.
.
Select "TABLE_NAME_33" as TBL_NAME,	(Select count(*)  from a_t33 where BDATE='2023-05-31') as  a,	(Select count(*)  from b_t33 where BDATE='2023-05-31') as  b,	(Select count(*)  from c_t33 where BDATE='2023-05-31') as  c

Not sure if this is the limitation of HIVE DB or if i am missing something.

Note: I have also used

set tez.queue.name = APPNAME; 
set hive.execution.engine = tez; 

EDITED
Output i want is:

TBL_NAME     a    b   c
TABLE_NAME1  10   20  15
TABLE_NAME2  10   20  15
TABLE_NAME3  10   20  15
.
.
.
TABLE_NAME33  10   20  15

Thanks

i can give you some solution. Like @nbk said syntax is wrong and this kind of inline SQL doesnt wotk in hive. But this is going to be an expensive solution - seek for some partitions in this table and see if you can count partitioned rows.

Here is something you can try -

WITH 
TABLE_NAME_2_a as Select "TABLE_NAME_2" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from a_t2 ,
TABLE_NAME_2_b as Select "TABLE_NAME_2" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from b_t2 ,
TABLE_NAME_2_c as Select "TABLE_NAME_2" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from c_t2 ,
TABLE_NAME_3_a as Select "TABLE_NAME_3" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from a_t3 ,
TABLE_NAME_3_b as Select "TABLE_NAME_3" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from b_t3 ,
TABLE_NAME_3_c as Select "TABLE_NAME_3" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from c_t3 

--UNION for Table 2
select 
TABLE_NAME_2_a.TBL_NAME, TABLE_NAME_2_a.a, TABLE_NAME_2_b.b,TABLE_NAME_2_c.c
from 
TABLE_NAME_2_a,TABLE_NAME_2_b,TABLE_NAME_2_c 
where 
TABLE_NAME_2_a.TBL_NAME=TABLE_NAME_2_b.TBL_NAME and TABLE_NAME_2_a.TBL_NAME=TABLE_NAME_2_c.TBL_NAME 
union all 
--UNION for Table 3
select 
TABLE_NAME_3_a.TBL_NAME, TABLE_NAME_3_a.a, TABLE_NAME_3_b.b,TABLE_NAME_3_c.c
from       
TABLE_NAME_3_a,TABLE_NAME_3_b,TABLE_NAME_3_c 
where      
TABLE_NAME_3_a.TBL_NAME=TABLE_NAME_3_b.TBL_NAME and TABLE_NAME_3_a.TBL_NAME=TABLE_NAME_3_c.TBL_NAME 
 


</details>