In the code below, Id like to have a new field in the results called CRE. The CRE field should be RE MANAGEMENT if that is a SEGMENT connected to any CLANs within the PREMISE_GP_ID. If no CLANs are RE MANAGEMENT then the CRE field should be OTHER.

SELECT

PREMISE_ID,

PREMISE_GP_ID,

CLAN_NAME,

SEGMENT_NAME,

TOTAL_USAGE_2019

FROM ES_VIEW.ES_CI_MA

where PREMISE_GP_ID in ('797768','22146163,'797296','1854347','995577')