更新表集列等于选择其中 id 在 oracle 中的查询之外匹配 [英] Update table set column equal to select where id matches outside of query in oracle
本文介绍了更新表集列等于选择其中 id 在 oracle 中的查询之外匹配的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这看起来很简单，但我似乎无法理解它......我正在尝试更新一个表，将列设置为等于查询的结果，该查询使用我正在更新的表中的 ID 列并使用 WITH() 子句.我也只想更新表中 Fall_Term = 'Fall 2019'
This seems simple but I can't seem to wrap my head around it... I am trying to update a table, set the columns equal to the result of a query which uses the ID column from the table which I am updating and uses a WITH() clause. I also only want to update the columns in my table where Fall_Term = 'Fall 2019'
我将提供我的伪代码和我讨厌的冗长的实际代码...
I will provide my pseudo code and my long nasty actual code...
注意:如果我手动输入 ID 列的值 - PERSON_SKEY，我的更新语句可以正常工作.例如，而不是:
NOTE: My update statement works perfectly fine if I manually enter values for the ID column - PERSON_SKEY. For example, instead of:
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY
这样做有效:
WHERE V.PERSON_SKEY = 12345
我的代码当前出现错误:
My code currently gets the error:
[Error] Execution (10: 27): ORA-00904: "SNP_FACULTY_CENSUS"."PERSON_SKEY": invalid identifier
这是我的伪代码..
update Table_A
set Column_A =
(select Column_B from Table_B
where TABLE_A.id = TABLE_B.id )
where Fall_Term = 'Fall 2019';
这是我的实际代码:
--UPDATE OIR TERMINAL DEGREE IN FALL 2019 SNAPSHOT
UPDATE SNP_FACULTY_CENSUS
SET OIR_TERMINAL_DEGREE = (
WITH HIGHES_DEG_VALUE AS (
SELECT max(D.OIR_DEGREE_HIERARCHY) AS MAX_DEG
FROM VPAA_PPAGGENL_FIS V
JOIN DIM_DEGREE_CROSSWALK D
ON V.VPAA_FACULTY_DEGREE_CODE =
D.VPAA_DEGREE_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY)
SELECT
CASE
WHEN MAX_DEG = 30
THEN --CHECK IF EXIST ON CROSSWALKS AND USE THAT VALUE IF IT DOES
(CASE
WHEN EXISTS (SELECT V.VPAA_FACULTY_DEGREE_CODE, --IF ON SUBJ CODE CROSSWALK
V.VPAA_FACULTY_SUBJECT_CODE
FROM VPAA_PPAGGENL_FIS V
JOIN DIM_DEGREE_CROSSWALK D
ON V.VPAA_FACULTY_DEGREE_CODE = D.VPAA_DEGREE_CODE
INNER JOIN SUBJ_CODE_CROSSWALK S
ON V.VPAA_FACULTY_DEGREE_CODE = S.VPAA_FACULTY_DEGREE_CODE
and S.VPAA_FACULTY_SUBJECT_CODE = V.VPAA_FACULTY_SUBJECT_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY
)
THEN (SELECT DISTINCT -- USE TERMINAL VALUE FROM SUBJ CODE
CASE
WHEN s.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN s.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM SUBJ_CODE_CROSSWALK S
INNER JOIN VPAA_PPAGGENL_FIS V
ON V.VPAA_FACULTY_DEGREE_CODE =
S.VPAA_FACULTY_DEGREE_CODE
AND S.VPAA_FACULTY_SUBJECT_CODE =
V.VPAA_FACULTY_SUBJECT_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY )
WHEN EXISTS (SELECT DISTINCT V.VPAA_FACULTY_DEGREE_CODE, --IF ON CLIC INSTRUCTORS CROSSWALK
V.VPAA_FACULTY_SUBJECT_CODE,
I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG
FROM VPAA_PPAGGENL_FIS V
INNER JOIN CLIC_INSTRUCTORS_CROSSWALK C
ON V.VPAA_FACULTY_DEGREE_CODE = C.VPAA_FACULTY_DEGREE_CODE
AND V.VPAA_FACULTY_SUBJECT_CODE = C.VPAA_FACULTY_SUBJECT_CODE
INNER JOIN IPEDS_REPORT_DETAILS I ON V.PIDM = I.HR_PIDM
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY
AND (I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396000'
OR I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396010')
)
THEN (SELECT DISTINCT
CASE
WHEN C.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN C.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM CLIC_INSTRUCTORS_CROSSWALK C
INNER JOIN VPAA_PPAGGENL_FIS V
ON C.VPAA_FACULTY_DEGREE_CODE =
V.VPAA_FACULTY_DEGREE_CODE
AND C.VPAA_FACULTY_SUBJECT_CODE =
V.VPAA_FACULTY_SUBJECT_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY ))
ELSE --USE DIM DEGREE CROSSWALK NORMALLY
(SELECT DISTINCT
CASE
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM DIM_DEGREE_CROSSWALK D
JOIN VPAA_PPAGGENL_FIS V
ON V.VPAA_FACULTY_DEGREE_CODE =
D.VPAA_DEGREE_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY)
ORDER BY OIR_DEGREE_HIERARCHY DESC
FETCH FIRST ROW ONLY )
END) --USE DIM DEGREE CROSSWALK FOR THE TERMINAL VALUE FOR THE MAX DEG THERE
WHEN MAX_DEG != 30
THEN
(SELECT DISTINCT
CASE
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM DIM_DEGREE_CROSSWALK D
JOIN VPAA_PPAGGENL_FIS V
ON V.VPAA_FACULTY_DEGREE_CODE =
D.VPAA_DEGREE_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY
ORDER BY OIR_DEGREE_HIERARCHY DESC
FETCH FIRST ROW ONLY )
ELSE NULL
END
FROM HIGHES_DEG_VALUE )
WHERE SNP_FACULTY_CENSUS.OIR_FALL_TERM = 'Fall 2019'
;
表格结构:
CREATE TABLE SNP_FACULTY_CENSUS --(Snapshot table)
(
OIR_ACADEMIC_RANK VARCHAR2(50 BYTE),
OIR_DEPARTMENT_HOME_ORG VARCHAR2(50 BYTE),
OIR_EMPLOYEE_NAME_LAST_FIRST VARCHAR2(300 BYTE),
HR_POSN_ESOC VARCHAR2(10 BYTE),
OIR_FT_PT VARCHAR2(25 BYTE),
OIR_FACULTY_STATUS VARCHAR2(25 BYTE),
OIR_FALL_TERM VARCHAR2(20 BYTE),
HR_GENDER_DESCRIPTION VARCHAR2(30 BYTE),
HR_CURRENT_ID VARCHAR2(10 BYTE),
IPEDS_RACE_ETHNICITY_DESCRIPTION VARCHAR2(50 BYTE),
OIR_FACULTY_STAFF_DETAILED_ROLE VARCHAR2(50 BYTE),
OIR_MGMT_FAC_AND_INSTR_IN_FALL_TERM VARCHAR2(50 BYTE),
OIR_OTHER_DEGREE_NOTES VARCHAR2(100 BYTE),
OIR_DOCTORATE VARCHAR2(20 BYTE),
OIR_HIGHEST_DEGREE_CATEGORY VARCHAR2(25 BYTE),
OIR_TERMINAL_DEGREE VARCHAR2(50 BYTE),
OIR_PRIMARY_ROLE VARCHAR2(25 BYTE),
HR_PIDM INTEGER,
OIR_POSITION VARCHAR2(25 BYTE),
OIR_FACULTY_SALARY NUMBER(13,3),
OIR_SCHOOL VARCHAR2(50 BYTE),
OIR_TENURE_STATUS VARCHAR2(50 BYTE),
PERSON_SKEY INTEGER,
CREATED_DATE DATE,
MODIFIED_DATE DATE,
OIR_IPEDS_OCCUPATION_CATEGORY VARCHAR2(1000 BYTE),
IPEDS_RANK_DESCRIPTION VARCHAR2(200 BYTE),
OIR_POSN_SEQUENCE INTEGER
);
CREATE TABLE VPAA_PPAGGENL_FIS --Degree Table
(
PIDM INTEGER NOT NULL,
VPAA_FACULTY_INSTITUTION_CODE VARCHAR2(6 CHAR) NOT NULL,
VPAA_FACULTY_INSTITUTION_NAME VARCHAR2(30 CHAR),
VPAA_FACULTY_OFFICIAL_TRANSCRIPT VARCHAR2(1 CHAR),
VPAA_FACULTY_OFFICIAL_TRANSCRIPT_RECEIPT_DATE DATE,
VPAA_FACULTY_DEGREE_CODE VARCHAR2(6 CHAR),
VPAA_FACULTY_DEGREE_DESCRIPTION VARCHAR2(30 CHAR),
VPAA_FACULTY_GRADUATION_DATE DATE,
VPAA_FACULTY_TERMINAL_DEGREE VARCHAR2(1 CHAR),
VPAA_FACULTY_SUBJECT_CODE VARCHAR2(4 CHAR),
VPAA_FACULTY_SUBJECT_DESCRIPTION VARCHAR2(30 CHAR),
VPAA_FACULTY_CIPC_CODE VARCHAR2(6 CHAR),
VPAA_FACULTY_ACAT_CODE VARCHAR2(2 CHAR),
CREATED_DATE DATE,
MODIFIED_DATE DATE,
PERSON_SKEY INTEGER,
VPAA_FACULTY_YEARS_SINCE_GRADUATION NUMBER,
VPAA_CURRENT_DEGREE INTEGER
);
--Crosswalk tables
CREATE TABLE WHOIRMGR.SUBJ_CODE_CROSSWALK
(
VPAA_FACULTY_DEGREE_CODE VARCHAR2(20 BYTE),
VPAA_FACULTY_DEGREE_DESCRIPTION VARCHAR2(50 BYTE),
VPAA_FACULTY_SUBJECT_CODE VARCHAR2(20 BYTE),
SUBJECT_CODE_DESCRIPTION VARCHAR2(50 BYTE),
PERFORMANCE_OR_THEORY VARCHAR2(20 BYTE),
OIR_TERMINAL_DEGREE_STATUS VARCHAR2(30 BYTE),
CREATED_DATE DATE,
MODIFIED_DATE DATE
);
CREATE TABLE WHOIRMGR.DIM_DEGREE_CROSSWALK
(
OIR_DEGREE_LEVEL VARCHAR2(50 BYTE),
VPAA_DEGREE_CODE VARCHAR2(25 BYTE),
VPAA_DEGREE_DESCRIPTION VARCHAR2(100 BYTE),
CREATED_DATE DATE,
MODIFIED_DATE DATE,
DEGREE_LEVEL_SKEY INTEGER,
OIR_DEGREE_HIERARCHY INTEGER,
OIR_TERMINAL_DEGREE_STATUS VARCHAR2(50 BYTE),
VPAA_FACULTY_SUBJECT_CODE VARCHAR2(25 BYTE),
VPAA_FACULTY_SUBJECT_CODE_DESCRIPTION VARCHAR2(100 BYTE),
PERFORMANCE_OR_THEORY VARCHAR2(25 BYTE)
);
CREATE TABLE WHOIRMGR.CLIC_INSTRUCTORS_CROSSWALK
(
HR_DEPARTMENT_HOME_ORGN_CODE NVARCHAR2(20),
HR_DEPARTMENT_HOME_ORGN_CODE_DESCRIPTION NVARCHAR2(50),
VPAA_FACULTY_DEGREE_CODE VARCHAR2(20 BYTE),
VPAA_FACULTY_DEGREE_DESCRIPTION VARCHAR2(50 BYTE),
VPAA_FACULTY_SUBJECT_CODE VARCHAR2(20 BYTE),
SUBJECT_CODE_DESCRIPTION VARCHAR2(50 BYTE),
OIR_TERMINAL_DEGREE_STATUS VARCHAR2(40 BYTE)
);
更新:这就是最终的工作！在子查询中，我不得不做一个左连接而不是两个表中的 id 匹配.
Update: This is what ended up working!! In the subquery, I had to do a left join instead of where id matches in both tables.
UPDATE SNP_FACULTY_CENSUS SFC
SET OIR_TERMINAL_DEGREE = (
WITH HIGHES_DEG_VALUE AS (
SELECT max(D.OIR_DEGREE_HIERARCHY) AS MAX_DEG
FROM VPAA_PPAGGENL_FIS V
JOIN DIM_DEGREE_CROSSWALK D
ON V.VPAA_FACULTY_DEGREE_CODE =
D.VPAA_DEGREE_CODE
left JOIN SNP_FACULTY_CENSUS S
ON V.PERSON_SKEY = S.PERSON_sKEY) --CHANGE TO = SRC.PERSON_SKEY
SELECT
CASE
WHEN MAX_DEG = 30
THEN --CHECK IF EXIST ON CROSSWALKS AND USE THAT VALUE IF IT DOES
(CASE
WHEN EXISTS (SELECT V.VPAA_FACULTY_DEGREE_CODE, --IF ON SUBJ CODE CROSSWALK
V.VPAA_FACULTY_SUBJECT_CODE
FROM VPAA_PPAGGENL_FIS V
JOIN DIM_DEGREE_CROSSWALK D
ON V.VPAA_FACULTY_DEGREE_CODE = D.VPAA_DEGREE_CODE
INNER JOIN SUBJ_CODE_CROSSWALK S
ON V.VPAA_FACULTY_DEGREE_CODE = S.VPAA_FACULTY_DEGREE_CODE
and S.VPAA_FACULTY_SUBJECT_CODE = V.VPAA_FACULTY_SUBJECT_CODE
left JOIN SNP_FACULTY_CENSUS S
ON V.PERSON_SKEY = S.PERSON_sKEY
--WHERE V.PERSON_SKEY = SFC.PERSON_SKEY ----CHANGE TO = SRC.PERSON_SKEY
)
THEN (SELECT DISTINCT -- USE TERMINAL VALUE FROM SUBJ CODE
CASE
WHEN s.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN s.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM SUBJ_CODE_CROSSWALK S
INNER JOIN VPAA_PPAGGENL_FIS V
ON V.VPAA_FACULTY_DEGREE_CODE =
S.VPAA_FACULTY_DEGREE_CODE
AND S.VPAA_FACULTY_SUBJECT_CODE =
V.VPAA_FACULTY_SUBJECT_CODE
left JOIN SNP_FACULTY_CENSUS S
ON V.PERSON_SKEY = S.PERSON_sKEY)
--WHERE V.PERSON_SKEY = SFC.PERSON_SKEY )----CHANGE TO = SRC.PERSON_SKEY )
WHEN EXISTS (SELECT DISTINCT V.VPAA_FACULTY_DEGREE_CODE, --IF ON CLIC INSTRUCTORS CROSSWALK
V.VPAA_FACULTY_SUBJECT_CODE,
I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG
FROM VPAA_PPAGGENL_FIS V
INNER JOIN CLIC_INSTRUCTORS_CROSSWALK C
ON V.VPAA_FACULTY_DEGREE_CODE = C.VPAA_FACULTY_DEGREE_CODE
AND V.VPAA_FACULTY_SUBJECT_CODE = C.VPAA_FACULTY_SUBJECT_CODE
INNER JOIN IPEDS_REPORT_DETAILS I ON V.PIDM = I.HR_PIDM
--WHERE V.PERSON_SKEY = SFC.PERSON_SKEY ----CHANGE TO = SRC.PERSON_SKEY
left JOIN SNP_FACULTY_CENSUS S
ON V.PERSON_SKEY = S.PERSON_sKEY
AND (I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396000'
OR I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396010')
)
THEN (SELECT DISTINCT
CASE
WHEN C.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN C.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM CLIC_INSTRUCTORS_CROSSWALK C
INNER JOIN VPAA_PPAGGENL_FIS V
ON C.VPAA_FACULTY_DEGREE_CODE =
V.VPAA_FACULTY_DEGREE_CODE
AND C.VPAA_FACULTY_SUBJECT_CODE =
V.VPAA_FACULTY_SUBJECT_CODE
--WHERE V.PERSON_SKEY = SFC.PERSON_SKEY)----CHANGE TO = SRC.PERSON_SKEY)
left JOIN SNP_FACULTY_CENSUS S
ON V.PERSON_SKEY = S.PERSON_sKEY)
ELSE --USE DIM DEGREE CROSSWALK NORMALLY
(SELECT DISTINCT
CASE
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM DIM_DEGREE_CROSSWALK D
JOIN VPAA_PPAGGENL_FIS V
ON V.VPAA_FACULTY_DEGREE_CODE =
D.VPAA_DEGREE_CODE
--WHERE V.PERSON_SKEY = SFC.PERSON_SKEY ----CHANGE TO = SRC.PERSON_SKEY)
left JOIN SNP_FACULTY_CENSUS S
ON V.PERSON_SKEY = S.PERSON_sKEY
ORDER BY OIR_DEGREE_HIERARCHY DESC
FETCH FIRST ROW ONLY )
END) --USE DIM DEGREE CROSSWALK FOR THE TERMINAL VALUE FOR THE MAX DEG THERE
WHEN MAX_DEG != 30
THEN
(SELECT DISTINCT
CASE
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM DIM_DEGREE_CROSSWALK D
JOIN VPAA_PPAGGENL_FIS V
ON V.VPAA_FACULTY_DEGREE_CODE =
D.VPAA_DEGREE_CODE
left JOIN SNP_FACULTY_CENSUS S
ON V.PERSON_SKEY = S.PERSON_sKEY
--WHERE V.PERSON_SKEY = SFC.PERSON_SKEY ----CHANGE TO = SRC.PERSON_SKEY)
ORDER BY OIR_DEGREE_HIERARCHY DESC
FETCH FIRST ROW ONLY )
ELSE NULL
END
FROM HIGHES_DEG_VALUE )
WHERE SFC.OIR_FALL_TERM = 'Fall 2019'
推荐答案
当您将 WITH 与 UPDATE 一起使用时，您需要为更新表使用别名.如果没有，它就不起作用.
When you use WITH with UPDATE you need to use alias for the update table. If not, it does not work.
此外，您无法在 WITH CLAUSE 中调用 UPDATED TABLE，此时该表是未知的，这就是您获得 INVALID IDENTIFIER 的原因.要解决该问题，您需要删除该条件并在最后添加.
Also, you can't invoke the UPDATED TABLE in the WITH CLAUSE, at that moment that table is unknown, that is why you get INVALID IDENTIFIER. To solve the problem you need to remove that condition and added at the end.
试试这个，但我写的时候甚至都不知道数据模型，所以要注意.
Try this one, but I am writing without even knowing the data model, so be aware.
UPDATE SNP_FACULTY_CENSUS SFC
SET OIR_TERMINAL_DEGREE = (
WITH HIGHES_DEG_VALUE AS (
SELECT max(D.OIR_DEGREE_HIERARCHY) AS MAX_DEG
FROM VPAA_PPAGGENL_FIS V
JOIN DIM_DEGREE_CROSSWALK D
ON V.VPAA_FACULTY_DEGREE_CODE = D.VPAA_DEGREE_CODE
LEFT JOIN SNP_FACULTY_CENSUS S
ON V.PERSON_SKEY = S.PERSON_SKEY
)
SELECT
CASE
WHEN MAX_DEG = 30
THEN --CHECK IF EXIST ON CROSSWALKS AND USE THAT VALUE IF IT DOES
(CASE
WHEN EXISTS (SELECT V.VPAA_FACULTY_DEGREE_CODE, --IF ON SUBJ CODE CROSSWALK
V.VPAA_FACULTY_SUBJECT_CODE
FROM VPAA_PPAGGENL_FIS V
JOIN DIM_DEGREE_CROSSWALK D
ON V.VPAA_FACULTY_DEGREE_CODE = D.VPAA_DEGREE_CODE
INNER JOIN SUBJ_CODE_CROSSWALK S
ON V.VPAA_FACULTY_DEGREE_CODE = S.VPAA_FACULTY_DEGREE_CODE
and S.VPAA_FACULTY_SUBJECT_CODE = V.VPAA_FACULTY_SUBJECT_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY ----CHANGE TO = SRC.PERSON_SKEY
)
THEN (SELECT DISTINCT -- USE TERMINAL VALUE FROM SUBJ CODE
CASE
WHEN s.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN s.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM SUBJ_CODE_CROSSWALK S
INNER JOIN VPAA_PPAGGENL_FIS V
ON V.VPAA_FACULTY_DEGREE_CODE =
S.VPAA_FACULTY_DEGREE_CODE
AND S.VPAA_FACULTY_SUBJECT_CODE =
V.VPAA_FACULTY_SUBJECT_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY )----CHANGE TO = SRC.PERSON_SKEY )
WHEN EXISTS (SELECT DISTINCT V.VPAA_FACULTY_DEGREE_CODE, --IF ON CLIC INSTRUCTORS CROSSWALK
V.VPAA_FACULTY_SUBJECT_CODE,
I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG
FROM VPAA_PPAGGENL_FIS V
INNER JOIN CLIC_INSTRUCTORS_CROSSWALK C
ON V.VPAA_FACULTY_DEGREE_CODE = C.VPAA_FACULTY_DEGREE_CODE
AND V.VPAA_FACULTY_SUBJECT_CODE = C.VPAA_FACULTY_SUBJECT_CODE
INNER JOIN IPEDS_REPORT_DETAILS I ON V.PIDM = I.HR_PIDM
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY ----CHANGE TO = SRC.PERSON_SKEY
AND (I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396000'
OR I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396010')
)
THEN (SELECT DISTINCT
CASE
WHEN C.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN C.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM CLIC_INSTRUCTORS_CROSSWALK C
INNER JOIN VPAA_PPAGGENL_FIS V
ON C.VPAA_FACULTY_DEGREE_CODE =
V.VPAA_FACULTY_DEGREE_CODE
AND C.VPAA_FACULTY_SUBJECT_CODE =
V.VPAA_FACULTY_SUBJECT_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY )----CHANGE TO = SRC.PERSON_SKEY)
ELSE --USE DIM DEGREE CROSSWALK NORMALLY
(SELECT DISTINCT
CASE
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM DIM_DEGREE_CROSSWALK D
JOIN VPAA_PPAGGENL_FIS V
ON V.VPAA_FACULTY_DEGREE_CODE =
D.VPAA_DEGREE_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY ----CHANGE TO = SRC.PERSON_SKEY)
ORDER BY OIR_DEGREE_HIERARCHY DESC
FETCH FIRST ROW ONLY )
END) --USE DIM DEGREE CROSSWALK FOR THE TERMINAL VALUE FOR THE MAX DEG THERE
WHEN MAX_DEG != 30
THEN
(SELECT DISTINCT
CASE
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Terminal'
THEN
'Yes'
WHEN D.OIR_TERMINAL_DEGREE_STATUS =
'Not terminal'
THEN
'No'
ELSE
'Unknown'
END
FROM DIM_DEGREE_CROSSWALK D
JOIN VPAA_PPAGGENL_FIS V
ON V.VPAA_FACULTY_DEGREE_CODE =
D.VPAA_DEGREE_CODE
WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY ----CHANGE TO = SRC.PERSON_SKEY)
ORDER BY OIR_DEGREE_HIERARCHY DESC
FETCH FIRST ROW ONLY )
ELSE NULL
END
FROM HIGHES_DEG_VALUE
) WHERE SNP_FACULTY_CENSUS.OIR_FALL_TERM = 'Fall 2019' ;
这篇关于更新表集列等于选择其中 id 在 oracle 中的查询之外匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
