DataWrap OCI: Unable to prepare query

[Nilisha1]

Hi,

I am running a SQL below which seems to run fine in Toad but gives me an Data Wrap OCI : Unable to prepare query error what could be wrong with the query

select * from
(
SELECT
f_student_unit_enr_snapshot.person_id,
f_student_unit_enr_snapshot.snapshot_date_key,
trunc(snapshot_dt),
f_student_unit_enr_snapshot.course_commence_date_key,
CASE WHEN substr(SNAPSHOT_DATE_KEY,1,4) = substr(f_student_unit_enr_snapshot.course_commence_date_key,1,4)
THEN 'Commencing'
WHEN substr(SNAPSHOT_DATE_KEY,1,4) > substr(f_student_unit_enr_snapshot.course_commence_date_key,1,4)
THEN 'Continuing'
ELSE 'Not Applicable'
END,
CASE WHEN substr(dim_course_version_ou_v.course_cd,1,1) in ('6','7')
THEN 'The College'
WHEN dim_course_version_ou_v.course_band_name like '%WSTC%'
THEN 'The College'
ELSE (CASE WHEN dim_course_version_ou_v.abbreviated_school = 'Unknown'
THEN 'Other'
WHEN dim_course_version_ou_v.abbreviated_school = 'Compting, Engineering & Maths Prog'
THEN 'Computing, Engineering & Maths'
WHEN dim_course_version_ou_v.abbreviated_school = 'Sciences & Health'
THEN 'Science & Health'
WHEN dim_course_version_ou_v.abbreviated_school = 'The College Health & Science'
THEN 'Science & Health'
WHEN dim_course_version_ou_v.abbreviated_school = 'The College Arts'
THEN 'Humanities & Communication Arts'
WHEN dim_course_version_ou_v.abbreviated_school = 'The College Business & Law'
THEN 'Business'
ELSE dim_course_version_ou_v.abbreviated_school

END)
END,
dim_course_version_ou_v.course_cd,
dim_course_version_ou_v.course_long_name,
dim_course_version_ou_v.COURSE_BAND_NAME,
dim_course_version_ou_v.broad_rpt_course_lvl_sname,
d_teaching_calendar.teaching_cal_name,
trunc(d_teaching_calendar.teaching_cal_start_dt),
(trunc(f_student_unit_enr_snapshot.snapshot_dt) - trunc(d_teaching_calendar.teaching_cal_start_dt)),
d_student_stats_profile.international_student_type,
count( distinct f_student_unit_enr_snapshot.person_id)
FROM
f_student_unit_enr_snapshot,
dim_course_version_ou_v,
d_teaching_calendar,
d_student_stats_profile
WHERE f_student_unit_enr_snapshot.snapshot_date_key between 20180501 and 20181231
AND f_student_unit_enr_snapshot.D_TEACHING_CALENDAR_KEY = d_teaching_calendar.D_TEACHING_CALENDAR_KEY
AND f_student_unit_enr_snapshot.DIM_COURSE_VERSION_KEY = dim_course_version_ou_v.DIM_COURSE_VERSION_KEY
AND f_student_unit_enr_snapshot.D_STUDENT_STATS_PROFILE_KEY = d_student_stats_profile.D_STUDENT_STATS_PROFILE_KEY
--AND DIM_COURSE_VERSION.CURR_RSPNSBL_OU_KEY = d_ou_key
AND d_teaching_calendar.teaching_cal_type_cd IN ('TCH-SPRING',
'TCH-2H',
'TCH-QUART3',
'TCH-QUART4',
'TCH-TERM2')
AND dim_course_version_ou_v.BROAD_RPT_COURSE_LVL_SNAME in ('Undergraduate','Postgraduate')
AND substr(COURSE_COMMENCE_DATE_KEY,1,4)='2018'
AND NOT EXISTS
(SELECT *
FROM F_STUDENT_UNIT_ENR_SNAPSHOT c,
(SELECT *
FROM D_TEACHING_CALENDAR
WHERE teaching_cal_type_cd IN ('TCH-AUTUMN',
'TCH-1H',
'TCH-QUART1',
'TCH-QUART2',
'TCH-TERM1')
AND extract(YEAR FROM census_dt) = 2018) b
 dim_course_version_ou_v e
WHERE c.D_TEACHING_CALENDAR_KEY = b.D_TEACHING_CALENDAR_KEY
AND substr(COURSE_COMMENCE_DATE_KEY,1,4) in (2018)
AND c.snapshot_date_key = 20180401
AND c.DIM_COURSE_VERSION_KEY=e.DIM_COURSE_VERSION_KEY
AND f_student_unit_enr_snapshot.person_id = c.person_id
AND dim_course_version_ou_v.GOVT_COURSE_TYPE = e.GOVT_COURSE_TYPE
)
group by person_id,
snapshot_date_key,
SNAPSHOT_DT,
abbreviated_school,
broad_rpt_course_lvl_sname,
COURSE_BAND_NAME,
TEACHING_CAL_NAME,
TEACHING_CAL_START_DT,
INTERNATIONAL_STUDENT_TYPE,
COURSE_COMMENCE_DATE_KEY,
dim_course_version_ou_v.course_cd,
dim_course_version_ou_v.course_long_name
) s_student_unit_enr_ss_my
where
NOT EXISTS
(SELECT *
FROM F_STUDENT_UNIT_ENR_SNAPSHOT c,
(SELECT *
FROM D_TEACHING_CALENDAR
WHERE teaching_cal_type_cd IN ('TCH-AUTUMN',
'TCH-1H',
'TCH-QUART1',
'TCH-QUART2',
'TCH-TERM1')
AND EXTRACT (YEAR FROM census_dt) = 2018) b,
(SELECT *
FROM dim_course_version
WHERE uwsc_flag = 'Y') e
WHERE c.D_TEACHING_CALENDAR_KEY = b.D_TEACHING_CALENDAR_KEY
AND SUBSTR (COURSE_COMMENCE_DATE_KEY, 1, 4) = '2018'
AND c.snapshot_date_key = 20180401
AND c.DIM_COURSE_VERSION_KEY = e.DIM_COURSE_VERSION_KEY
AND s_student_unit_enr_ss_my.person_id = c.person_id)
Union
select * from
(
SELECT
f_student_unit_enr_snapshot.person_id,
f_student_unit_enr_snapshot.snapshot_date_key,
trunc(snapshot_dt),
f_student_unit_enr_snapshot.course_commence_date_key,
CASE WHEN substr(SNAPSHOT_DATE_KEY,1,4) = substr(f_student_unit_enr_snapshot.course_commence_date_key,1,4)
THEN 'Commencing'
WHEN substr(SNAPSHOT_DATE_KEY,1,4) > substr(f_student_unit_enr_snapshot.course_commence_date_key,1,4)
THEN 'Continuing'
ELSE 'Not Applicable'
END,
CASE WHEN substr(dim_course_version_ou_v.course_cd,1,1) in ('6','7')
THEN 'The College'
WHEN dim_course_version_ou_v.course_band_name like '%WSTC%'
THEN 'The College'
ELSE (CASE WHEN dim_course_version_ou_v.abbreviated_school = 'Unknown'
THEN 'Other'
WHEN dim_course_version_ou_v.abbreviated_school = 'Compting, Engineering & Maths Prog'
THEN 'Computing, Engineering & Maths'
WHEN dim_course_version_ou_v.abbreviated_school = 'Sciences & Health'
THEN 'Science & Health'
WHEN dim_course_version_ou_v.abbreviated_school = 'The College Health & Science'
THEN 'Science & Health'
WHEN dim_course_version_ou_v.abbreviated_school = 'The College Arts'
THEN 'Humanities & Communication Arts'
WHEN dim_course_version_ou_v.abbreviated_school = 'The College Business & Law'
THEN 'Business'
ELSE dim_course_version_ou_v.abbreviated_school

END)
END,
dim_course_version_ou_v.course_cd,
dim_course_version_ou_v.course_long_name,
dim_course_version_ou_v.COURSE_BAND_NAME,
dim_course_version_ou_v.broad_rpt_course_lvl_sname,
d_teaching_calendar.teaching_cal_name,
trunc(d_teaching_calendar.teaching_cal_start_dt),
(trunc(f_student_unit_enr_snapshot.snapshot_dt) - trunc(d_teaching_calendar.teaching_cal_start_dt)),
d_student_stats_profile.international_student_type,
count( distinct f_student_unit_enr_snapshot.person_id)
FROM
f_student_unit_enr_snapshot,
dim_course_version_ou_v,
d_teaching_calendar,
d_student_stats_profile
WHERE f_student_unit_enr_snapshot.snapshot_date_key between 20190501 and 20191231
AND f_student_unit_enr_snapshot.D_TEACHING_CALENDAR_KEY = d_teaching_calendar.D_TEACHING_CALENDAR_KEY
AND f_student_unit_enr_snapshot.DIM_COURSE_VERSION_KEY = dim_course_version_ou_v.DIM_COURSE_VERSION_KEY
AND f_student_unit_enr_snapshot.D_STUDENT_STATS_PROFILE_KEY = d_student_stats_profile.D_STUDENT_STATS_PROFILE_KEY
AND d_teaching_calendar.teaching_cal_type_cd IN ('TCH-SPRING',
'TCH-2H',
'TCH-QUART3',
'TCH-QUART4',
'TCH-TERM2')
AND dim_course_version_ou_v.BROAD_RPT_COURSE_LVL_SNAME in ('Undergraduate','Postgraduate')
AND substr(COURSE_COMMENCE_DATE_KEY,1,4)='2019'
AND NOT EXISTS
(SELECT *
FROM F_STUDENT_UNIT_ENR_SNAPSHOT c,
(SELECT *
FROM D_TEACHING_CALENDAR
WHERE teaching_cal_type_cd IN ('TCH-AUTUMN',
'TCH-1H',
'TCH-QUART1',
'TCH-QUART2',
'TCH-TERM1')
AND extract(YEAR FROM census_dt) = 2019) b
dim_course_version_ou_v e
WHERE c.D_TEACHING_CALENDAR_KEY = b.D_TEACHING_CALENDAR_KEY
AND substr(COURSE_COMMENCE_DATE_KEY,1,4) in (2017)
AND c.snapshot_date_key = 20170401
AND c.DIM_COURSE_VERSION_KEY=e.DIM_COURSE_VERSION_KEY
AND f_student_unit_enr_snapshot.person_id = c.person_id
AND dim_course_version_ou_v.GOVT_COURSE_TYPE = e.GOVT_COURSE_TYPE
)
group by person_id,
snapshot_date_key,
SNAPSHOT_DT,
abbreviated_school,
broad_rpt_course_lvl_sname,
COURSE_BAND_NAME,
TEACHING_CAL_NAME,
TEACHING_CAL_START_DT,
INTERNATIONAL_STUDENT_TYPE,
COURSE_COMMENCE_DATE_KEY,
dim_course_version_ou_v.course_cd,
dim_course_version_ou_v.course_long_name
) s_student_unit_enr_ss_my
where
NOT EXISTS
(SELECT *
FROM F_STUDENT_UNIT_ENR_SNAPSHOT c,
(SELECT *
FROM D_TEACHING_CALENDAR
WHERE teaching_cal_type_cd IN ('TCH-AUTUMN',
'TCH-1H',
'TCH-QUART1',
'TCH-QUART2',
'TCH-TERM1')
AND EXTRACT (YEAR FROM census_dt) = 2019) b,
(SELECT *
FROM dim_course_version
WHERE uwsc_flag = 'Y') e
WHERE c.D_TEACHING_CALENDAR_KEY = b.D_TEACHING_CALENDAR_KEY
AND SUBSTR (COURSE_COMMENCE_DATE_KEY, 1, 4) = '2019'
AND c.snapshot_date_key = 20190401
AND c.DIM_COURSE_VERSION_KEY = e.DIM_COURSE_VERSION_KEY
AND s_student_unit_enr_ss_my.person_id = c.person_id)

[AbhilashR]

Hi @Nilisha1, can you try run chunks of your query in Alteryx and see where does it breakdown? Also, give the CASE statement section of your code a column alias. E.g. 

Toad is a bit forgiving if we don't give explicit column names, while Alteryx is a bit more strict.

[Nilisha1]

Hi,

Thanks for that but it did not work.

Nilisha

[AbhilashR]

I would urge you to try and run sections of your SQL in the Input tool to help identify the issue.

[urbot]

@AbhilashR when query is working correctly on normal sql why its not working in alteryx either normal input/dynamic input, u have any idea ?