If I understand your question correctly, the crucial question in the intended logic is whether the payment “paid” appears multiple times for the same order.

If no, you want to fetch the distinct rows as they are.

If yes, you want to append the row number to the order id for those rows.

In order to build exactly this result, you can use a CTE where you select both the number of payments “paid” and the row number per order id.

Then you query this CTE with the two conditions mentioned above and combine the results using UNION ALL.

So the entire SQL would be like this:

CREATE OR REPLACE VIEW SHIPPING_LABELS AS
WITH sub AS
(SELECT 
  o.order_id, o.customer, o.payment,
  SUM(o.payment = 'paid') OVER(PARTITION BY order_id) AS paids,
  ROW_NUMBER() OVER(PARTITION BY order_id) AS rn
FROM 
  Orders o 
  LEFT JOIN Parcels p 
    ON o.order_id = p.order_id)
SELECT 
  order_id, customer, payment
FROM sub
WHERE paids < 2
GROUP BY 
  order_id, customer, payment
UNION ALL
SELECT 
  CONCAT(order_id, '_', rn), customer, payment
FROM sub
WHERE paids > 1; 

I recommend to remove the ORDER BY clause from the view’s query. A view is usually unordered. If you want the result to be sorted, use ORDER BY when selecting from the view.
The ORDER BY clause might be changed to ORDER BY order_id DESC (in your query, you used DESC, but you must remove the DESC to build the result you have shown in your question).

Then the view will show exactly the result you requested for your sample data when running SELECT * FROM SHIPPING_LABELS ORDER BY order_id;:

You can verify it on this demo.

If I misunderstood your requirements, please let me know.

It’s absolutely possible to achieve your desired result using pure SQL with some conditional logic. To do this, you can use a combination of CASE and ROW_NUMBER() in SQL.

I gave you the updated view.

CREATE OR REPLACE VIEW SHIPPING_LABELS AS
SELECT 
    CASE 
        WHEN ORD.payment = 'paid' AND parcel_count > 1 
            THEN CONCAT(ORD.order_id, '_', ROW_NUMBER() OVER (PARTITION BY ORD.order_id ORDER BY PAR.id)) 
        ELSE ORD.order_id 
    END AS order_id,
    ORD.customer AS customer,
    ORD.payment AS payment
FROM orders ORD
LEFT JOIN parcels PAR ON ORD.order_id = PAR.order_id
JOIN (
    -- Subquery to count parcels per order
    SELECT order_id, COUNT(*) AS parcel_count
    FROM parcels
    GROUP BY order_id
) AS parcel_counts ON ORD.order_id = parcel_counts.order_id
ORDER BY ORD.order_id DESC;

Please try this and I am sure it will work.

Well, your current result is just select * from orders. It makes no sense to outer join a table, but not select anything from it and then use DISTINCT to remove the duplicate rows again created by the join.

What you want is to duplicate order rows in case the payment equals ‘paid’, so change the outer join accordingly. Then use window functions (COUNT OVER and ROW_NUMBER OVER) to decide whether to attatch numbers to the original order numbers and to get these numbers. Use a CASE expression to decide which kind of order number to display.

CREATE OR REPLACE VIEW shipping_labels AS
  SELECT
    CASE WHEN COUNT(*) OVER (PARTITION BY o.order_id) > 1 THEN
      CONCAT(p.order_id, '_', ROW_NUMBER() OVER (PARTITION BY p.order_id ORDER BY p.id))
    ELSE
      o.order_id
    END AS order_id,
    o.customer,
    o.payment
  FROM orders o
  LEFT JOIN parcels p ON p.order_id = o.order_id AND o.payment = 'paid';

Demo: https://dbfiddle.uk/tQNrSTC5

This could be done directly from your two joined tables :

CREATE OR REPLACE VIEW SHIPPING_LABELS AS
Select Distinct 
          Coalesce( Case When o.payment = 'paid' And 
                              Count(Case When o.payment = 'paid' Then p.id End) 
                                    Over(Partition By p.order_id) > 1
                         Then Concat( o.order_id, 
                                      '_', 
                                      Count(Case When o.payment = 'paid' Then p.id End) 
                                            Over(Partition By p.order_id Order By p.id
                                                  Rows Between Unbounded Preceding And Current Row)
                                    ) 
                    End,
                    o.order_id 
                  ) as order_id,
          o.customer as customer,
          o.payment  as payment
From      orders o
Left Join parcels p ON (o.order_id = p.order_id);

… check the result …

Select * From SHIPPING_LABELS Order By order_id;

/*    R e s u l t : 
order_id  customer      payment
--------  ------------  -------
100       customer 1    COD
101       customer 2    paid
102       customer 3    COD
103       customer 4    COD
104_1     customer 5    paid
104_2     customer 5    paid
104_3     customer 5    paid

See the fiddle here.