Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

DataWrap OCI: Unable to prepare query

Nilisha1
5 - Atom

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)

4 REPLIES 4
AbhilashR
15 - Aurora
15 - Aurora

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. 

AbhilashR_0-1593997162046.png

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

 

Nilisha1
5 - Atom

Hi,

 

Thanks for that but it did not work.

 

Nilisha

AbhilashR
15 - Aurora
15 - Aurora

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

urbot
7 - Meteor

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

Labels