Script with IF statment for updating an Oracle table field

Question

I need your help for creating my script for updating a field in my table.

In my application, I have persons (table PERSON) who create REQUESTS (table REQUEST). A person is active when she has created a request during the last 3 years. I have created a field (ACTIVE - default value: 1) in the table PERSON in order to know if the person is still active.

I create a query for retrieving the number of requests for each person (active request, inactive request):

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

This script is working. Now I would like to implement a script for updating the field ACTIVE when the person is active. I tried in a first time to implement the IF Statement but it's not working.

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

Could you please help me to do that ?

Thanks for your help

in a query you can use case not if . .. ,case when ..count(1) >0... end — Moudiz
– Moudiz, Commented Jan 8, 2019 at 11:25

sticky bit · Accepted Answer · 2019-01-08 11:35:08Z

1

You can use a CASE ... END expression. You might also want to try to use EXISTS instead of getting the count. That may be faster.

SELECT p.id,
       p.lastname || ' ' ||  p.firstname personname,
       p.company,
       p.active,
       (SELECT count(*)
               FROM request req
               WHERE req.personid = p.id) nb_request,
       (SELECT count(*)
               FROM request reqact
               WHERE reqact.personid = p.id
                     AND reqact.requestdate > add_months(trunc(sysdate, 'YYYY'), -36)) nb_active_requests,
       (SELECT count(*)
               FROM request reqinact
               WHERE reqinact.personid = p.id
                     AND reqinact.requestdate < add_months(trunc(sysdate, 'YYYY'), -36)) nb_inactive_requests,
       CASE
         WHEN EXISTS (SELECT *
                             FROM request reqreact
                             WHERE reqact.personid = p.id
                                   AND reqreact.requestdate > add_months(trunc(sysdate, 'YYYY'), -36)) THEN
           1
         ELSE
           0
       END re_active
       FROM person p;

An alternative would be Oracle's proprietary decode().

BTW: Looking at your both subqueries comparing requestdate, there are only the operators < and > used. That leaves a gap for the = case. Maybe that wasn't intended and you want to check whether to use =< or =< for one of them.

answered Jan 8, 2019 at 11:35

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

coeurdange57 Over a year ago

Thank you it's working. Could you tell me please if I can do the update of the field ACTIVATE (value = 0 when nb_active_requests = 0) directly in the query ?

Moudiz · Accepted Answer · 2019-01-08 11:34:19Z

Well just replace if statement with case condition

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",
                             case when ( SELECT COUNT(*)
                                                                                         FROM request reqReAct
                                                                                        WHERE reqAct.PERSONID = p.ID
                                                                                          and reqReAct.requestdate >
                                                                                              ADD_MONTHS(TRUNC(SYSDATE,
                                                                                                               'YYYY'),
                                                                                                         -36))) >0 THEN 1 ELSE 0 end ) AS "RE-ACTIVE"


  from person p

Collectives™ on Stack Overflow

Script with IF statment for updating an Oracle table field

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related