Hello guys,
I'm translating many queries and I have some doubts on etl strategy.
There are cases with nested subqueries where I must do the selections on fields on all used tables.
Here's an example:
SELECT
table01.C_PDS,
table01.VOLUME
FROM
table01
WHERE
(
table01.C_CARRIER = @Carrier
AND table01.V_ANNO = year(@StartDate)
AND table01.C_PDS IN
(SELECT
table02.PDS FROM
table02
WHERE
(
table02.DT_INI <= @StartDate
AND table02.DT_FINE >= @EndDate
AND table02.COD_TRASP = @Carrier
AND table02.PDS NOT IN
(SELECT
table03.C_PDR FROM
table03
WHERE
(
table03.V_ANNO = year(@StartDate)
AND table03 = @Carrier
)
)))))
@Carrier, @StartDate and @EndDate are the values that i'll use on the front end with set analysis, lists etc.
Please note that I'm working with large volumes of data and each table corresponds to a QVD file.
What's the best approach that you think?
Thanks in advance
