Получение числового значения не распознано

SELECT 'this is a string' as this_is_a_str,
    NVL(this_is_a_str, 0) as one_of_these_is_str_and_the_other_is_a_number;

также дает:

Numeric value 'this is a string' is not recognized

Что сказать, ваши типы разные, эфир преобразует вашу «строку в число, если она равна единице» или преобразует ваш ноль в строку '0'

SELECT '1234' as this_is_a_str,
    0 as this_is_a_number,
    NVL(this_is_a_str, this_is_a_number::string),
    NVL(this_is_a_str::int, this_is_a_number);

это прекрасно работает. Теперь для вашего кода вы делаете это дважды.

SELECT 
    ID,
    INDEX,
    purchase_list,
    NVL(CASE WHEN INDEX = 2 THEN purchase_list END, '0') AS qty,
    sum(NVL(CASE WHEN INDEX = 2 THEN purchase_list END, '0')) over (partition by ID) as order_qty    
FROM (
    SELECT 
        ID,
        index,
        d.value::string AS purchase_list 
    FROM (
        SELECT 
            ID,
            c.value::string AS purchase_list
        FROM table_1,
            LATERAL flatten(INPUT=>split(post_purchase_list, '|')) c 
    ), LATERAL flatten(INPUT=>split(purchase_list, ';')) d
)
WHERE ID = 1250707

но, как упоминалось ранее, эти сглаживания можно было написать

SELECT 
    ID,
    INDEX,
    purchase_list,
    NVL(CASE WHEN INDEX = 2 THEN purchase_list END,'0') AS qty,
    sum(NVL(CASE WHEN INDEX = 2 THEN purchase_list END,'0')) over (partition by ID) as order_qty    
FROM (
    SELECT 
        ID,
        d.index,
        d.value::string AS purchase_list 
    FROM table_1
        ,LATERAL flatten(INPUT=>split(post_purchase_list, '|')) c 
        ,LATERAL flatten(INPUT=>split(c.value::string, ';')) d
)
WHERE ID = 1250707

и qty можно использовать повторно:

SELECT 
    ID,
    INDEX,
    purchase_list,
    NVL(CASE WHEN INDEX = 2 THEN purchase_list END,'0') AS qty,
    sum(qty) over (partition by ID) as order_qty    
FROM (
    SELECT 
        ID,
        d.index,
        d.value::string AS purchase_list 
    FROM table_1
        ,LATERAL flatten(INPUT=>split(post_purchase_list, '|')) c 
        ,LATERAL flatten(INPUT=>split(c.value::string, ';')) d
)
WHERE ID = 1250707

ИЛИ

SELECT 
    ID,
    d.index,
    d.value::string AS purchase_list, 
    NVL(CASE WHEN d.index = 2 THEN purchase_list END, '0') AS qty,
    sum(qty) over (partition by ID) as order_qty 
FROM table_1
    ,LATERAL flatten(INPUT=>split(post_purchase_list, '|')) c 
    ,LATERAL flatten(INPUT=>split(c.value::string, ';')) d
WHERE ID = 1250707