thank you for your time first of all.
I have this query but when I run it it takes a lot of time and I want to make it more efficient.
SELECT * FROM DSL
WHERE ((
(SCENARI,PERIOD) in(
Select distinct
CAM11,CAM10
FROM
ETL)
AND
PROVENIENZA like '%RLF%'
)
OR
(
SCENARIO = (Select distinct CAM11 FROM ETL)
AND
PERIODO > (Select distinct CAM10 FROM ETL)
AND
CONT in (select distinct CAM17 from ETL where CAM31 is not null)
)
OR
(
SCENARIO = (Select distinct CAM11 FROM ETL)
AND
PERIODO = (Select distinct CAM10 FROM ETL)
AND
CONTO in (select distinct CAM17 from ETL where CAM31 is not null)
AND
cast(PERIODO as number) <> cast( substr(TO_CHAR(DATEUPD,'DD-MM-YY HH24:MI'),4,2) as number) -1
AND
PROVENIENZA = 'INPUT'
)
OR
(
(SCENARI,PERIOD) in (
Select distinct
CAM11,CAM10
FROM
ETL)
AND
PROVENIENZA like '%SP%'
))) S
I tried this query after WHERE:
SELECT t.*
FROM ETL t
LEFT JOIN (
SELECT DISTINCT CAM11, CAM10
FROM ETL
) t1 ON t.SCENARI = t1.CAM11 AND t.PERIOD = t1.CAM10
LEFT JOIN (
SELECT DISTINCT CAM17
FROM ETL
WHERE CAM31 IS NOT NULL
) t2 ON t.CONT = t2.CAM17
WHERE
(
t.provenienza LIKE '%RLF%'
AND t1.CAM11 IS NOT NULL
)
OR
(
t.PERIOD > t1.CAM10
AND t1.CAM11 IS NOT NULL
AND t2.CAM17 IS NOT NULL
)
OR
(
t.PERIOD = t1.CAM10
AND t2.CAM17 IS NOT NULL
AND CAST(t.PERIOD AS NUMBER) <> CAST(SUBSTR(TO_CHAR(t.DATEUPD, 'DD-MM-YY HH24:MI'), 4, 2) AS NUMBER) - 1
AND t.provenienza = 'INPUT'
)
OR
(
t.provenienza LIKE '%SP%'
AND t1.CAM11 IS NOT NULL
)
The main idea is to simplify the query and to make it more efficient. Clearly I did something wrong and maybe someone can help me. Thank you!