From 3b19ee3f6c0a2dc62bd1b4c4a7c39ff2db6ab621 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 19 Jan 2024 14:21:14 +0000 Subject: [PATCH] Load DadIDPrelim; adjust DadID and DadIDPub accordingly --- conversion/load_data.sql | 26 ++++++++++++++++++++++++-- 1 file changed, 24 insertions(+), 2 deletions(-) diff --git a/conversion/load_data.sql b/conversion/load_data.sql index 968e590..ff89330 100644 --- a/conversion/load_data.sql +++ b/conversion/load_data.sql @@ -65,6 +65,7 @@ INSERT INTO biography( ,sex ,momid ,dadid + ,dadprelim ,dadidpub ,firstborn ,birthdate @@ -83,8 +84,29 @@ INSERT INTO biography( , b_bgcertainty , b_sex , b_momid - , b_dadid - , b_dadid_publication_info + , CASE WHEN split_part(b_dadid, '_', 2) IS NOT DISTINCT FROM 'prelim' + THEN split_part(b_dadid, '_', 1) + WHEN b_dadid = '' + THEN NULL + ELSE b_dadid + END CASE + , CASE WHEN (b_dadid IS NULL + OR b_dadid = '') + THEN NULL + WHEN split_part(b_dadid, '_', 2) = 'prelim' + THEN TRUE + ELSE FALSE + END CASE + , CASE WHEN b_dadid_publication_info = '' + AND (b_dadid IS NOT NULL + AND b_dadid <> '') + THEN 'Unknown' + WHEN b_dadid_publication_info = '' + AND (b_dadid IS NULL + OR b_dadid = '') + THEN NULL + ELSE b_dadid_publication_info + END CASE , b_firstborn , b_birthdate , b_bdmin -- 2.34.1