Hi
I have a huge query .. with the similar conditions on column data as listed below:
SELECT
CASE WHEN EXISTS
(SELECT 1
FROM (
SELECT TRIM(UPPER(SYSTEM_NAME)) SYSTEM_NAME,TRIM(UPPER(NVL(CPARTY_ID,'X'))) CPARTY_ID,TRIM(UPPER(LPAD(SHORT_CODE,200,'0')))
SHORT_CODE,BATCHID
FROM VOLTAGE
JOIN MAXVALUES MV ON (MV.IDENTT=1
AND BATCHID >MV.S1BATCHID
)
GROUP BY TRIM(UPPER(SYSTEM_NAME)),TRIM(UPPER(NVL(CPARTY_ID,'X'))),TRIM(UPPER(LPAD(SHORT_CODE,200,'0'))),BATCHID
HAVING COUNT(DISTINCT CLASSIFICATION)>1
) DBV
WHERE DBV.SYSTEM_NAME= TRIM(UPPER(S1.SYSTEM_NAME))
AND DBV.SHORT_CODE=TRIM(UPPER(LPAD(S1.SHORT_CODE,200,'0')))
AND DBV.CPARTY_ID=TRIM(UPPER(NVL(S1.CPARTY_ID,'X')))
)
THEN 'Y'
ELSE 'N'
END AS S1_MULTIPLE_CLASSIFICATON
FROM S1 join S2 join S3 on (conditions)
We have join conditions on the column data to fetch the value in Case when .. I have around 80k records and to fetch all the results .. its taking too long .. Please help !!
I have tried WITH DBV AS (the join condition on the tables MAXVALUES and VOLTAGE ) but no improvement.
Please suggest ..