From d911ef246160eb4a9ebc338f8b833d214939c867 Mon Sep 17 00:00:00 2001
From: Stevan Earl <stevan.earl@asu.edu>
Date: Thu, 20 Aug 2026 09:46:47 -0700
Subject: [PATCH] Resolve Problem #82 with NODATA severity

---
 conversion/clean.sql           | 8 ++++++++
 conversion/load_aggressions.m4 | 7 +------
 conversion/load_support.sql    | 3 ++-
 doc/src/code_tables.m4         | 4 +++-
 4 files changed, 14 insertions(+), 8 deletions(-)

diff --git a/conversion/clean.sql b/conversion/clean.sql
index 59c7e2f..087a842 100644
--- a/conversion/clean.sql
+++ b/conversion/clean.sql
@@ -757,6 +757,14 @@ UPDATE aggression_event
         OR ae_full_description IS NULL
         OR ae_aggressor_behavior IS NULL;
 
+-- Problem #82
+-- Trim fight-category codes and distinguish missing data from an unrated fight.
+UPDATE aggression_event
+  SET ae_fight_category =
+        COALESCE(NULLIF(BTRIM(ae_fight_category), ''), 'NODATA')
+  WHERE ae_fight_category IS DISTINCT FROM
+        COALESCE(NULLIF(BTRIM(ae_fight_category), ''), 'NODATA');
+
 -- Problem #101
 UPDATE aggression_event
   SET ae_fol_b_focal_id = 'UNK'
diff --git a/conversion/load_aggressions.m4 b/conversion/load_aggressions.m4
index 1a89ab7..a39dd98 100644
--- a/conversion/load_aggressions.m4
+++ b/conversion/load_aggressions.m4
@@ -212,12 +212,7 @@ BEGIN
           ELSE
             'Invalid'  -- Blow up with an typecasting error
         END
-      , CASE  -- severity
-          WHEN this_ae.ae_fight_category_flag IS NULL THEN
-            'unrated'
-          ELSE
-            this_ae.ae_fight_category_flag
-        END
+      , this_ae.ae_fight_category
       , this_ae.ae_aggressor_behavior
       , this_ae.ae_recipient_behavior
       , this_ae.ae_full_description
diff --git a/conversion/load_support.sql b/conversion/load_support.sql
index 56db6e8..93110c9 100644
--- a/conversion/load_support.sql
+++ b/conversion/load_support.sql
@@ -28,7 +28,8 @@ INSERT INTO agg_severities (severity, description)
        , ('0', 'Low severity')
        , ('1', 'Medium severity')
        , ('2', 'High severity')
-       , ('?', 'Questionable severity');
+       , ('?', 'Questionable severity')
+       , ('NODATA', 'No fight-category data recorded');
 
 -- arrival_sources
 -- From the follow_arrival part of the MS Word doc for the MS Access db
diff --git a/doc/src/code_tables.m4 b/doc/src/code_tables.m4
index 6fba126..8e79576 100644
--- a/doc/src/code_tables.m4
+++ b/doc/src/code_tables.m4
@@ -53,7 +53,9 @@ AGG_SEVERITIES (AGGression SEVERITIES)
 Special Values
 ``````````````
 
-None.
+``NODATA``
+   No fight-category data were recorded.  This differs from ``unrated``,
+   which means that the aggression was not evaluated for severity.
 
 
 Column Descriptions
-- 
2.34.1