0

I have the following queries which work fine alone:

SELECT COUNT(u.id) FROM users u);
SELECT COUNT(s.id) FROM servers s);

I want to use them in the same query, so I created this:

SELECT CAST( MULTISET( SELECT count(u.id) FROM users u)
             AS NUMBER) AS nr_users,
       CAST( MULTISET( SELECT count(s.id) FROM servers s)
             AS NUMBER) AS nr_servers
FROM dual;

And its returning the error:

ORA-00932: inconsistent datatypes: expected NUMBER got -00932.

count() returns a number, and I specified the multiset as number, so why is it giving me this error?

Alexandru Severin
  • 6,021
  • 11
  • 48
  • 71

1 Answers1

1

The sub-queries don't return collection types but scalars, just use

SELECT
    (SELECT count(u.id) FROM users u) AS nr_users,
    (SELECT count(s.id) FROM servers s) AS nr_servers
FROM dual;
Husqvik
  • 5,669
  • 1
  • 19
  • 29