使用ORDER BY和UNION的ORACLE查询
使用ORDER BY和UNION的ORACLE查询
提问于 2012-08-10 02:15:31
我有下面的查询，它工作得很好！
唯一需要的是，我需要DESC Order格式的结果集，并且我附加到查询末尾的以下ORDER BY子句将生成以下Oracle错误：
ORA- 00933 : SQL命令未正确结束00933。00000 - "SQL命令未正确结束“*原因：
*操作:第46行错误第54列
下面是查询。同样，这个查询可以正常工作。只是当我添加下面这行代码时：
FINAL_SEARCH order by FINAL_SEARCH.DOC_HDR_ID desc所有的东西都坏了。
有什么想法吗？我相信我的化名与此有关，但我可能错了。
查询-
Select * from ( select DISTINCT(DOC_HDR.DOC_HDR_ID),
DOC_HDR.INITR_PRNCPL_ID,
DOC_HDR.DOC_HDR_STAT_CD,
DOC_HDR.CRTE_DT,
DOC_HDR.TTL,
DOC_HDR.APP_DOC_STAT,
DOC1.DOC_TYP_NM,
DOC1.LBL, DOC1.DOC_HDLR_URL,
DOC1.ACTV_IND
from KREW_DOC_TYP_T DOC1, KREW_DOC_HDR_T DOC_HDR
where DOC_HDR.INITR_PRNCPL_ID IN ( '10000000001' ) and
( DOC1.DOC_TYP_NM = 'PO' or
DOC1.DOC_TYP_NM = 'POA' or
DOC1.DOC_TYP_NM = 'POC' or
DOC1.DOC_TYP_NM= 'POPH' or
DOC1.DOC_TYP_NM ='PORH' or
DOC1.DOC_TYP_NM = 'POR' or
DOC1.DOC_TYP_NM = 'PORT' or
DOC1.DOC_TYP_NM = 'POSP' or
DOC1.DOC_TYP_NM = 'POV') and
DOC_HDR.DOC_HDR_STAT_CD!= 'I' and
DOC_HDR.DOC_TYP_ID = DOC1.DOC_TYP_ID )
UNION
Select DISTINCT DOC_HDR.DOC_HDR_ID,
DOC_HDR.INITR_PRNCPL_ID,
DOC_HDR.DOC_HDR_STAT_CD,
DOC_HDR.CRTE_DT,
DOC_HDR.TTL,
DOC_HDR.APP_DOC_STAT,
DOC1.DOC_TYP_NM,
DOC1.LBL,
DOC1.DOC_HDLR_URL,
DOC1.ACTV_IND
FROM KREW_DOC_TYP_T DOC1,
KREW_DOC_HDR_T DOC_HDR
WHERE DOC1.DOC_TYP_NM = 'PO' AND CAST(DOC_HDR.DOC_HDR_ID AS VARCHAR(14)) = ANY (
Select PPT.FDOC_NBR
FROM PUR_PO_T PPT, PL_PURCHASE_LOG_PO_T PPL
WHERE PPT.AP_PUR_DOC_LNK_ID = ANY (
Select PRT.AP_PUR_DOC_LNK_ID
FROM PUR_REQS_T PRT,
KREW_DOC_HDR_T DOC_HDR
WHERE CAST(DOC_HDR.DOC_HDR_ID AS VARCHAR(14)) = PRT.FDOC_NBR AND
DOC_HDR.INITR_PRNCPL_ID IN ( '10000000001' ) )) FINAL_SEARCH order by FINAL_SEARCH.DOC_HDR_ID desc;UPDATE -我删除了别名，并在两个查询之后放置了'ORDER BY‘语句，它在SQL Developer中起作用；但是，当我通过使用Java生成此查询的Web应用程序执行此查询时，我得到一个关于无效符号的ORACLE错误。无效符号是用于终止第一个ORDER BY语句的分号。它在SQL Developer中不能正常工作，但很明显，当我执行通过Web应用程序生成相同语句时-它会失败。
回答 4
Stack Overflow用户
回答已采纳
发布于 2012-08-10 02:25:04
你需要计算你的括号--两个被联合的查询应该在一个括号里--然后你的别名和SELECT * FROM就可以工作了。
SELECT *
FROM ( (SELECT DISTINCT (doc_hdr.doc_hdr_id),
doc_hdr.initr_prncpl_id,
doc_hdr.doc_hdr_stat_cd,
doc_hdr.crte_dt,
doc_hdr.ttl,
doc_hdr.app_doc_stat,
doc1.doc_typ_nm,
doc1.lbl,
doc1.doc_hdlr_url,
doc1.actv_ind
FROM krew_doc_typ_t doc1, krew_doc_hdr_t doc_hdr
WHERE doc_hdr.initr_prncpl_id IN ('10000000001')
AND (doc1.doc_typ_nm = 'PO'
OR doc1.doc_typ_nm = 'POA'
OR doc1.doc_typ_nm = 'POC'
OR doc1.doc_typ_nm = 'POPH'
OR doc1.doc_typ_nm = 'PORH'
OR doc1.doc_typ_nm = 'POR'
OR doc1.doc_typ_nm = 'PORT'
OR doc1.doc_typ_nm = 'POSP'
OR doc1.doc_typ_nm = 'POV')
AND doc_hdr.doc_hdr_stat_cd != 'I'
AND doc_hdr.doc_typ_id = doc1.doc_typ_id)
UNION
SELECT DISTINCT doc_hdr.doc_hdr_id,
doc_hdr.initr_prncpl_id,
doc_hdr.doc_hdr_stat_cd,
doc_hdr.crte_dt,
doc_hdr.ttl,
doc_hdr.app_doc_stat,
doc1.doc_typ_nm,
doc1.lbl,
doc1.doc_hdlr_url,
doc1.actv_ind
FROM krew_doc_typ_t doc1, krew_doc_hdr_t doc_hdr
WHERE doc1.doc_typ_nm = 'PO'
AND CAST (doc_hdr.doc_hdr_id AS VARCHAR (14)) =
ANY (SELECT ppt.fdoc_nbr
FROM pur_po_t ppt, pl_purchase_log_po_t ppl
WHERE ppt.ap_pur_doc_lnk_id =
ANY (SELECT prt.ap_pur_doc_lnk_id
FROM pur_reqs_t prt,
krew_doc_hdr_t doc_hdr
WHERE CAST (
doc_hdr.doc_hdr_id AS VARCHAR (14)
) = prt.fdoc_nbr
AND doc_hdr.initr_prncpl_id IN
('10000000001'))))
final_search
ORDER BY final_search.doc_hdr_id DESC;Stack Overflow用户
发布于 2012-08-10 02:26:04
我认为你使用了额外的括号')‘
Select * from ( select DISTINCT(DOC_HDR.DOC_HDR_ID),
DOC_HDR.INITR_PRNCPL_ID,
DOC_HDR.DOC_HDR_STAT_CD,
DOC_HDR.CRTE_DT,
DOC_HDR.TTL,
DOC_HDR.APP_DOC_STAT,
DOC1.DOC_TYP_NM,
DOC1.LBL, DOC1.DOC_HDLR_URL,
DOC1.ACTV_IND
from KREW_DOC_TYP_T DOC1, KREW_DOC_HDR_T DOC_HDR
where DOC_HDR.INITR_PRNCPL_ID IN ( '10000000001' ) and
( DOC1.DOC_TYP_NM = 'PO' or
DOC1.DOC_TYP_NM = 'POA' or
DOC1.DOC_TYP_NM = 'POC' or
DOC1.DOC_TYP_NM= 'POPH' or
DOC1.DOC_TYP_NM ='PORH' or
DOC1.DOC_TYP_NM = 'POR' or
DOC1.DOC_TYP_NM = 'PORT' or
DOC1.DOC_TYP_NM = 'POSP' or
DOC1.DOC_TYP_NM = 'POV') and
DOC_HDR.DOC_HDR_STAT_CD!= 'I' and
DOC_HDR.DOC_TYP_ID = DOC1.DOC_TYP_ID
UNION
Select DISTINCT DOC_HDR.DOC_HDR_ID,
DOC_HDR.INITR_PRNCPL_ID,
DOC_HDR.DOC_HDR_STAT_CD,
DOC_HDR.CRTE_DT,
DOC_HDR.TTL,
DOC_HDR.APP_DOC_STAT,
DOC1.DOC_TYP_NM,
DOC1.LBL,
DOC1.DOC_HDLR_URL,
DOC1.ACTV_IND
FROM KREW_DOC_TYP_T DOC1,
KREW_DOC_HDR_T DOC_HDR
WHERE DOC1.DOC_TYP_NM = 'PO' AND CAST(DOC_HDR.DOC_HDR_ID AS VARCHAR(14)) = ANY (
Select PPT.FDOC_NBR
FROM PUR_PO_T PPT, PL_PURCHASE_LOG_PO_T PPL
WHERE PPT.AP_PUR_DOC_LNK_ID = ANY (
Select PRT.AP_PUR_DOC_LNK_ID
FROM PUR_REQS_T PRT,
KREW_DOC_HDR_T DOC_HDR
WHERE CAST(DOC_HDR.DOC_HDR_ID AS VARCHAR(14)) = PRT.FDOC_NBR AND
DOC_HDR.INITR_PRNCPL_ID IN ( '10000000001' ) ))) FINAL_SEARCH order by FINAL_SEARCH.DOC_HDR_ID desc;删除第一个where条件的最后一个括号，然后尝试。还要在) FINAL_SEARCH order by的末尾再添加一个括号
Stack Overflow用户
发布于 2012-08-10 02:27:13
您忘记了一些括号：
Select * from ( select DISTINCT(DOC_HDR.DOC_HDR_ID),
DOC_HDR.INITR_PRNCPL_ID,
DOC_HDR.DOC_HDR_STAT_CD,
DOC_HDR.CRTE_DT,
DOC_HDR.TTL,
DOC_HDR.APP_DOC_STAT,
DOC1.DOC_TYP_NM,
DOC1.LBL, DOC1.DOC_HDLR_URL,
DOC1.ACTV_IND
from KREW_DOC_TYP_T DOC1, KREW_DOC_HDR_T DOC_HDR
where DOC_HDR.INITR_PRNCPL_ID IN ( '10000000001' ) and
( DOC1.DOC_TYP_NM = 'PO' or
DOC1.DOC_TYP_NM = 'POA' or
DOC1.DOC_TYP_NM = 'POC' or
DOC1.DOC_TYP_NM= 'POPH' or
DOC1.DOC_TYP_NM ='PORH' or
DOC1.DOC_TYP_NM = 'POR' or
DOC1.DOC_TYP_NM = 'PORT' or
DOC1.DOC_TYP_NM = 'POSP' or
DOC1.DOC_TYP_NM = 'POV') and
DOC_HDR.DOC_HDR_STAT_CD!= 'I' and
DOC_HDR.DOC_TYP_ID = DOC1.DOC_TYP_ID )
UNION
(Select DISTINCT DOC_HDR.DOC_HDR_ID,
DOC_HDR.INITR_PRNCPL_ID,
DOC_HDR.DOC_HDR_STAT_CD,
DOC_HDR.CRTE_DT,
DOC_HDR.TTL,
DOC_HDR.APP_DOC_STAT,
DOC1.DOC_TYP_NM,
DOC1.LBL,
DOC1.DOC_HDLR_URL,
DOC1.ACTV_IND
FROM KREW_DOC_TYP_T DOC1,
KREW_DOC_HDR_T DOC_HDR
WHERE DOC1.DOC_TYP_NM = 'PO' AND CAST(DOC_HDR.DOC_HDR_ID AS VARCHAR(14)) = ANY (
Select PPT.FDOC_NBR
FROM PUR_PO_T PPT, PL_PURCHASE_LOG_PO_T PPL
WHERE PPT.AP_PUR_DOC_LNK_ID = ANY (
Select PRT.AP_PUR_DOC_LNK_ID
FROM PUR_REQS_T PRT,
KREW_DOC_HDR_T DOC_HDR
WHERE CAST(DOC_HDR.DOC_HDR_ID AS VARCHAR(14)) = PRT.FDOC_NBR AND
DOC_HDR.INITR_PRNCPL_ID IN ( '10000000001' ) ))) FINAL_SEARCH order by FINAL_SEARCH.DOC_HDR_ID desc;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11889518
复制相关文章
相似问题
腾讯云开发者
