Best way to write load script which uses the same CTE multiple times！！！

[baylor2016]

Hi,

I am using the same CTE statement (query to get patients) for multiple times in my load script in order to get different information like lab test, visit info, medication. I think there might be a way to avoid this duplication. I heard about QVD, not sure whether it is a good choice.

Thanks

Longmatch

My code like this:

Patient:

SQL

WITH Patients as (

SELECT patient_id, patient_name, patient_age

FROM patient

WHERE age >18

)

select *

from Patient;

Medication:

SQL

WITH Patients as (

SELECT patient_id, patient_name, patient_age

FROM patient

WHERE age >18

)

select patient_id, medication

from medication a

inner join patients b on a.patient_id = b.patient_id;

Lab:

SQL

WITH Patients as (

SELECT patient_id, patient_name, patient_age

FROM patient

WHERE age >18

)

select patient_id, labTest

from labTest a

inner join patients b on a.patient_id = b.patient_id;

[jagan]

HI,

Try like this

Patient:

SELECT patient_id, patient_name, patient_age

FROM patient

WHERE age >18;

Medication:

LEFT KEEP(Patient)

select patient_id, medication

from medication;

Lab:

LEFT KEEP(Patient)

select patient_id, labTest

from labTest;

Hope this helps you.

Regards,

Jagan.

[marcus_sommer]

You could use Variables to store redundant script- or expression(parts) and in your example might be a for each loop for your sql-loadings possible - but if there are only those few loads and script-lines I wouldn't change it - readability of script is often a more important point then it's short.

- Marcus