DECLARE
FUNCTION random_question_query (
user_id_in IN INTEGER,
domain_id_in IN INTEGER DEFAULT NULL,
topic_id_in IN INTEGER DEFAULT NULL,
difficulty_id_in IN INTEGER DEFAULT NULL,
filter_in IN VARCHAR2 DEFAULT NULL,
unplayed_in IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2
IS
l_query qdb_config.maxvarchar2
:= 'SELECT question_id FROM (SELECT question_id FROM dg_training_questions_v tq';
l_domain_count INTEGER;
l_cur SYS_REFCURSOR;
l_selected INTEGER;
BEGIN
SELECT COUNT (*)
INTO l_domain_count
FROM qdb_user_domains
WHERE user_id = user_id_in;
l_query :=
l_query
|| CASE
WHEN domain_id_in IS NULL AND topic_id_in IS NULL
THEN
CASE
WHEN l_domain_count > 0
THEN
' WHERE domain_id IN (SELECT domain_id FROM qdb_user_domains WHERE user_id = '
|| user_id_in
|| ')'
END
WHEN topic_id_in IS NOT NULL
THEN
' WHERE tq.topic_id IN (SELECT t2.topic_id from qdb_topics t2'
|| ' CONNECT BY prior t2.topic_id = t2.parent_topic_id START WITH t2.topic_id = '
|| topic_id_in
|| ')'
WHEN domain_id_in IS NOT NULL
THEN
' WHERE domain_id = ' || domain_id_in
END
|| CASE
WHEN difficulty_id_in IS NOT NULL
THEN
' AND difficulty_id = ' || difficulty_id_in
END
|| CASE
WHEN filter_in IS NOT NULL
THEN
/* Find questions with topics whose names match. */
' AND UPPER (tq.topic) LIKE ''%'' || upper (filter_in) || ''%'''
END;
l_query :=
l_query
|| CASE
WHEN unplayed_in
THEN
'MINUS SELECT z.question_id FROM qdb_quizzes z, qdb_compev_answers a'
|| ' WHERE a.user_id = '
|| user_id_in
|| 'AND z.comp_event_id = a.comp_event_id'
END
|| ') ORDER BY DBMS_RANDOM.VALUE ()';
RETURN l_query;
END;
BEGIN
NULL;
END;