CODEWITHSUNDEEP
mysqlsqldb2
TheFaster
TheFaster

Reputation: 241

Converting mySQL queries with subqueries to DB2 SQL query with subqueries

I'm pretty unfamiliar with DB2 SQL sever. I'm trying to query for a total rowCount from multiple tables, like so:

SELECT
 SUM(COUNT)
  AS ROWS
 FROM
 (SELECT COUNT(*) AS COUNT FROM TABLE1
 UNION ALL
SELECT COUNT(*) AS COUNT FROM TABLE2
 UNION ALL
SELECT COUNT(*) AS COUNT FROM TABLE3
 UNION ALL
//ETC...
)

But it keeps flagging an syntax error on ")". How would I restructure this?

Upvotes: 0

Views: 59

Answers (1)

paul
paul

Reputation: 22001

DB2 (and SQL Server) require that subqueries are aliased, so:

SELECT
 SUM(COUNT)
  AS ROWS
 FROM
 (SELECT COUNT(*) AS COUNT FROM TABLE1
 UNION ALL
SELECT COUNT(*) AS COUNT FROM TABLE2
 UNION ALL
SELECT COUNT(*) AS COUNT FROM TABLE3
 UNION ALL
//ETC...
) as subqueryAlias

Upvotes: 1

Related Questions