我有以下查詢，有一個子查詢可以從雙中選擇。主查詢和子查詢的結果都需要顯示在輸出中。
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';
執行查詢時ORA-00933: SQL command not properly ended遇到近線錯誤) AS A。
uj5u.com熱心網友回復：
在您修復語法錯誤（EXISTS不是EXIST，缺少右大括號并且不要使用AS關鍵字作為表別名）后，您的外部查詢是
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)
c外部查詢中沒有表或別名，因為子查詢的別名為anotc并且子查詢只包含一row_exists列而不是一CLIENT_ID列；所以在這兩點上，c.CLIENT_ID都是無效的。
你可能想要的是這樣的：
如果要檢查client_id匹配當前行：
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);
或者，如果您想檢查client_id回傳的行集中是否匹配：
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);
或者，如果您想檢查表中的client_id任何位置是否存在clients：
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);
取決于您想如何檢查客戶端是否存在。
uj5u.com熱心網友回復：
exists， 不是exist- 缺少結束子查詢括號
- 表別名不接受
as關鍵字（與列相反）
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';
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/373096.html