Main select query together with subquery having "select * dual"

Question

I have the following query that having a sub-query to select from dual. Both result of main and subquery need to be display in the output.

SELECT
a.ROW_EXISTS AS CLIENT_EXIST,
c.AP_Before AS AP_before,
c.AP_TIMESTAMP AS AP_TIMESTAMP,
cd.AAM_FLAG AS AAM_FLAG,
cd.SSM_FLAG AS SSM_FLAG
FROM 
(
select
case when exist (select 1 from c.clients where client_id='c-001' then 'Y' else 'N' end as ROW_EXISTS
from dual
) AS a
INNER JOIN CLIENT_DYN cd ON c.CLIENT_ID = cd.CLIENT_ID
WHERE c.CLIENT_ID = 'c-001';

Error ORA-00933: SQL command not properly ended encounters near line ) AS A when execute the query.

Littlefoot · Answer 1 · 2021-12-02T08:47:34.957

exists, not exist
missing closing subquery bracket
table aliases don't accept the as keyword (as opposed to columns)

SELECT a.ROW_EXISTS AS CLIENT_EXIST,
       c.AP_Before AS AP_before,
       c.AP_TIMESTAMP AS AP_TIMESTAMP,
       cd.AAM_FLAG AS AAM_FLAG,
       cd.SSM_FLAG AS SSM_FLAG
  FROM client c
       INNER JOIN CLIENT_DYN cd ON c.CLIENT_ID = cd.CLIENT_ID
       CROSS JOIN (SELECT CASE
                             WHEN EXISTS
                                     (SELECT 1
                                        FROM clients c
                                       WHERE client_id = 'c-001')
                             THEN
                                'Y'
                             ELSE
                                'N'
                          END AS ROW_EXISTS
                     FROM DUAL) a
 WHERE c.CLIENT_ID = 'c-001';

thanks, I'm getting table or view does not exist, the table name in subquery already changed to clients c — user2102665, Dec 02 '21 at 08:36
It is the `clients` table you're referencing in `inner join` - it *doesn't exist* in that scope. You did use it deep in a subquery, but - that's not visible outside. You'll have to join it *again*. See edited code. — Littlefoot, Dec 02 '21 at 08:48

MT0 · Accepted Answer · 2021-12-02T08:51:18.073

After you fix the syntax errors (EXISTS not EXIST, missing closing brace and don't use the AS keyword for table aliases), your outer query is

SELECT <some columns>
FROM   (SELECT 'Y/N Value' AS row_exists FROM DUAL) a
       INNER JOIN client_dyn cd
       ON (c.CLIENT_ID = cd.CLIENT_ID)

There is no c table or alias in the outer-query as the sub-query is aliased to a not c and the sub-query only contains one row_exists column and not a CLIENT_ID column; so on both these points, c.CLIENT_ID is invalid.

What you probably want is something like:

If you want to check the client_id matches the current row:

SELECT CASE WHEN c.client_id IS NOT NULL THEN 'Y' ELSE 'N' END AS CLIENT_EXIST,
       c.AP_Before AS AP_before,
       c.AP_TIMESTAMP AS AP_TIMESTAMP,
       cd.AAM_FLAG AS AAM_FLAG,
       cd.SSM_FLAG AS SSM_FLAG
FROM   clients c
       RIGHT OUTER JOIN CLIENT_DYN cd
       ON (c.CLIENT_ID = 'c-001' AND c.CLIENT_ID = cd.CLIENT_ID);

or, if you want to check if the client_id matches in the set of returned rows:

SELECT CASE
       WHEN COUNT(CASE WHEN c.client_id = 'c-001' THEN 1 END) OVER () > 0
       THEN 'Y'
       ELSE 'N'
       END AS CLIENT_EXIST,
       c.AP_Before AS AP_before,
       c.AP_TIMESTAMP AS AP_TIMESTAMP,
       cd.AAM_FLAG AS AAM_FLAG,
       cd.SSM_FLAG AS SSM_FLAG
FROM   clients c
       INNER JOIN CLIENT_DYN cd
       ON (c.CLIENT_ID = cd.CLIENT_ID);

or, if you want to check if the client_id exists anywhere in the clients table:

SELECT CASE
       WHEN EXISTS(SELECT 1 FROM clients WHERE client_id = 'c-001')
       THEN 'Y'
       ELSE 'N'
       END AS CLIENT_EXIST,
       c.AP_Before AS AP_before,
       c.AP_TIMESTAMP AS AP_TIMESTAMP,
       cd.AAM_FLAG AS AAM_FLAG,
       cd.SSM_FLAG AS SSM_FLAG
FROM   clients c
       INNER JOIN CLIENT_DYN cd
       ON (c.CLIENT_ID = cd.CLIENT_ID);

Depending on how you want to check if the client exists.

Main select query together with subquery having "select * dual"

2 Answers2