SELECTDISTINCTCASE
WHEN REGEXP_LIKE(B.REGISTERORGID, .XM) THEN
SUBSTR(B.REGISTERORGID,
1,
INSTR(B.REGISTERORGID, .XM, 1) - 1)
ELSE
B.REGISTERORGID
END,
B.SERVNUMBER,
C.RECOPID,
C.RECDATE,
B.SUBSID
FROM TBCS.SUBSCRIBER PARTITION(SUBSCRIBER_724) B,
TBCS.RECEPTION PARTITION(RECEPTION_724_202202) C
WHERE B.NETTYPE = GSM
AND B.ACTIVE = 1
AND B.STATUS = US10
AND B.CREATEDATE > TO_DATE(20220201, YYYYMMDD)
AND B.REGISTERORGID LIKEHB.JM.03.%
AND C.SERVNUMBER = B.SERVNUMBER
AND C.RECDATE > TO_DATE(20220201, YYYYMMDD)
AND C.RECDEFID = Install
AND B.SUBSID NOTIN
(SELECT  A.SUBSID
FROM TBCS.SUBS_PRODUCT PARTITION(SUBS_PRODUCT_724) A
WHERE A.PRODID LIKEG238354%
AND A.APPLYDATE > TO_DATE(20220201, YYYYMMDD)
UNIONALL
SELECT  A.SUBSID
FROM TBCS.SUBS_PRODUCT PARTITION(SUBS_PRODUCT_724) A
WHERE A.PRODID = MP9990103000300
AND A.APPLYDATE > TO_DATE(20220201, YYYYMMDD)
UNIONALL
SELECT/*+ index(a IDX_SUBS_PRODUCT_PRODID) */
A.SUBSID
FROM TBCS.SUBS_PRODUCT PARTITION(SUBS_PRODUCT_724) A
WHERE A.PRODID IN (SELECT D.PRODID
FROM TBCS.PRODUCT D
WHERE D.PRODNAME LIKEXX%XXX%
OR D.PRODNAME LIKEXX%XXX%
OR D.PRODNAME LIKEXX%XXX%%
OR D.PRODNAME LIKEXX%XXX%%
OR D.PRODNAME LIKE%XX%
OR D.PRODNAME LIKE%XX%)
AND A.APPLYDATE > TO_DATE(20220201, YYYYMMDD))
ORDERBYCASE
WHEN REGEXP_LIKE(B.REGISTERORGID, .XM) THEN
SUBSTR(B.REGISTERORGID, 1, INSTR(B.REGISTERORGID, .XM, 1) - 1)
ELSE
B.REGISTERORGID
END,
C.RECDATE;