Hi,
I have a problem and I ask for your help.
There is a table with 90 fields.The number of records in the table is approximately 2 380 000.Total number of rules 149.
Depending on the various combinations in these fields, the types of organizations are assigned.
I know how to do this with sql and I use case when function.
But the rules intersect each other.
I created an example of 6 fields in order to be an example for not writing 90 variables one by one.
WITH DATA (ESTABLISH_nr,A_1,B_1,C_1,D_1,D_2)
AS (SELECT 'E1',1,1,0,0,0 FROM dual UNION ALL
SELECT 'E2',1,1,3,1,0 FROM dual UNION ALL
SELECT 'E3',1,0,2,0,0 FROM dual UNION ALL
SELECT 'E4',1,0,2,1,0 FROM dual UNION ALL
SELECT 'E5',1,1,3,1,1 FROM dual UNION ALL
SELECT 'E6',1,0,9,2,2 FROM dual UNION ALL
SELECT 'E8',1,2,3,0,0 FROM dual UNION ALL
SELECT 'E9',1,2,9,1,0 FROM dual UNION ALL
SELECT 'E10',1,1,1,0,0 FROM dual UNION ALL
SELECT 'E11',1,1,0,0,0 FROM dual UNION ALL
SELECT 'E12',1,1,0,1,0 FROM dual UNION ALL
SELECT 'E13',1,1,7,8,1 FROM dual)
SELECT ESTABLISH_nr,
CASE
WHEN A_1=1 AND B_1=1 THEN 'EST_A' /* RULE_1 */
WHEN A_1=1 AND B_1=1 AND C_1>0 THEN 'EST_B' /* RULE_2 */
WHEN A_1=1 AND B_1=0 THEN 'EST_C' /* RULE_3 */
WHEN A_1=1 AND B_1=0 AND C_1>0 AND D_1=1 THEN 'EST_D' /* RULE_4 */
WHEN A_1=1 AND B_1>=0 AND C_1>0 THEN 'EST_E' /* RULE_5 */
WHEN A_1=1 AND B_1>=0 AND C_1>0 AND D_1>0 THEN 'EST_E' /* RULE_6 */
WHEN A_1=1 AND D_1>=0 THEN 'EST_F' /* RULE_7 */
ELSE 'NOT_ASSIGNED' /* RULE_NULL */
END AS Institution_type,A_1, B_1,C_1,D_1,D_2
FROM DATA
To explain this example out.
Written;
WHEN A_1=1 AND B_1=1 THEN 'EST_A' /* RULE_1 */
In fact, it should be;
WHEN A_1=1 AND B_1=1 AND C_1=0 AND D_1=0 THEN 'EST_A' /* RULE_1 */
But in reality, it is quite troublesome to write a condition that equals zero for all other variables after each condition.At the end of the rules these fields are incorrectly assigned if not written.
Fields that are not in the written rule have a single condition and must be zero.
for example;
Assigned EST_A for E2
The EST_B should be assigned
The rules work sequentially.So the first rule works and is assigned and the next record goes on.
How can I do this with sql without having to write all the fields(rest of fields=0) at the end of the rules.
or How can I create the fields collectively without writing with one by one the fields/variables that are not in the rule
I would like your help in this regard