select * from(
SELECT
'dwprd' as src,
PIN,PRODUCT_ID,PRODUCT_TYPE,CUSTOMER_ID,PIN_STATUS,PIN_STATUS_RSN_CODE,CHANGE_STATUS_REQUEST,RESTRICTION,
ORDER_NO,NAME_TAG,PIN_ORD_QTY
FROM DIM.DELTA_PIN_PULL_TBL a where order_no = 242097257
union
SELECT
'odm' as src,
PIN,PRODUCT_ID,PRODUCT_TYPE,CUSTOMER_ID,PIN_STATUS,PIN_STATUS_RSN_CODE,CHANGE_STATUS_REQUEST,RESTRICTION,
ORDER_NO,NAME_TAG,PIN_ORD_QTY
FROM DIM.DELTA_PIN_PULL_TBL@DWPRDODM a where order_no = 242097257
)
order by order_no, pin, product_type, src;