Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

accept non-rule fields as zero/the rules intersect each other

erdem ustunMar 23 2017 — edited Mar 25 2017

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

This post has been answered by mathguy on Mar 23 2017
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 22 2017
Added on Mar 23 2017
31 comments
1,245 views