1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

CREATE OR REPLACE TABLE DEV_MAJ."PD_2021_Wk7_ShoppingList" (
    "Product"     VARCHAR(255),
    "Description" VARCHAR(1000),
    "Ingredients" VARCHAR(2000)
);
CREATE OR REPLACE TABLE DEV_MAJ."PD_2021_Wk7_Keywords" (
    "AnimalIngredients" VARCHAR(1000),
    "ENumbers"          VARCHAR(1000)
);
IMPORT INTO DEV_MAJ."PD_2021_Wk7_ShoppingList" FROM LOCAL CSV
    FILE 'C:\Mark\Preppin Data\PD 2021 Wk 7 Shopping List.csv'
    SKIP = 1
    ROW SEPARATOR  = 'CRLF'
    COLUMN SEPARATOR = ','
    COLUMN DELIMITER = '"'
;
IMPORT INTO DEV_MAJ."PD_2021_Wk7_Keywords" FROM LOCAL CSV
    FILE 'C:\Mark\Preppin Data\PD 2021 Wk 7 Ingredients.csv'
    SKIP = 1
    ROW SEPARATOR  = 'CRLF'
    COLUMN SEPARATOR = ','
    COLUMN DELIMITER = '"'
;

CREATE OR REPLACE VIEW DEV_MAJ."PD_2021_Wk7_ShoppingListKeywords_vw" AS
    WITH keyword_tall_pass1 AS ( --generate unique rows for ingredients / e-numbers
        SELECT
            i."RecordID"
            ,REGEXP_SUBSTR(k."AnimalIngredients",'(?i)([a-z]+)',1,i."RecordID") AS "Ingredient"
            ,REGEXP_SUBSTR(k."ENumbers",'(?i)([0-9]+)',1,i."RecordID") AS "ENumber"
        FROM DEV_MAJ."PD_2021_Wk7_Keywords" k
            CROSS JOIN (SELECT level AS "RecordID" FROM DUAL CONNECT BY level < 30) i --30 is arbitrary
        WHERE local."Ingredient" IS NOT NULL OR local."ENumber" IS NOT NULL --drop null records

    ), nonvegan_keywords AS ( --stack ingredients / e-numbers
        SELECT "Ingredient" AS "Keyword" FROM keyword_tall_pass1 k WHERE k."Ingredient" IS NOT NULL
        UNION ALL
        SELECT 'E' || "ENumber" AS "Keyword" FROM keyword_tall_pass1 k WHERE k."ENumber" IS NOT NULL
    )

    SELECT --return products w/ delimited list of matching ingredients
        sl."Product"
        ,sl."Description"
        ,sl."Ingredients"
        ,GROUP_CONCAT(
            CASE WHEN INSTR(LOWER(sl."Ingredients"),LOWER(nvk."Keyword"))>0  --when ingredients contain keyword
            THEN nvk."Keyword" END) AS "Contains"
    FROM DEV_MAJ."PD_2021_Wk7_ShoppingList" sl
        CROSS JOIN nonvegan_keywords nvk
    GROUP BY 1,2,3
;

--OUTPUT 1: Vegan Shopping List
SELECT slk."Product", slk."Description"
FROM DEV_MAJ."PD_2021_Wk7_ShoppingListKeywords_vw" slk
WHERE slk."Contains" IS NULL
ORDER BY 1;

--OUTPUT 2: Non-Vegan Shopping List
SELECT slk."Product", slk."Description", slk."Contains"
FROM DEV_MAJ."PD_2021_Wk7_ShoppingListKeywords_vw" slk
WHERE slk."Contains" IS NOT NULL
ORDER BY 1;