Query with joins and clause conditions in Load script
Hi Guys,
I have some QVDs generated with information retrieved from database. Now I need to join some of the tables and get new columns base on certain conditions (please see below) based on a MAX date. In SQL the query is similar to the following:
SELECT qc.Id,
qc.qualdate,
CASE WHEN mc.isreq = 0 and qc.result = 'Q' THEN 1 ELSE 0 END NrQ,
CASE WHEN mc.isreq = 0 and qc.result = 'NOT Q' THEN 1 ELSE 0 END NrNotQ,
CASE WHEN mc.isreq = 1 and qc.result = 'RQ' THEN 1 ELSE 0 END NrRQ,
CASE WHEN mc.isreq = 1 and qc.result = 'NOT RQ' THEN 1 ELSE 0 END NrNotRQ
FROM mapcategory AS mc INNER JOIN qualcategory AS qc ON mc.id = qc.id
INNER JOIN candidate AS c ON mc.id = c.id
WHERE qc.qualdate = (select max(vq2.qualdate) from qualcategory vq2
where vq2.Idsupplier = qc.Idsupplier
and vq2.Id = qc.Id)
Considering that i need to implement this logic in QV load script, what should be the best approach to avoid performance issues?
