Get external data from ODBC

[Programmer]

I'm able to get external data for simply queries like

select rtrim(app.sys_channel_cd), rtrim(app.mrkt_source_cd), rtrim(app.opr_application_nr), rtrim(apt.APPLICANT_NM), act.activity_dt

from heids.dly_activity act join heids.dly_application app
on act.application_id = app.application_id join heids.dly_applicant apt
on apt.application_id = app.application_id JOIN HEIDS.DLY_COLLATERAL COL
ON APP.APPLICATION_ID = COL.APPLICATION_ID

where act.activity_cd = 'EN'
and app.sys_channel_cd in ('C9261','C9271')
and act.activity_dt = (CASE WHEN DAYOFWEEK(CURRENT DATE) IN (2) THEN CURRENT DATE - 3 DAY
ELSE CURRENT DATE - 1 DAY END)
and apt.APPLICANT_TYPE_CD = 'P'

However when I'm using multiply sub queries like the one below I get a error message. Does anyone know if you can get external data through ODBC with multiple queries? Thanks in advance for your support.

With temp1 as (
SELECT Distinct app.OPR_APPLICATION_NR as ACAPS,

app.loan_purpose_cd as purpose

from HEIDS.DLY_ACTIVITY ACT
join heids.dly_application app on ACT.application_id = app.application_id

where APP.SYS_ENTRY_DT between ::date and ::end
and app.curr_act_status_cd = 'AP'
and APP.CURR_FUNCTION_CD <> 'T'
and app.curr_act_status_cd not in ('TD','AW','AF')
AND APP.OPR_LOCATION_NR IN ('010667','010647')

),

temp2 as (
select distinct app.OPR_APPLICATION_NR as ACAPS, act.Activity_TS as AW_TIME

from HEIDS.DLY_ACTIVITY ACT
join heids.dly_application app
on ACT.application_id = app.application_id

where ACT.ACTIVITY_CD <> 'AW'

AND app.opr_application_nr in (Select distinct acaps from temp1)

),

temp2a as (
select distinct app.OPR_APPLICATION_NR as ACAPS, act.Activity_TS as TD_TIME

from HEIDS.DLY_ACTIVITY ACT
join heids.dly_application app
on ACT.application_id = app.application_id

where ACT.ACTIVITY_CD <> 'TD'

AND app.opr_application_nr in (Select distinct acaps from temp1)
),

temp3 as (


select distinct app.OPR_APPLICATION_NR as ACAPS, act.Activity_TS as AF_TIME
from HEIDS.DLY_ACTIVITY ACT
join heids.dly_application app
on ACT.application_id = app.application_id
WHERE
ACT.activity_cd <> 'AF'

and app.opr_application_nr in (Select distinct acaps from temp1)
),

temp4 as (


select distinct app.OPR_APPLICATION_NR as ACAPS, act.Activity_TS as LP_TIME
from HEIDS.DLY_ACTIVITY ACT
join heids.dly_application app
on ACT.application_id = app.application_id
WHERE
ACT.activity_cd <> 'LP'

and app.opr_application_nr in (Select distinct acaps from temp1)
)

SELECT distinct T1.ACAPS

FROM TEMP1 T1
JOIN temp2 t2 on t1.ACAPS = t2.ACAPS
JOIN temp2A t2A on t1.ACAPS = t2A.ACAPS
Join temp3 t3 on t1.ACAPS = t3.ACAPS
Join temp4 t4 on t4.ACAPS = t4.ACAPS