J.Col
J.Col

Reputation: 41

Oracle / PLSQL: ORA-00933 error at SELECT statement

I'm creating that query. Where do I set 'SELECT'?

 (SELECT COUNT(B.USER_ID) 
    FROM USER_LIST B, USER_INFO A  
    WHERE B.PHONE_NUM not in ('11100000') 
    AND B.USER_ID = A.USER_ID) USERCOUNT, 

    (SELECT COUNT(B.USER_ID) FROM USER_LIST B, USER_INFO A  
    AND B.PHONE_NUM in ('11100000')
     AND B.USER_ID = A.USER_ID) BLACKUSERCOUNT

Oracle / PLSQL: ORA-00933 Error Message..

But it is working if I run one by one. How to merge that query?

Upvotes: 1

Views: 122

Answers (2)

Littlefoot
Littlefoot

Reputation: 142710

Another option would be this: it'll show two rows of data, each one's first column (whose label is what) shows what it represents.

SELECT 'usercount' what, COUNT (b.user_id) cnt
  FROM user_list b JOIN user_info a ON a.user_id = b.user_id
 WHERE b.phone_num NOT IN ('11100000')
UNION ALL
SELECT 'blackusercount' what, COUNT (b.user_id)
  FROM user_list b JOIN user_info a ON a.user_id = b.user_id
 WHERE b.phone_num IN ('11100000');

Or, perhaps even better, save some CPU by querying the table only once as

SELECT SUM (CASE WHEN b.phone_num NOT IN ('1110000') THEN 1 ELSE 0 END) usercount,
       SUM (CASE WHEN b.phone_num     IN ('1110000') THEN 1 ELSE 0 END) blackusercount
  FROM user_list b JOIN user_info a ON a.user_id = b.user_id;

Upvotes: 0

Ori Marko
Ori Marko

Reputation: 58774

You can wrap statement using the following:

start with

  select

And end with

 from dual

Upvotes: 2

Related Questions