CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Daniel Cobo
Daniel Cobo

Reputation: 3

Get attribute from string

I'm making a database and I have

CREATE TABLE reserva ( 
 id_clinica       NUMBER, 
 APOSITIVO       DECIMAL(10,4), 
 ANEGATIVO         DECIMAL(10,4), 
 BPOSITIVO       DECIMAL(10,4), 
 BNEGATIVO         DECIMAL(10,4), 
 ABPOSITIVO       DECIMAL(10,4), 
 ABNEGATIVO         DECIMAL(10,4), 
 OPOSITIVO       DECIMAL(10,4), 
 ONEGATIVO         DECIMAL(10,4), 
);

CREATE TABLE donante (  
 dni_donante     VARCHAR(9), 
 fecha_alta DATE, 
 movil  VARCHAR(9),
 nombre VARCHAR(20),
 apellido1 VARCHAR(20),
 apellido2 VARCHAR(20),
 tipo_sangre VARCHAR(15),
);

In the table donante, the attribute "tipo_sangre" will always be "APOSITIVO", "ANEGATIVO", "BPOSITIVO", BNEGATIVO", etc.

I want to make a trigger that when inserting a row on another table, will get the "tipo_sangre" from donante and add a number to it's correspondant attribute of the table reserva.

Is there any way to do that besides declaring a variable for tipo_sangre and doing a switch case?

Thank you beforehand :)

Upvotes: 0

Views: 60

Answers (1)

user5683823
user5683823

Reputation:

If the first table is meant to simply give you the count by blood type for the rows in the second table, I would recommend rethinking the database design. You only need a "dimension" table for blood types (a single column, eight rows, showing the names of the blood types). This column should be primary key. Then tipo-sangre in donate should have a foreign key constraint, referencing this blood types table. Finally, for the count you don't need a table, you need a view. Based on a query like this:

select count(case tipo_sangre when 'apositivo' then 1 end) as apositivo,
       count(case tipo_sangre when 'anegativo' then 1 end) as anegativo, 
       .....
       count(case tipo_sangre when 'onegativo' then 1 end) as onegativo
from donate

Don't create a separate table to store these counts; that can only cause trouble in the future as you will have redundant data which may, at some point, contradict itself for any number of reasons.

Upvotes: 1

Related Questions