Need full order data if Order contains a specific subclass

18 Views Asked by At

I'm looking to report on orders that contain a specific subclass. The orders can have multiple subclasses on them if a customer orders more than 1 item per order. I want to retrieve the results of orders with a specific subclass but i also want the data of any other product on the customers orders. The way i currently have it written, my answerset only gives me lines where the specific subclass exists. If i want subclass APPLE but there's also SUBCLASS Marvel Movies, my answerset only gives a row of APPLE. I'd have to look at the order by itself to know it had other lines on it. I also only want subclasses or lines that have a specific modification present.

select distinct
ORD_ID,
ORD_DT,
reship,
SKU,
SUBCLASS
     
                   
from
TBEND_OR_ORD ord1
JOIN TBEND_OR_ORDER_It item on ord1.ORD_ID = item.ORD_ID
   INNER JOIN    TBEND_OR_ORD_AUD   aud        ON      ORD1.ORD_ID                            =  aud.ORD_ID

LEFT JOIN PVWBB_LU_ITEM_ECM  sku ON    ITEM.OFFR_KEY = sku.OFFR_KEY


where
aud.src_rec like ANY ('allow%', 'RESHIP%') and ORD_DT >= '2023-08-21' 

 and

 SUBCLASS in ('NINTENDO SWITCH HDWE',
'STANDARD APPLE WATCH',
'BEATS ON/OVER EARS',
'APPLE WIFI',
'APPLE TRUE WIRELESS',
'STANDALONE VR',
'GSRF APPLE HEADPHONE')

I've tried a few things, but nothing has worked

0

There are 0 best solutions below