sql - ORA-00913: oracle 子查询中的值太多
问题描述
我需要捕获所有提供给候选人的工作机会以及候选人最终被录用的职位。我创建了两个单独的查询,一个捕获所有报价,一个子查询仅捕获每个候选人最终被聘用的工作。当我结合两个查询时出现问题,我得到 ORA-00913: too many values。有什么建议么?
SELECT DISTINCT h.pi_candidate_num,
h.parent_pi_number,
h.associate_name,
j.job_family_name,
j.organization_name,
j.title,
e.contest_number,
e.pi_his_itm_app_trk_creation_dt offer_date
FROM wc_hired_interns_fa h
INNER JOIN wc_application_event_f e
ON h.pi_candidate_num = e.pi_candidate_num
INNER JOIN wc_job_information_d j
ON e.job_info_row_wid = j.row_wid
WHERE e.pi_his_itm_app_trk_creation_dt IN
(SELECT DISTINCT e.pi_his_itm_app_trk_creation_dt
FROM wc_application_event_f e
INNER JOIN wc_hired_interns_fa h
ON h.pi_candidate_num = e.pi_candidate_num
INNER JOIN wc_job_information_d j
ON e.job_info_row_wid = j.row_wid
WHERE j.job_family_name IN ('MDP', 'ELP', 'Emerging Leader Program', 'Other')
AND e.pi_his_itm_app_trk_sts_name = 'Extended'
AND e.pi_his_itm_app_trk_step_name = 'Offer'
AND j.title NOT IN ('Student Ambassador Program for Eligible Summer Interns', 'Student Ambassador'))
AND j.title IN (SELECT *
FROM (SELECT DISTINCT j.title full_time_detail,
rank() over(PARTITION BY e.pi_candidate_num ORDER BY e.pi_his_itm_app_trk_creation_dt DESC) AS rowrank
FROM wc_hired_interns_fa h
INNER JOIN wc_application_event_f e
ON h.pi_candidate_num = e.pi_candidate_num
INNER JOIN wc_job_information_d j
ON e.job_info_row_wid = j.row_wid
AND j.job_family_name IN ('MDP', 'ELP', 'Emerging Leader Program', 'Other')
AND e.pi_his_itm_app_trk_sts_name = 'Hired - External'
AND e.pi_his_itm_app_trk_step_name = 'Hire'
AND j.title NOT IN ('Student Ambassador Program for Eligible Summer Interns', 'Student Ambassador'))
WHERE rowrank = 1);
解决方案
眼前的问题似乎是这部分:
...
AND J.TITLE IN (
SELECT * FROM (
SELECT DISTINCT J.TITLE FULL_TIME_DETAIL,
RANK () OVER (PARTITION BY E.PI_CANDIDATE_NUM ORDER BY E.PI_HIS_ITM_APP_TRK_CREATION_DT DESC) AS ROWRANK
FROM WC_HIRED_INTERNS_FA H
...
这SELECT *
意味着子查询返回两个列值,FULL_TIME_DETAIL
并且ROWRANK
. 不过,您实际上并不想要ROWRANK
那里,因此将 更改为*
您想要的列:
...
AND J.TITLE IN (
SELECT FULL_TIME_DETAIL FROM (
SELECT DISTINCT J.TITLE FULL_TIME_DETAIL,
RANK () OVER (PARTITION BY E.PI_CANDIDATE_NUM ORDER BY E.PI_HIS_ITM_APP_TRK_CREATION_DT DESC) AS ROWRANK
FROM WC_HIRED_INTERNS_FA H
...
您正在执行大量子查询并重复大量代码,因此这可能会大大简化;也许研究分析函数。不过,这超出了这个问题和具体错误的范围。
推荐阅读
- javascript - 如果一个精灵完全在视图之外,它还会被渲染吗?
- pyspark - 使用 PySpark 而不使用窗口对来自 Kafka 的流数据执行滚动平均
- performance - Perl,如何让这个脚本更小、更快、更好、更强?
- java - 如何在新的独立泛型类中重用另一个文件中的代码?
- c# - Microsoft Forms 停靠在左上角。C#
- python - Selenium:如何从“find_elements”列表中单击单个项目
- react-native - 反应原生buttons.slice不是一个函数
- python - 何时在 multiprocessing.Pool 上使用 multiprocessing.Queue?什么时候需要使用 multiprocessing.Queue?
- python - 在文件 (10MB+) 中搜索出现在相似上下文中的关键字(<512 字长)
- swiftui - 如何解决“不接受通知”。您可以稍后在 iOS 设置的问题下打开它们?