我需要您的帮助来创建用于更新表中字段的脚本。
在我的应用程序中,我有创建请求(表请求)的人员(表人员)。
当一个人在过去3年内创建了一个请求时,该人处于活动状态。
我已经在表Person中创建了一个字段(active-默认值:1),以便知道该人是否仍然处于活动状态。
我创建一个查询,用于检索每个人的请求数(活动请求、非活动请求):
select p.ID, p.LASTNAME || ' ' || p.FIRSTNAME personName,p.COMPANY, p.ACTIVE,
(SELECT COUNT(*) FROM request req WHERE req.PERSONID = p.ID) AS "NB_REQUEST",
(SELECT COUNT(*) FROM request reqAct WHERE reqAct.PERSONID = p.ID and reqAct.requestdate > ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36)) AS "NB_ACTIVE_REQUESTS",
(SELECT COUNT(*) FROM request reqInact WHERE reqInact.PERSONID = p.ID and reqInact.requestdate < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36)) AS "NB_INACTIVE_REQUESTS"
from person p
这个脚本正在运行。现在,我想实现一个脚本,用于在人员处于活动状态时更新字段active。我第一次尝试实现if语句,但它不起作用。
select p.ID, p.LASTNAME || ' ' || p.FIRSTNAME personName,p.COMPANY, p.ACTIVE,
(SELECT COUNT(*) FROM request req WHERE req.PERSONID = p.ID) AS "NB_REQUEST",
(SELECT COUNT(*) FROM request reqAct WHERE reqAct.PERSONID = p.ID and reqAct.requestdate > ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36)) AS "NB_ACTIVE_REQUESTS",
(SELECT COUNT(*) FROM request reqInact WHERE reqInact.PERSONID = p.ID and reqInact.requestdate < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36)) AS "NB_INACTIVE_REQUESTS"
(IF((SELECT COUNT(*) FROM request reqReAct WHERE reqAct.PERSONID = p.ID and reqReAct.requestdate > ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36))) > 0 THEN 1 ELSE 0) AS "RE-ACTIVE"
from person p
你能帮我吗?
谢谢你的帮助