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.

CodePudding user response：

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.

CodePudding user response：

• 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';