[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO ies_svy_ques_data_v(
survey_name,
survey_id,
cycle_name,
survey_cycle_id,
survey_deployment_id,
deployment_name,
dscript_id,
dscript_name,
panel_id,
panel_name,
question_id,
question_name,
question_type,
question_order,
lookup_id,
answer_id,
answer_order,
answer_value,
answer_display_value,
ANSWER_COUNT)
(
SELECT issv.survey_name,
issv.survey_id,
iscv.survey_cycle_name,
iscv.survey_cycle_id,
isdv.survey_deployment_id,
isdv.deployment_name,
issv.dscript_id,
ids.dscript_name,
ip.panel_id,
ip.panel_label,
iq.question_id,
iq.question_label,
iqt.question_type,
iq.question_order,
il.lookup_id,
ia.answer_id,
ia.answer_order,
ia.answer_value,
ia.answer_display_value,
0 answer_count
from ies_svy_surveys_v issv,
ies_svy_cycles_v iscv,
ies_svy_deplyments_v isdv,
ies_deployed_scripts ids,
ies_panels ip,
ies_questions iq,
ies_question_types iqt,
ies_lookups il,
ies_answers ia
WHERE issv.dscript_id = ids.dscript_id
AND ids.active_status = 1
AND ids.dscript_id = ip.dscript_id
AND ip.active_status = 1
AND ip.panel_id = iq.panel_id
AND iq.active_status = 1
AND iq.lookup_id is not null
AND iq.lookup_id = il.lookup_id
AND iq.question_type_id = iqt.question_type_id
AND il.lookup_id = ia.lookup_id
AND ia.active_status = 1
AND isdv.survey_cycle_id = iscv.survey_cycle_id
AND iscv.survey_id = issv.survey_id
AND isdv.survey_deployment_id = p_deployment_id
UNION
SELECT issv.survey_name,
issv.survey_id,
iscv.survey_cycle_name,
iscv.survey_cycle_id,
isdv.survey_deployment_id,
isdv.deployment_name,
issv.dscript_id,
ids.dscript_name,
ip.panel_id,
ip.panel_label,
iq.question_id,
iq.question_label,
iqt.question_type,
iq.question_order,
iq.lookup_id,
0 answer_id,
0 answer_order,
' ' answer_value,
' ' answer_display_value,
0 answer_count
from ies_svy_surveys_v issv,
ies_svy_cycles_v iscv,
ies_svy_deplyments_v isdv,
ies_deployed_scripts ids,
ies_panels ip,
ies_questions iq,
ies_question_types iqt
WHERE issv.dscript_id = ids.dscript_id
AND ids.dscript_id = ip.dscript_id
AND ids.active_status = 1
AND ip.panel_id = iq.panel_id
AND ip.active_status = 1
AND not exists (select '*' from ies_answers where ies_answers.lookup_id = iq.lookup_id )
AND iq.question_type_id = iqt.question_type_id
AND iq.active_status = 1
AND isdv.survey_cycle_id = iscv.survey_cycle_id
AND isdv.survey_deployment_id = p_deployment_id
AND iscv.survey_id = issv.survey_id);
INSERT INTO ies_svy_list_summary_v
(survey_id,
survey_name,
survey_cycle_id,
survey_cycle_name,
survey_deployment_id,
deployment_name,
target_response_percent,
list_header_id,
list_name,
response_date,
no_sent,
no_responses,
no_errors)
( SELECT
issv.survey_id,
issv.survey_name,
isdv.survey_cycle_id,
iscv.survey_cycle_name,
islev.survey_deployment_id,
isdv.deployment_name,
isdv.min_responses_for_close,
ialh.list_header_id,
ialh.list_name,
null,
count(islev.survey_list_entry_id),
0,
0
FROM
ies_svy_list_entries_v islev,
ies_svy_cycles_v iscv,
ies_svy_deplyments_v isdv,
ies_svy_surveys_v issv,
ams_list_headers_all ialh,
ams_list_entries iale
WHERE isdv.survey_deployment_id = p_deployment_id
AND islev.survey_deployment_id = isdv.survey_deployment_id
AND isdv.survey_cycle_id = iscv.survey_cycle_id
AND iscv.survey_id = issv.survey_id
AND islev.list_entry_id = iale.list_entry_id
AND iale.list_header_id = ialh.list_header_id
GROUP BY
issv.survey_id,
issv.survey_name,
isdv.survey_cycle_id,
iscv.survey_cycle_name,
islev.survey_deployment_id,
isdv.deployment_name,
isdv.min_responses_for_close,
ialh.list_header_id,
ialh.list_name,
null,
0,
0
);
SELECT list_header_id
INTO l_list_header_id
FROM ies_svy_deplyments_all
WHERE SURVEY_DEPLOYMENT_ID = p_deployment_id;
select 1 into l_count
from ies_svy_deplyments_v
where survey_deployment_id = p_deployment_id;