INSERT A Record when clicking a button depending on a if statement

Question

hello everyone i want to make a save button in my oracle form ,and that form have many symptoms checkboxes that records a 1 when checked and I want a way to count those ones into a column called count_symptoms then if the number is larger than 3 the value for the result column would be like "positive" if not it would be "negative" and yes its a covid 19 project :) , finally it will commit_form;

the table is like :

[ ID  NAME  FEVER COUGH HEADACHE.....NO_OF_SYMPTOMS RESULT
 123. SCOTT  1.    1.    1.             3.      POSITIVE ]

I tried this create table statement before but it didn't work so now I think if I did it with a button pl statement it might work

create table covid(ID NUMBER(7) , NAME VARCHAR2(32) ,DEPT VARCHAR2(16) , FEVER NUMBER(1) , COUGH NUMBER(1) ,
TIREDNESS NUMBER(1) , SHORT_BREATH NUMBER(1) ,SORE_THROAT NUMBER(1) ,CHEST_PAIN NUMBER(1) ,
LOSE_SENSES NUMBER(1),RUNNY_NOSE NUMBER(1), TEST_DATE DATE , 
SYMPTOMS NUMBER(16) ,
(CASE
WHEN SYMPTOMS >= 3 THEN 'POSITIVE' ELSE 'NEGATIVE' 
END AS RESULT));
/

THANKS IN ADVANCE!

Answer 1

First of all, from my point of view, data model is wrong. There should be at least 3 tables to handle that: patients, symptoms, and [patient x symptom] which contains data about "active" symptoms for each patient. Something like this:

SQL> create table patient
  2    (id_pat number       primary key,
  3     name   varchar2(20) not null
  4    );

Table created.

SQL> create table symptom
  2    (id_sym number       primary key,
  3     name   varchar2(20) not null
  4    );

Table created.

SQL> create table patxsym
  2    (id_pat number constraint fk_pxs_pat references patient,
  3     id_sym number constraint fk_pxs_sym references symptom,
  4                   --
  5                   constraint pk_pxs primary key (id_pat, id_sym)
  6    );

Table created.

Sample data:

SQL> insert into patient (id_pat, name)
  2    select 1, 'Little' from dual union all
  3    select 2, 'Foot'   from dual;

2 rows created.

SQL> insert into symptom (id_sym, name)
  2    select 1, 'fever'        from dual union all
  3    select 2, 'cough'        from dual union all
  4    select 3, 'headache'     from dual union all
  5    select 4, 'short breath' from dual union all
  6    select 5, 'sore throat'  from dual;

5 rows created.

Symptoms per patients: in Forms, patient would be a master block, while patxsym would be its detail block (tabular layout) so you'd add as many symptoms as necessary.

This option scales well. If new symptoms appear, you'd just add them into the symptom table and the form works with the newly added symptom.

Your option scales as a goat you'd like to teach how to fly, i.e. it doesn't scale at all. For any new symptom, you have

• alter table and add a new column (for a new symptom)
• run Forms Builder and edit the form by
  • adding new checboxes for new symptoms
  • modifying code that INSERTs INTO (as you have new column(s) and new checkbox(es))
• modify any reports you might have written

And that's an endless nightmare. I wouldn't do it, if I were you.

So: sample data for patients who have some symptoms:

SQL> insert into patxsym (id_pat, id_sym)
  2    select 1, 1 from dual union all
  3    select 1, 4 from dual union all
  4    select 1, 5 from dual union all
  5    --
  6    select 2, 2 from dual;

4 rows created.

Reporting:

Number of symptoms per patient:

SQL> select p.id_pat,
  2         p.name patient_name,
  3         count(*) no_of_symptoms,
  4         case when count(*) >= 3 then 'positive'
  5              else 'negative'
  6         end result
  7  from patient p join patxsym x on p.id_pat = x.id_pat
  8  group by p.id_pat, p.name;

    ID_PAT PATIENT_NAME         NO_OF_SYMPTOMS RESULT
---------- -------------------- -------------- --------
         1 Little                            3 positive
         2 Foot                              1 negative

SQL>

Symptoms per patient:

SQL> select p.name patient_name,
  2         s.name symptom,
  3         case when x.id_sym is not null then 'Yes'
  4              else 'No'
  5         end patient_has_symptom
  6  from patient p cross join symptom s
  7  left join patxsym x on x.id_pat = p.id_pat
  8                     and x.id_sym = s.id_sym
  9  order by p.name, s.name;

PATIENT_NAME         SYMPTOM              PAT
-------------------- -------------------- ---
Foot                 cough                Yes
Foot                 fever                No
Foot                 headache             No
Foot                 short breath         No
Foot                 sore throat          No
Little               cough                No
Little               fever                Yes
Little               headache             No
Little               short breath         Yes
Little               sore throat          Yes

10 rows selected.

SQL>