带有 ORDER BY 和 UNION 的 ORACLE 查询 [英] ORACLE Query with ORDER BY and UNION
问题描述
我有以下查询效果很好!
I have the following Query which works great!
唯一的问题是我需要 DESC Order 中的结果集,并且附加到查询末尾的以下 ORDER BY 子句会生成以下 Oracle 错误:
The only thing is that I need the result set in DESC Order and the following ORDER BY Clause that I have appended to the end of my Query generates the following Oracle Error:
ORA-00933:SQL 命令没有正确结束00933. 00000 - SQL 命令未正确结束"*原因:
*行动:行错误:46 列:54
ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 46 Column: 54
这是查询.同样,此查询有效.只是当我添加以下行时:
Here is the Query. Again, this query works. It is just that when I add the following line:
FINAL_SEARCH order by FINAL_SEARCH.DOC_HDR_ID desc
一切都坏了.
有什么想法吗?我相信我的 ALIAS 与它有关,但我可能是错的.
Any ideas? I believe my ALIAS has something to do with it, but I could be wrong.
查询 -
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;
更新 - 我删除了别名并在两个查询之后放置了ORDER BY"语句,它在 SQL Developer 中工作;但是,当我通过使用 Java 生成此查询的 Web 应用程序执行此查询时,我收到有关无效符号的 ORACLE 错误.无效符号是一个分号,用于终止第一个 ORDER BY 语句.它不会在 SQL Developer 中以其他方式工作,但显然当我执行通过 Web 应用程序生成的相同语句时 - 它失败了.
UPDATE - I removed the ALIAS and placed the 'ORDER BY' Statement after both queries and it worked in SQL Developer; however, when I execute this Query through the Web Application that generates this query using Java, I get a ORACLE ERROR about an INVALID SYMBOL. The invalid symbol is a semi-colon that use to terminate the 1st ORDER BY Statement. It won't work otherwise in SQL Developer, but apparently when I execute that same statement that I generate through the Web App - it fails.
推荐答案
您需要计算您的括号 - 合并的两个查询应该在一个括号内 - 然后您的别名和 SELECT * FROM 将起作用.
You need to count your brackets - the two queries being unioned should be in one bracket - then your alias and SELECT * FROM will work.
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;
这篇关于带有 ORDER BY 和 UNION 的 ORACLE 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!