sql - 在主选择和子选择中添加引用另一个表的查询
问题描述
我有一个用于显示目标和完成时间的表,所有信息都来自一个表“job”-但是我现在想添加更多 SQL 以提供来自另一个表“job_type”的一些信息,但是每当我不断收到错误消息时说“SQLSTATE = S0022 [Oracle][ODBC][Ora]ORA-00904:“JOB_TYPE”。“JOB_TYPE_CODE”:标识符无效。
我拥有的代码(没有我想要添加的代码)
select
job_number, priority_code, job_entry_date, clock_start,
site_code,
TO_CHAR(job_entry_date, 'Dy') as DAY_LOGGED,
TO_CHAR(actual_start_date, 'IW') as WEEK_ON_SITE,
actual_start_date,
actual_comp_date,
-- kpi tracker
CASE
WHEN actual_start_date is null AND current_date < target_time THEN 'Not arrived on site yet'
WHEN actual_start_date is null AND current_date > target_time THEN 'FAIL (2)'
when actual_start_date <= target_time then 'Pass'
when actual_start_date > target_time then 'FAIL'
ELSE 'Overdue' END as Arrived_on_time_check,
-- start of correct target time SQL
case
when to_char(target_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
and floor((target_time - trunc(target_time)) * 24) >= 17
then target_time + 2 + 63/24
when floor((target_time - trunc(target_time)) * 24) >= 17
then target_time + 15/24
else target_time
end as target_time
from
(
select job_number, priority_code, job_entry_date, clock_start, site_code, actual_start_date, actual_comp_date,
CASE
WHEN PRIORITY_CODE IN ('GC01','GC02','GC03','GC04','GC05','GC06','GC07')
THEN
clock_start + case priority_code
when 'GC01' then 1
when 'GC02' then 2
when 'GC03' then 0.5
when 'GC04' then 1
when 'GC05' then 2
when 'GC06' then 4
when 'GC07' then 24
end / 24
ELSE TARGET_COMP_DATE END as target_time
from
(
select job_number, priority_code, job_entry_date, target_comp_date, site_code, actual_start_date, actual_comp_date,
case
when to_char(job_entry_date, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
and floor((job_entry_date - trunc(job_entry_date)) * 24) >= 17
then trunc(job_entry_date) + 80/24
when to_char(job_entry_date, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Sat'
then trunc(job_entry_date) + 56/24
when to_char(job_entry_date, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Sun'
or floor((job_entry_date - trunc(job_entry_date)) * 24) >= 17
then trunc(job_entry_date) + 32/24
when floor((job_entry_date - trunc(job_entry_date)) * 24) < 8
then trunc(job_entry_date) + 8/24
else job_entry_date
end as clock_start
from job
)
)
where
priority_code in ('GC01','GC02','GC03','GC04','GC05','GC06','GC07')
我要添加的代码是:
-- JOB KPI ATTRIBUTE LOOK UP IN GJOB TABLE
(SELECT attribute_value.attrib_value_name FROM attribute_value WHERE
attribute_value.attrib_type_code = 'GJOB'
AND attribute_value.attrib_value_code = job_type.job_type_code) as JOB_KPI,
-- JOB KPI PENALTY POINTS LOOK UP IN GJOB TABLE
(SELECT attribute_value.attrib_value_nom FROM attribute_value WHERE
attribute_value.attrib_type_code = 'GJOB'
AND attribute_value.attrib_value_code = job_type.job_type_code) as KPI_POINTS,
如何将上述代码添加到现有代码中而不会出现错误消息指出它是无效标识符?
解决方案
推荐阅读
- azure - Azure Policy 检查空值
- javascript - 如何修复错误:ExpressionChangedAfterItHasBeenCheckedError zorro modal in angular
- python - 在 Python 中格式化 x 和 y 轴的数据(刻度)
- python - 错误---只有整数、切片 (`:`)、省略号 (`...`)、numpy.newaxis (`None`) 和整数或布尔数组是有效的索引
- android - 我们如何在 android 中修改我的应用程序中的网络类型?
- c++ - 从 doIt 函数调用函数对象
- mysql - 从 table1 中选择不在 table2 中的列
- android - 找不到依赖项“com.google.android.gms:play-services-maps:17.0.0”
- jsf - 属性无法解析为 bean (JSF) 的成员
- flutter - 如果我们的小部件树中只有 StatelessWidget,我们就不需要 Keys 了吗?