odc
odc copied to clipboard
oceanbase
[Bug]: SQL window ODC parsing SQL speed optimization
ODC version
ODC432
OB version
independent
What happened?
SQL window ODC parsing SQL speed optimization
What did you expect to happen?
。
How can we reproduce it (as minimally and precisely as possible)?
SELECT
bd.TARIFFPLAN_ID,
bd.PLANITEM_ID,
bd.DISC_ENTITY,
bd.DISC_COND,
bd.G_DISC_COND,
bd.DISC_TYPE,
bd.REFITEM_TYPE,
bd.FREEREFITEM_TYPE,
bd.REFITEM_CODE,
bd.REFITEM_CYCLES,
bd.REFITEM_CYCLE_OFFSET,
bd.REFVALUE_CALC_TYPE,
bd.REFVALUE_UNIT,
bd.DISC_OBJECT_TYPE,
bd.DISC_ITEM_CODE,
bd.START_CYCLE_OFFSET,
bd.VALID_CYCLE_TYPE,
bd.VALID_CYCLES,
bd.INUSE,
bd.TARIFFPLAN_TYPE,
bd.DISC_ITEM_TYPE,
bd.FREEREFITEM_MIN_USAGE,
bd.NOTE,
bd.ISDISCTSPEC,
bd.SPECDISCT_EXPR,
bd.G_SPECDISCT_EXPR,
bd.CONVERT_TYPE,
bd.REF_ENTITY_TYPE,
bd.DISC_SOURCE,
bd.G_DISC_SOURCE,
bd.DISC_SOURCE_FACTOR,
bd.DISC_ITEM_EXPR_ID,
bd.FORCE_OUTPUT_EVENT,
bd.DISC_SOURCE_TYPE,
bducd.WORKORDER_ID,
bducd.DISC_ENTITY AS UCD_DISC_ENTITY,
bducd.DISC_COND AS UCD_DISC_COND,
bducd.G_DISC_COND AS UCD_G_DISC_COND,
bducd.DISC_TYPE AS UCD_DISC_TYPE,
bducd.REFITEM_TYPE AS UCD_REFITEM_TYPE,
bducd.FREEREFITEM_TYPE AS UCD_FREEREFITEM_TYPE,
bducd.REFITEM_CODE AS UCD_REFITEM_CODE,
bducd.REFITEM_CYCLES AS UCD_REFITEM_CYCLES,
bducd.REFITEM_CYCLE_OFFSET AS UCD_REFITEM_CYCLE_OFFSET,
bducd.REFVALUE_CALC_TYPE AS UCD_REFVALUE_CALC_TYPE,
bducd.REFVALUE_UNIT AS UCD_REFVALUE_UNIT,
bducd.DISC_OBJECT_TYPE AS UCD_DISC_OBJECT_TYPE,
bducd.DISC_ITEM_CODE AS UCD_DISC_ITEM_CODE,
bducd.START_CYCLE_OFFSET AS UCD_START_CYCLE_OFFSET,
bducd.VALID_CYCLE_TYPE AS UCD_VALID_CYCLE_TYPE,
bducd.VALID_CYCLES AS UCD_VALID_CYCLES,
bducd.INUSE AS UCD_INUSE,
bducd.TARIFFPLAN_TYPE AS UCD_TARIFFPLAN_TYPE,
bducd.DISC_ITEM_TYPE AS UCD_DISC_ITEM_TYPE,
bducd.FREEREFITEM_MIN_USAGE AS UCD_FREEREFITEM_MIN_USAGE,
bducd.NOTE AS UCD_NOTE,
bducd.ISDISCTSPEC AS UCD_ISDISCTSPEC,
bducd.SPECDISCT_EXPR AS UCD_SPECDISCT_EXPR,
bducd.G_SPECDISCT_EXPR AS UCD_G_SPECDISCT_EXPR,
bcl.LOGONID,
bcl.OPERID,
bcl.MACADDR,
bcl.IPADDR,
bcl.SUCCESS,
bcl.LOGONTIME,
bcl.HOSTNAME,
bcl.SID,
-- Fields from BILLINGCYCLE
bc.CYCLE_SCHEMA_ID,
bc.CYCLE_ID,
bc.CYCLE_NAME,
bc.CYCLE_STATUS,
bc.OPENDATE,
bc.CLOSEDATE,
bc.INVOICEDATE,
CASE
WHEN bd.DISC_TYPE = 1 THEN 'Type 1'
WHEN bd.DISC_TYPE = 2 THEN 'Type 2'
ELSE 'Other Type'
END AS DISC_TYPE_DESCRIPTION,
NVL(bd.NOTE, 'No Note') AS NOTE_STATUS,
DECODE(bcl.SUCCESS, 1, 'Success', 0, 'Failure', 'Unknown') AS LOGIN_STATUS,
TO_CHAR(bcl.LOGONTIME, 'YYYY-MM-DD HH24:MI:SS') AS FORMATTED_LOGONTIME,
(bd.REFITEM_CYCLES * bd.REFVALUE_UNIT) AS TOTAL_REF_VALUE,
(bducd.REFITEM_CYCLES + bducd.REFITEM_CYCLE_OFFSET) AS TOTAL_UCD_REF_CYCLES,
COUNT(bd.TARIFFPLAN_ID) OVER (PARTITION BY bd.TARIFFPLAN_ID) AS TARIFFPLAN_COUNT,
SUM(bd.FREEREFITEM_MIN_USAGE) OVER (PARTITION BY bd.PLANITEM_ID) AS TOTAL_USAGE_PER_PLAN,
AVG(bd.DISC_SOURCE_FACTOR) OVER () AS AVERAGE_DISC_SOURCE_FACTOR,
ROW_NUMBER() OVER (ORDER BY bcl.LOGONTIME DESC) AS RN,
RANK() OVER (PARTITION BY bd.TARIFFPLAN_TYPE ORDER BY bd.DISC_TYPE DESC) AS DISC_TYPE_RANK,
DENSE_RANK() OVER (ORDER BY bc.CYCLE_STATUS) AS CYCLE_STATUS_RANK,
NTILE(4) OVER (ORDER BY bd.TARIFFPLAN_ID) AS QUARTILE_TARIFFPLAN,
LEAD(bcl.LOGONTIME, 1) OVER (ORDER BY bcl.LOGONTIME) AS NEXT_LOGONTIME,
LAG(bcl.LOGONTIME, 1) OVER (ORDER BY bcl.LOGONTIME) AS PREV_LOGONTIME,
FIRST_VALUE(bd.DISC_COND) OVER (PARTITION BY bd.TARIFFPLAN_ID ORDER BY bd.PLANITEM_ID) AS FIRST_DISC_COND,
LAST_VALUE(bd.DISC_COND) OVER (PARTITION BY bd.TARIFFPLAN_ID ORDER BY bd.PLANITEM_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_DISC_COND,
(SELECT COUNT(*)
FROM BILLDISC_DEF bd_sub
WHERE bd_sub.TARIFFPLAN_ID = bd.TARIFFPLAN_ID
) AS SUBQUERY_COUNT_BD,
(SELECT MAX(bcl_sub.LOGONTIME)
FROM BILLINGCLIENTLOG bcl_sub
WHERE bcl_sub.LOGONID = bcl.LOGONID
) AS LAST_LOGON_TIME,
(SELECT bc_sub.CYCLE_NAME
FROM BILLINGCYCLE bc_sub
WHERE bc_sub.CYCLE_ID = bc.CYCLE_ID
) AS CURRENT_CYCLE_NAME,
(SELECT AVG(bducd_sub.FREEREFITEM_MIN_USAGE)
FROM BILLDISC_DEF_UCD bducd_sub
WHERE bducd_sub.PLANITEM_ID = bducd.PLANITEM_ID
) AS AVG_FREEREFITEM_MIN_USAGE,
(SELECT SUM(bd_sub.REFVALUE_UNIT)
FROM BILLDISC_DEF bd_sub
WHERE bd_sub.DISC_ENTITY = bd.DISC_ENTITY
) AS TOTAL_REFVALUE_UNIT_PER_ENTITY,
(
SELECT MAX(
(
SELECT COUNT(*)
FROM BILLINGCLIENTLOG bcl_inner
WHERE bcl_inner.LOGONID = bcl.LOGONID
)
)
FROM DUAL
) AS MAX_LOGIN_COUNT,
(
SELECT MIN(bc_sub.OPENDATE)
FROM BILLINGCYCLE bc_sub
WHERE bc_sub.CYCLE_STATUS = bc.CYCLE_STATUS
) AS MIN_OPEN_DATE_SAME_STATUS,
CASE
WHEN bd.DISC_TYPE = 1 AND bcl.SUCCESS = 1 THEN 'Active Discount and Successful Login'
WHEN bd.DISC_TYPE = 2 AND bcl.SUCCESS = 0 THEN 'Inactive Discount and Failed Login'
ELSE 'Other Conditions'
END AS COMBINED_STATUS,
CONCAT(bd.DISC_ITEM_CODE, '_', bducd.WORKORDER_ID) AS DISC_WORKORDER_COMBINE,
UPPER(bcl.OPERID) AS OPERID_UPPERCASE,
LOWER(bd.DISC_SOURCE) AS DISC_SOURCE_LOWERCASE,
INITCAP(bc.CYCLE_NAME) AS CYCLE_NAME_INITCAP,
MONTHS_BETWEEN(bc.CLOSEDATE, bc.OPENDATE) AS CYCLE_DURATION_MONTHS,
ROUND((bc.CLOSEDATE - bc.OPENDATE)) AS CYCLE_DURATION_DAYS,
ADD_MONTHS(bc.OPENDATE, 6) AS SIX_MONTHS_AFTER_OPEN,
NEXT_DAY(bc.OPENDATE, 'MONDAY') AS NEXT_MONDAY_AFTER_OPEN,
LAST_DAY(bc.CLOSEDATE) AS LAST_DAY_CLOSEDATE,
bd.REFITEM_CYCLES * bducd.REFITEM_CYCLE_OFFSET AS CYCLE_MULTIPLICATION,
bd.FORCE_OUTPUT_EVENT + bducd.DISC_SOURCE_TYPE AS EVENT_SUM,
bd.DISC_SOURCE_FACTOR / NULLIF(bducd.REFVALUE_UNIT, 0) AS FACTOR_DIVISION,
POWER(bd.DISC_TYPE, 2) AS DISC_TYPE_SQUARED,
SQRT(bd.REFITEM_CYCLES) AS SQRT_REFITEM_CYCLES,
SUM(CASE WHEN bcl.SUCCESS = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY bd.TARIFFPLAN_ID) AS SUCCESSFUL_LOGINS,
COUNT(CASE WHEN bc.CYCLE_STATUS = 1 THEN 1 END) OVER (PARTITION BY bd.TARIFFPLAN_ID) AS ACTIVE_CYCLES,
MAX(bd.DISC_SOURCE_FACTOR) OVER (PARTITION BY bd.TARIFFPLAN_TYPE) AS MAX_DISC_SOURCE_FACTOR_PER_TYPE,
MIN(bcl.LOGONTIME) OVER (PARTITION BY bcl.LOGONID) AS FIRST_LOGIN_TIME_PER_USER,
AVG(bd.FREEREFITEM_MIN_USAGE) OVER (PARTITION BY bd.DISC_ENTITY) AS AVG_USAGE_PER_ENTITY,
bd.TARIFFPLAN_ID + bducd.DISC_ENTITY AS TARIFFDISC_COMBINED_ID,
bcl.SID * bc.CYCLE_ID AS SID_CYCLE_MULTIPLIER,
bd.PLANITEM_ID || '-' || bducd.WORKORDER_ID AS PLANITEM_WORKORDER_COMBINE,
(bd.START_CYCLE_OFFSET + bc.CYCLE_SCHEMA_ID) AS CYCLE_OFFSET_SUM,
(bd.VALID_CYCLES - bducd.VALID_CYCLES) AS VALID_CYCLES_DIFFERENCE,
(bd.DISC_SOURCE_FACTOR * bd.CONVERT_TYPE) AS DISC_SOURCE_CONVERTED,
CASE
WHEN (bd.REFVALUE_CALC_TYPE = 1 AND bducd.REFVALUE_CALC_TYPE = 2) THEN 'Type Combination 1-2'
WHEN (bd.REFVALUE_CALC_TYPE = 2 AND bducd.REFVALUE_CALC_TYPE = 1) THEN 'Type Combination 2-1'
ELSE 'Other Type Combinations'
END AS REFVALUE_TYPE_COMBINATION,
bd.TARIFFPLAN_ID + 0 AS TD1,
bd.TARIFFPLAN_ID + 1 AS TD2,
bd.TARIFFPLAN_ID + 2 AS TD3,
bd.TARIFFPLAN_ID + 3 AS TD4,
bd.TARIFFPLAN_ID + 4 AS TD5,
bd.TARIFFPLAN_ID + 5 AS TD6,
bd.TARIFFPLAN_ID + 6 AS TD7,
bd.TARIFFPLAN_ID + 7 AS TD8,
bd.TARIFFPLAN_ID + 8 AS TD9,
bd.TARIFFPLAN_ID + 9 AS TD10,
bd.TARIFFPLAN_ID + 10 AS TD11,
bd.TARIFFPLAN_ID + 11 AS TD12,
bd.TARIFFPLAN_ID + 12 AS TD13,
bd.TARIFFPLAN_ID + 13 AS TD14,
bd.TARIFFPLAN_ID + 14 AS TD15,
bd.TARIFFPLAN_ID + 15 AS TD16,
bd.TARIFFPLAN_ID + 16 AS TD17,
bd.TARIFFPLAN_ID + 17 AS TD18,
bd.TARIFFPLAN_ID + 18 AS TD19,
bd.TARIFFPLAN_ID + 19 AS TD20,
bducd.DISC_ENTITY * 1 AS UCD_DE1,
bducd.DISC_ENTITY * 2 AS UCD_DE2,
bducd.DISC_ENTITY * 3 AS UCD_DE3,
bducd.DISC_ENTITY * 4 AS UCD_DE4,
bducd.DISC_ENTITY * 5 AS UCD_DE5,
bducd.DISC_ENTITY * 6 AS UCD_DE6,
bducd.DISC_ENTITY * 7 AS UCD_DE7,
bducd.DISC_ENTITY * 8 AS UCD_DE8,
bducd.DISC_ENTITY * 9 AS UCD_DE9,
bducd.DISC_ENTITY * 10 AS UCD_DE10,
bd.REFITEM_CYCLES - bducd.REFITEM_CYCLES AS REF_CYCLES_DIFF,
bd.FREEREFITEM_MIN_USAGE + bducd.FREEREFITEM_MIN_USAGE AS TOTAL_MIN_USAGE,
LISTAGG(bd.DISC_COND, ', ') WITHIN GROUP (ORDER BY bd.DISC_COND) OVER (PARTITION BY bd.TARIFFPLAN_ID) AS LISTAGG_DISC_COND,
LISTAGG(bducd.SPECDISCT_EXPR, '; ') WITHIN GROUP (ORDER BY bducd.SPECDISCT_EXPR) OVER (PARTITION BY bducd.PLANITEM_ID) AS LISTAGG_SPECDISCT_EXPR,
CONCAT(CONCAT(bd.DISC_ITEM_CODE, '-'), bducd.WORKORDER_ID) AS CONCAT_DISC_WORKORDER,
SUBSTR(bd.DISC_COND, 1, 10) AS DISC_COND_SUBSTR,
REPLACE(bd.NOTE, ' ', '_') AS NOTE_REPLACED,
MOD(bd.TARIFFPLAN_ID, 100) AS TARIFFPLAN_ID_MOD,
FLOOR(bducd.REFVALUE_UNIT / 2) AS FLOOR_REFVALUE_UNIT,
CEIL(bd.FREEREFITEM_MIN_USAGE / 1000) AS CEIL_MIN_USAGE,
ROUND(bd.DISC_SOURCE_FACTOR, 2) AS ROUND_DISC_SOURCE_FACTOR,
CASE
WHEN bd.INUSE = 1 AND bducd.INUSE = 1 THEN 'Both In Use'
WHEN bd.INUSE = 1 THEN 'BD In Use Only'
WHEN bducd.INUSE = 1 THEN 'UCD In Use Only'
ELSE 'Neither In Use'
END AS INUSE_STATUS,
CASE
WHEN bc.CYCLE_STATUS = 1 AND bd.DISC_TYPE = 1 THEN 'Active Cycle & Type 1'
WHEN bc.CYCLE_STATUS = 2 AND bd.DISC_TYPE = 2 THEN 'Closed Cycle & Type 2'
ELSE 'Other Cycle Status & Types'
END AS CYCLE_TYPE_COMBINATION,
TRUNC(bcl.LOGONTIME, 'MONTH') AS LOGON_MONTH,
EXTRACT(YEAR FROM bc.OPENDATE) AS OPEN_YEAR,
EXTRACT(MONTH FROM bc.CLOSEDATE) AS CLOSE_MONTH,
bd.TARIFFPLAN_ID + 20 AS TD21,
bd.TARIFFPLAN_ID + 21 AS TD22,
bd.TARIFFPLAN_ID + 22 AS TD23,
bd.TARIFFPLAN_ID + 23 AS TD24,
bd.TARIFFPLAN_ID + 24 AS TD25,
bd.TARIFFPLAN_ID + 25 AS TD26,
bd.TARIFFPLAN_ID + 26 AS TD27,
bd.TARIFFPLAN_ID + 27 AS TD28,
bd.TARIFFPLAN_ID + 28 AS TD29,
bd.TARIFFPLAN_ID + 29 AS TD30,
bducd.DISC_ENTITY * 11 AS UCD_DE11,
bducd.DISC_ENTITY * 12 AS UCD_DE12,
bducd.DISC_ENTITY * 13 AS UCD_DE13,
bducd.DISC_ENTITY * 14 AS UCD_DE14,
bducd.DISC_ENTITY * 15 AS UCD_DE15,
bducd.DISC_ENTITY * 16 AS UCD_DE16,
bducd.DISC_ENTITY * 17 AS UCD_DE17,
bducd.DISC_ENTITY * 18 AS UCD_DE18,
bducd.DISC_ENTITY * 19 AS UCD_DE19,
bducd.DISC_ENTITY * 20 AS UCD_DE20,
bd.TARIFFPLAN_ID + 30 AS TD31,
bd.TARIFFPLAN_ID + 31 AS TD32,
bd.TARIFFPLAN_ID + 32 AS TD33,
bd.TARIFFPLAN_ID + 33 AS TD34,
bd.TARIFFPLAN_ID + 34 AS TD35,
bd.TARIFFPLAN_ID + 35 AS TD36,
bd.TARIFFPLAN_ID + 36 AS TD37,
bd.TARIFFPLAN_ID + 37 AS TD38,
bd.TARIFFPLAN_ID + 38 AS TD39,
bd.TARIFFPLAN_ID + 39 AS TD40,
bducd.DISC_ENTITY * 21 AS UCD_DE21,
bducd.DISC_ENTITY * 22 AS UCD_DE22,
bducd.DISC_ENTITY * 23 AS UCD_DE23,
bducd.DISC_ENTITY * 24 AS UCD_DE24,
bducd.DISC_ENTITY * 25 AS UCD_DE25,
bducd.DISC_ENTITY * 26 AS UCD_DE26,
bducd.DISC_ENTITY * 27 AS UCD_DE27,
bducd.DISC_ENTITY * 28 AS UCD_DE28,
bducd.DISC_ENTITY * 29 AS UCD_DE29,
bducd.DISC_ENTITY * 30 AS UCD_DE30,
-- Additional Conditions and Filters
CASE
WHEN bd.VALID_CYCLE_TYPE IN (1, 2, 3) THEN 'Valid Cycle Type 1-3'
WHEN bd.VALID_CYCLE_TYPE IN (4, 5) THEN 'Valid Cycle Type 4-5'
ELSE 'Other Valid Cycle Types'
END AS VALID_CYCLE_TYPE_DESC,
CASE
WHEN bc.CYCLE_STATUS = 1 AND bd.INUSE = 1 THEN 'Active and In Use'
WHEN bc.CYCLE_STATUS = 0 AND bd.INUSE = 0 THEN 'Inactive and Not In Use'
ELSE 'Mixed Status'
END AS STATUS_COMBINATION,
-- Final Repetitions to Ensure Length
bd.TARIFFPLAN_ID + 40 AS TD41,
bd.TARIFFPLAN_ID + 41 AS TD42,
bd.TARIFFPLAN_ID + 42 AS TD43,
bd.TARIFFPLAN_ID + 43 AS TD44,
bd.TARIFFPLAN_ID + 44 AS TD45,
bd.TARIFFPLAN_ID + 45 AS TD46,
bd.TARIFFPLAN_ID + 46 AS TD47,
bd.TARIFFPLAN_ID + 47 AS TD48,
bd.TARIFFPLAN_ID + 48 AS TD49,
bd.TARIFFPLAN_ID + 49 AS TD50,
bducd.DISC_ENTITY * 31 AS UCD_DE31,
bducd.DISC_ENTITY * 32 AS UCD_DE32,
bducd.DISC_ENTITY * 33 AS UCD_DE33,
bducd.DISC_ENTITY * 34 AS UCD_DE34,
bducd.DISC_ENTITY * 35 AS UCD_DE35,
bducd.DISC_ENTITY * 36 AS UCD_DE36,
bducd.DISC_ENTITY * 37 AS UCD_DE37,
bducd.DISC_ENTITY * 38 AS UCD_DE38,
bducd.DISC_ENTITY * 39 AS UCD_DE39,
bducd.DISC_ENTITY * 40 AS UCD_DE40,
-- Final Fields to Reach Required Length
bd.TARIFFPLAN_ID + 50 AS TD51,
bd.TARIFFPLAN_ID + 51 AS TD52,
bd.TARIFFPLAN_ID + 52 AS TD53,
bd.TARIFFPLAN_ID + 53 AS TD54,
bd.TARIFFPLAN_ID + 54 AS TD55,
bd.TARIFFPLAN_ID + 55 AS TD56,
bd.TARIFFPLAN_ID + 56 AS TD57,
bd.TARIFFPLAN_ID + 57 AS TD58,
bd.TARIFFPLAN_ID + 58 AS TD59,
bd.TARIFFPLAN_ID + 59 AS TD60,
bducd.DISC_ENTITY * 41 AS UCD_DE41,
bducd.DISC_ENTITY * 42 AS UCD_DE42,
bducd.DISC_ENTITY * 43 AS UCD_DE43,
bducd.DISC_ENTITY * 44 AS UCD_DE44,
bducd.DISC_ENTITY * 45 AS UCD_DE45,
bducd.DISC_ENTITY * 46 AS UCD_DE46,
bducd.DISC_ENTITY * 47 AS UCD_DE47,
bducd.DISC_ENTITY * 48 AS UCD_DE48,
bducd.DISC_ENTITY * 49 AS UCD_DE49,
bducd.DISC_ENTITY * 50 AS UCD_DE50
FROM
GSH.BILLDISC_DEF bd
INNER JOIN GSH.BILLDISC_DEF_UCD bducd
ON bd.TARIFFPLAN_ID = bducd.TARIFFPLAN_ID
AND bd.PLANITEM_ID = bducd.PLANITEM_ID
LEFT JOIN GSH.BILLINGCLIENTLOG bcl
ON bd.TARIFFPLAN_ID = bcl.LOGONID
LEFT JOIN GSH.BILLINGCYCLE bc
ON bcl.SID = bc.CYCLE_ID
WHERE
-- Complex WHERE Conditions
(
(bd.DISC_TYPE = 1 AND bd.INUSE = 1)
OR
(bducd.DISC_TYPE = 2 AND bducd.INUSE = 0)
)
AND
(
bc.CYCLE_STATUS IN (1, 2, 3)
AND
bcl.SUCCESS = 1
)
AND
(
bd.REFITEM_CYCLES BETWEEN 1 AND 10
OR
bducd.REFITEM_CYCLES BETWEEN 5 AND 15
)
AND
(
bd.DISC_COND LIKE '%Discount%'
OR
bducd.DISC_COND LIKE '%Condition%'
)
AND
(
bcl.IPADDR IS NOT NULL
AND
bcl.MACADDR IS NOT NULL
)
AND
(
bc.OPENDATE < SYSDATE
AND
bc.CLOSEDATE > SYSDATE
)
AND
(
bd.TARIFFPLAN_TYPE IN (100, 200, 300)
OR
bducd.TARIFFPLAN_TYPE IN (400, 500)
)
AND
(
bcl.LOGONTIME BETWEEN ADD_MONTHS(SYSDATE, -6) AND SYSDATE
)
AND
(
bc.CYCLE_NAME LIKE 'Cycle%'
)
AND
(
(
bd.DISC_SOURCE_FACTOR > 0
AND
bducd.REFVALUE_UNIT > 1
)
OR
(
bd.DISC_SOURCE_FACTOR < 5
AND
bducd.REFVALUE_UNIT < 10
)
)
AND
(
NVL(bd.NOTE, 'No Note') <> 'Deprecated'
)
AND
(
bducd.SPECDISCT_EXPR IS NOT NULL
OR
bd.SPECDISCT_EXPR IS NOT NULL
)
AND
(
(
bd.CONVERT_TYPE = 1
AND
bducd.CONVERT_TYPE = 0
)
OR
(
bd.CONVERT_TYPE = 0
AND
bducd.CONVERT_TYPE = 1
)
)
AND
(
EXISTS (
SELECT 1
FROM GSH.BILLDISC_DEF bd_inner
WHERE bd_inner.TARIFFPLAN_ID = bd.TARIFFPLAN_ID
AND bd_inner.DISC_ENTITY = bd.DISC_ENTITY
)
)
AND
(
NOT EXISTS (
SELECT 1
FROM GSH.BILLINGCLIENTLOG bcl_inner
WHERE bcl_inner.OPERID = bcl.OPERID
AND bcl_inner.SUCCESS = 0
)
)
AND
(
bd.DISC_ITEM_CODE IN (
SELECT DISC_ITEM_CODE
FROM GSH.BILLDISC_DEF
WHERE DISC_TYPE = 1
)
)
AND
(
bducd.WORKORDER_ID IS NOT NULL
)
AND
(
bc.CYCLE_ID IS NOT NULL
)
ORDER BY
bd.TARIFFPLAN_ID,
bducd.PLANITEM_ID,
bcl.LOGONTIME DESC,
bc.CLOSEDATE ASC;
Anything else we need to know?
No response
This is an old problem that requires a special person to be responsible for, and can be done as an optimization requirement
