
----------------------------------
-- Session: ESPOTNITZ@EDWPROD
-- Timestamp: 06:13:36.606
declare
  v_ignore raw(100);
  v_oldhash number;
  v_hash number;
begin
  v_hash := dbms_utility.get_sql_hash(:SQLText || chr(0), v_ignore, v_oldhash);
  :outHash := v_hash;
end;

:SQLText(VARCHAR[12932],IN)='SELECT /*+ first_rows */ DISTINCT
       d.donor_number                              donor_number,     -- 166642
       ftq.don_date_key                            don_date_key,
       ftq.tq_date_key                             tq_date_key,
       d.donor_last_name                           donor_last_name,
       d.donor_first_name                          donor_first_name,
       d.donor_middle_name                         donor_middle_name,
       d.donor_suffix                              donor_suffix,
       d.donor_gender                              donor_sex,
       d.donor_date_of_birth                       donor_date_of_birth,
       d.donor_city                                donor_city,
       d.donor_state_abbrv                         donor_state_cd,
       d.donor_zip                                 donor_zip,
       d.donor_address_line_1                      donor_address_line_1,
       d.donor_address_line_2                      donor_address_line_2,
       d.donor_phone_number                        donor_phone_number,
       d.donor_alt_phone_number                    donor_alt_phone_number,
       d.donor_email                               donor_email,
       d.donor_cell_phone                          donor_cell_phone,
       d.race_description                          race_description,
       dd.age_group                                age_group,
       dd.education                                education,
       dd.ethnicity_code                           ethnicity_code,
       dd.ethnicity_description                    ethnicity_description,
       dd.sex                                      sex,
       q.question_code                             question_code,
       q.questionnaire_type_description            questionnaire_type_description,
       q.question_text                             question_text,
       ftq.donor_key                               donor_key,
       ftq.donation_number                         donation_number,
       ftq.question_key                            questions_key,
       ftq.tq_answer                               tq_answer,
       ddon.device                                 device,
       ddon.phlebotomy_group                       phlebotomy_group,
       ddon.phlebotomy_status                      phlebotomy_status,
       ddon.donation_status                        donation_status,
       ddon.phlebotomy_code                        phlebotomy_code,
       fd.phleb_start_date_key                     phleb_start_date_key,
       t.military_time                             AS phelb_start_time,
       fd.phleb_stop_date_key                      phleb_stop_date_key,
       t2.military_time                            AS phelb_stop_time,
       op.operator_badge_code,
       dop.operator_first_name,
       dop.operator_last_name,
       ds.create_date                              create_date,
       ds.supplier_address_line_1                  supplier_address_line_1,
       ds.supplier_address_line_2                  supplier_address_line_2,
       ds.supplier_city                            supplier_city,
       ds.supplier_code                            supplier_code,
       ds.supplier_facility_id                     supplier_isbt,
       ds.supplier_name                            supplier_name,
       ds.supplier_state_abbrv                     supplier_state_code,
       SUBSTR (ds.supplier_zip, 1, 5)              supplier_zip,
       cs.collection_site_code                     collection_site_code,
       CASE
           WHEN SUBSTR (collection_description_orig,
                        1,
                        INSTR (collection_description_orig, '-'))
                    IS NULL
           THEN
               collection_description_orig
           ELSE
               SUBSTR (collection_description_orig,
                       1,
                       INSTR (collection_description_orig, '-') - 1)
       END                                         AS collection_description,
       cs.collection_description_orig              collection_site_description,
       cs.collection_site                          collection_site,
       z.w1_qkey                                   w1_qkey,
       z.w1_tqanswer                               w1_tqanswer,
       z.mr_qkey                                   mr_qkey,
       z.mr_tqanswer                               mr_tqanswer,
       z.mq_qkey                                   mq_qkey,
       z.mq_tqanswer                               mq_tqanswer,
       t2.question_key                             c9_qkey,
       t2.tq_answer                                c9_tqanswer,
       t3.question_key                             tq13_qkey,
       t3.tq_answer                                tq13_tqanswer,
       NVL (dnrdntcnt12.number_of_donations, 0)    AS number_of_donations,
       NVL (dnrdntcnt.donor_donation_count, 0)     AS donor_donation_count,
       NVL (prdcnt.product_count, 0)               AS components_produced -- product_count
  FROM edwnew.fact_donation_tq  ftq
       INNER JOIN edwnew.dim_question q ON ftq.question_key = q.question_key
       INNER JOIN edwnew.dim_donor d ON d.donor_key = ftq.donor_key
       INNER JOIN edwnew.fact_donation fd
           ON (    fd.donor_key = ftq.donor_key
               AND fd.donation_key = ftq.donation_key
               AND fd.don_date_key = ftq.don_date_key)
       INNER JOIN edwnew.dim_demographics dd
           ON dd.demographics_key = fd.demographics_key
       INNER JOIN edwnew.dim_donation ddon
           ON ddon.donation_key = ftq.donation_key
       INNER JOIN edwnew.dim_supplier ds
           ON ds.supplier_key = ftq.supplier_key
       INNER JOIN edwnew.dim_time t ON t.time_key = fd.phleb_start_time_key
       INNER JOIN edwnew.dim_time t2 ON t2.time_key = fd.phleb_stop_time_key
       INNER JOIN edwnew.dim_collection_site cs
           ON fd.collection_site_key = cs.collection_site_key
       LEFT JOIN
       (SELECT ftq.donation_number,
               CASE
                   WHEN op1.tq_answer IS NULL THEN op2.tq_answer
                   ELSE op1.tq_answer
               END    AS operator_badge_code
          FROM edwnew.fact_donation_tq  ftq
               LEFT JOIN
               (SELECT ftq2.donation_number, ftq2.tq_answer
                  FROM edwnew.fact_donation_tq ftq2 --  ,EDWNEW.dim_operator do2
                 WHERE ftq2.question_key =
                       (SELECT question_key
                          FROM edwnew.dim_question
                         WHERE     question_code = 'V6'
                               AND questionnaire_class = 'DONATION'
                               AND questionnaire_type_description =
                                   'Phlebotomy')) op1
                   ON op1.donation_number = ftq.donation_number
               LEFT JOIN
               (SELECT ftq3.donation_number, ftq3.tq_answer
                  FROM edwnew.fact_donation_tq ftq3 -- ,EDWNEW.dim_operator do3
                 WHERE ftq3.question_key =
                       (SELECT question_key
                          FROM edwnew.dim_question
                         WHERE     question_code = 'V8'
                               AND questionnaire_class = 'DONATION'
                               AND questionnaire_type_description =
                                   'Phlebotomy')) op2
                   ON op2.donation_number = ftq.donation_number) op
           ON op.donation_number = ftq.donation_number
       LEFT JOIN edwnew.dim_operator dop
           ON dop.operator_badge_code = op.operator_badge_code
       LEFT JOIN
       (  SELECT d.donor_number, COUNT (f.donor_key) AS number_of_donations
            FROM edwnew.dim_donor d
                 INNER JOIN edwnew.fact_donation f ON f.donor_key = d.donor_key
           WHERE f.don_date_key >=
                 TO_CHAR (ADD_MONTHS (SYSDATE, -12), 'yyyymmdd')
        GROUP BY d.donor_number) dnrdntcnt12
           ON dnrdntcnt12.donor_number = d.donor_number
       LEFT JOIN
       (  SELECT d.donor_number, COUNT (f.donor_key) AS donor_donation_count
            FROM edwnew.dim_donor d
                 INNER JOIN edwnew.fact_donation f ON f.donor_key = d.donor_key
           WHERE f.don_date_key >= '20070101'
        GROUP BY d.donor_number) dnrdntcnt
           ON dnrdntcnt.donor_number = d.donor_number
       LEFT JOIN
       (  SELECT donation_number, COUNT (donation_number) AS product_count
            FROM (  SELECT fp.product_number                     donation_number,
                           pc.product_code,
                           COUNT (DISTINCT fp.product_number)    AS product_count
                      FROM edwnew.fact_product fp
                           JOIN edwnew.dim_date dte
                               ON fp.don_date_key = dte.date_key
                           JOIN edwnew.dim_product_code pc
                               ON fp.product_code_key = pc.product_code_key
                           JOIN edwnew.dim_site s ON fp.site_key = s.site_key
                           JOIN edwnew.dim_donation dd
                               ON fp.donation_key = dd.donation_key
                     WHERE     dte.full_date >=
                               TO_DATE ('01-01-2007', 'dd-mm-yyyy')
                           AND dd.phlebotomy_status = 'Successful Phlebotomy'
                  GROUP BY fp.product_number, pc.product_code) y
        GROUP BY donation_number) prdcnt
           ON ftq.donation_number = prdcnt.donation_number
       LEFT JOIN
       (SELECT d.donation_number,
               d.question_key,
               d.tq_answer,
               d.tq_date_key,
               f1.question_key        w1_qkey,
               f1.donation_number     w1_dn,
               f1.tq_answer           w1_tqanswer,
               f2.question_key        mr_qkey,
               f2.donation_number     mr_dn,
               f2.tq_answer           mr_tqanswer,
               f3.question_key        mq_qkey,
               f3.donation_number     mq_dn,
               f3.tq_answer           mq_tqanswer
          FROM edwnew.fact_donation_tq  d
               LEFT OUTER JOIN
               (SELECT a1.donation_number,
                       dq1.question_key,
                       a1.tq_answer
                  FROM edwnew.fact_donation_tq  a1,
                       edwnew.dim_question      dq1
                 WHERE     a1.question_key = dq1.question_key
                       AND dq1.question_code = 'MR'
                       AND dq1.questionnaire_class = 'DONATION') f2
                   ON (f2.donation_number = d.donation_number)
               LEFT OUTER JOIN
               (SELECT a2.donation_number,
                       dq2.question_key,
                       a2.tq_answer
                  FROM edwnew.fact_donation_tq  a2,
                       edwnew.dim_question      dq2
                 WHERE     a2.question_key = dq2.question_key
                       AND dq2.question_code = 'MQ'
                       AND dq2.questionnaire_class = 'DONATION') f3
                   ON (f3.donation_number = d.donation_number)
               LEFT OUTER JOIN
               (SELECT a3.donation_number,
                       dq3.question_key,
                       a3.tq_answer
                  FROM edwnew.fact_donation_tq  a3,
                       edwnew.dim_question      dq3
                 WHERE     a3.question_key = dq3.question_key
                       AND dq3.question_code = '1W'
                       AND dq3.questionnaire_class = 'DONATION') f1
                   ON (f1.donation_number = d.donation_number)
         WHERE     d.question_key IN
                       (SELECT question_key
                          FROM edwnew.dim_question
                         WHERE     question_code = '19'
                               AND questionnaire_class = 'DONATION')
               AND d.tq_answer = 'MILD'
               AND d.tq_date_key > '20100117') z
           ON ftq.donation_number = z.donation_number
       LEFT OUTER JOIN
       (SELECT a4.donation_number, dq4.question_key, a4.tq_answer
          FROM edwnew.fact_donation_tq a4, edwnew.dim_question dq4
         WHERE     a4.question_key = dq4.question_key
               AND dq4.question_code = 'C9'
               AND dq4.questionnaire_class = 'DONATION') t2
           ON (t2.donation_number = ftq.donation_number)
       LEFT OUTER JOIN
       (SELECT a5.donation_number, dq5.question_key, a5.tq_answer
          FROM edwnew.fact_donation_tq a5, edwnew.dim_question dq5
         WHERE     a5.question_key = dq5.question_key
               AND dq5.question_code = '13'
               AND dq5.questionnaire_class = 'DONATION') t3
           ON (t3.donation_number = ftq.donation_number)
 WHERE     (q.question_code = '19' AND q.questionnaire_class = 'DONATION')
       AND (ftq.tq_answer LIKE 'BS%' OR ftq.tq_answer = 'MILD')' 
:outHash(FLOAT,OUT)=3457985957


----------------------------------
-- Session: ESPOTNITZ@EDWPROD
-- Timestamp: 06:13:36.638
select min(child_number) cn
from v$sql_plan
Where hash_value = :sqlhv;

:sqlhv(VARCHAR[10],IN)='3457985957'


----------------------------------
-- Session: ESPOTNITZ@EDWPROD
-- Timestamp: 06:13:36.663
Select *
from v$sql_plan
Where hash_value = :sqlhv
order by id;

:sqlhv(VARCHAR[10],IN)='3457985957'


----------------------------------
-- Session: ESPOTNITZ@EDWPROD
-- Timestamp: 06:13:36.720
explain plan set statement_id='308881:091921061336' into PUBLIC.PLAN_TABLE For
SELECT /*+ first_rows */ DISTINCT
       d.donor_number                              donor_number,     -- 166642
       ftq.don_date_key                            don_date_key,
       ftq.tq_date_key                             tq_date_key,
       d.donor_last_name                           donor_last_name,
       d.donor_first_name                          donor_first_name,
       d.donor_middle_name                         donor_middle_name,
       d.donor_suffix                              donor_suffix,
       d.donor_gender                              donor_sex,
       d.donor_date_of_birth                       donor_date_of_birth,
       d.donor_city                                donor_city,
       d.donor_state_abbrv                         donor_state_cd,
       d.donor_zip                                 donor_zip,
       d.donor_address_line_1                      donor_address_line_1,
       d.donor_address_line_2                      donor_address_line_2,
       d.donor_phone_number                        donor_phone_number,
       d.donor_alt_phone_number                    donor_alt_phone_number,
       d.donor_email                               donor_email,
       d.donor_cell_phone                          donor_cell_phone,
       d.race_description                          race_description,
       dd.age_group                                age_group,
       dd.education                                education,
       dd.ethnicity_code                           ethnicity_code,
       dd.ethnicity_description                    ethnicity_description,
       dd.sex                                      sex,
       q.question_code                             question_code,
       q.questionnaire_type_description            questionnaire_type_description,
       q.question_text                             question_text,
       ftq.donor_key                               donor_key,
       ftq.donation_number                         donation_number,
       ftq.question_key                            questions_key,
       ftq.tq_answer                               tq_answer,
       ddon.device                                 device,
       ddon.phlebotomy_group                       phlebotomy_group,
       ddon.phlebotomy_status                      phlebotomy_status,
       ddon.donation_status                        donation_status,
       ddon.phlebotomy_code                        phlebotomy_code,
       fd.phleb_start_date_key                     phleb_start_date_key,
       t.military_time                             AS phelb_start_time,
       fd.phleb_stop_date_key                      phleb_stop_date_key,
       t2.military_time                            AS phelb_stop_time,
       op.operator_badge_code,
       dop.operator_first_name,
       dop.operator_last_name,
       ds.create_date                              create_date,
       ds.supplier_address_line_1                  supplier_address_line_1,
       ds.supplier_address_line_2                  supplier_address_line_2,
       ds.supplier_city                            supplier_city,
       ds.supplier_code                            supplier_code,
       ds.supplier_facility_id                     supplier_isbt,
       ds.supplier_name                            supplier_name,
       ds.supplier_state_abbrv                     supplier_state_code,
       SUBSTR (ds.supplier_zip, 1, 5)              supplier_zip,
       cs.collection_site_code                     collection_site_code,
       CASE
           WHEN SUBSTR (collection_description_orig,
                        1,
                        INSTR (collection_description_orig, '-'))
                    IS NULL
           THEN
               collection_description_orig
           ELSE
               SUBSTR (collection_description_orig,
                       1,
                       INSTR (collection_description_orig, '-') - 1)
       END                                         AS collection_description,
       cs.collection_description_orig              collection_site_description,
       cs.collection_site                          collection_site,
       z.w1_qkey                                   w1_qkey,
       z.w1_tqanswer                               w1_tqanswer,
       z.mr_qkey                                   mr_qkey,
       z.mr_tqanswer                               mr_tqanswer,
       z.mq_qkey                                   mq_qkey,
       z.mq_tqanswer                               mq_tqanswer,
       t2.question_key                             c9_qkey,
       t2.tq_answer                                c9_tqanswer,
       t3.question_key                             tq13_qkey,
       t3.tq_answer                                tq13_tqanswer,
       NVL (dnrdntcnt12.number_of_donations, 0)    AS number_of_donations,
       NVL (dnrdntcnt.donor_donation_count, 0)     AS donor_donation_count,
       NVL (prdcnt.product_count, 0)               AS components_produced -- product_count
  FROM edwnew.fact_donation_tq  ftq
       INNER JOIN edwnew.dim_question q ON ftq.question_key = q.question_key
       INNER JOIN edwnew.dim_donor d ON d.donor_key = ftq.donor_key
       INNER JOIN edwnew.fact_donation fd
           ON (    fd.donor_key = ftq.donor_key
               AND fd.donation_key = ftq.donation_key
               AND fd.don_date_key = ftq.don_date_key)
       INNER JOIN edwnew.dim_demographics dd
           ON dd.demographics_key = fd.demographics_key
       INNER JOIN edwnew.dim_donation ddon
           ON ddon.donation_key = ftq.donation_key
       INNER JOIN edwnew.dim_supplier ds
           ON ds.supplier_key = ftq.supplier_key
       INNER JOIN edwnew.dim_time t ON t.time_key = fd.phleb_start_time_key
       INNER JOIN edwnew.dim_time t2 ON t2.time_key = fd.phleb_stop_time_key
       INNER JOIN edwnew.dim_collection_site cs
           ON fd.collection_site_key = cs.collection_site_key
       LEFT JOIN
       (SELECT ftq.donation_number,
               CASE
                   WHEN op1.tq_answer IS NULL THEN op2.tq_answer
                   ELSE op1.tq_answer
               END    AS operator_badge_code
          FROM edwnew.fact_donation_tq  ftq
               LEFT JOIN
               (SELECT ftq2.donation_number, ftq2.tq_answer
                  FROM edwnew.fact_donation_tq ftq2 --  ,EDWNEW.dim_operator do2
                 WHERE ftq2.question_key =
                       (SELECT question_key
                          FROM edwnew.dim_question
                         WHERE     question_code = 'V6'
                               AND questionnaire_class = 'DONATION'
                               AND questionnaire_type_description =
                                   'Phlebotomy')) op1
                   ON op1.donation_number = ftq.donation_number
               LEFT JOIN
               (SELECT ftq3.donation_number, ftq3.tq_answer
                  FROM edwnew.fact_donation_tq ftq3 -- ,EDWNEW.dim_operator do3
                 WHERE ftq3.question_key =
                       (SELECT question_key
                          FROM edwnew.dim_question
                         WHERE     question_code = 'V8'
                               AND questionnaire_class = 'DONATION'
                               AND questionnaire_type_description =
                                   'Phlebotomy')) op2
                   ON op2.donation_number = ftq.donation_number) op
           ON op.donation_number = ftq.donation_number
       LEFT JOIN edwnew.dim_operator dop
           ON dop.operator_badge_code = op.operator_badge_code
       LEFT JOIN
       (  SELECT d.donor_number, COUNT (f.donor_key) AS number_of_donations
            FROM edwnew.dim_donor d
                 INNER JOIN edwnew.fact_donation f ON f.donor_key = d.donor_key
           WHERE f.don_date_key >=
                 TO_CHAR (ADD_MONTHS (SYSDATE, -12), 'yyyymmdd')
        GROUP BY d.donor_number) dnrdntcnt12
           ON dnrdntcnt12.donor_number = d.donor_number
       LEFT JOIN
       (  SELECT d.donor_number, COUNT (f.donor_key) AS donor_donation_count
            FROM edwnew.dim_donor d
                 INNER JOIN edwnew.fact_donation f ON f.donor_key = d.donor_key
           WHERE f.don_date_key >= '20070101'
        GROUP BY d.donor_number) dnrdntcnt
           ON dnrdntcnt.donor_number = d.donor_number
       LEFT JOIN
       (  SELECT donation_number, COUNT (donation_number) AS product_count
            FROM (  SELECT fp.product_number                     donation_number,
                           pc.product_code,
                           COUNT (DISTINCT fp.product_number)    AS product_count
                      FROM edwnew.fact_product fp
                           JOIN edwnew.dim_date dte
                               ON fp.don_date_key = dte.date_key
                           JOIN edwnew.dim_product_code pc
                               ON fp.product_code_key = pc.product_code_key
                           JOIN edwnew.dim_site s ON fp.site_key = s.site_key
                           JOIN edwnew.dim_donation dd
                               ON fp.donation_key = dd.donation_key
                     WHERE     dte.full_date >=
                               TO_DATE ('01-01-2007', 'dd-mm-yyyy')
                           AND dd.phlebotomy_status = 'Successful Phlebotomy'
                  GROUP BY fp.product_number, pc.product_code) y
        GROUP BY donation_number) prdcnt
           ON ftq.donation_number = prdcnt.donation_number
       LEFT JOIN
       (SELECT d.donation_number,
               d.question_key,
               d.tq_answer,
               d.tq_date_key,
               f1.question_key        w1_qkey,
               f1.donation_number     w1_dn,
               f1.tq_answer           w1_tqanswer,
               f2.question_key        mr_qkey,
               f2.donation_number     mr_dn,
               f2.tq_answer           mr_tqanswer,
               f3.question_key        mq_qkey,
               f3.donation_number     mq_dn,
               f3.tq_answer           mq_tqanswer
          FROM edwnew.fact_donation_tq  d
               LEFT OUTER JOIN
               (SELECT a1.donation_number,
                       dq1.question_key,
                       a1.tq_answer
                  FROM edwnew.fact_donation_tq  a1,
                       edwnew.dim_question      dq1
                 WHERE     a1.question_key = dq1.question_key
                       AND dq1.question_code = 'MR'
                       AND dq1.questionnaire_class = 'DONATION') f2
                   ON (f2.donation_number = d.donation_number)
               LEFT OUTER JOIN
               (SELECT a2.donation_number,
                       dq2.question_key,
                       a2.tq_answer
                  FROM edwnew.fact_donation_tq  a2,
                       edwnew.dim_question      dq2
                 WHERE     a2.question_key = dq2.question_key
                       AND dq2.question_code = 'MQ'
                       AND dq2.questionnaire_class = 'DONATION') f3
                   ON (f3.donation_number = d.donation_number)
               LEFT OUTER JOIN
               (SELECT a3.donation_number,
                       dq3.question_key,
                       a3.tq_answer
                  FROM edwnew.fact_donation_tq  a3,
                       edwnew.dim_question      dq3
                 WHERE     a3.question_key = dq3.question_key
                       AND dq3.question_code = '1W'
                       AND dq3.questionnaire_class = 'DONATION') f1
                   ON (f1.donation_number = d.donation_number)
         WHERE     d.question_key IN
                       (SELECT question_key
                          FROM edwnew.dim_question
                         WHERE     question_code = '19'
                               AND questionnaire_class = 'DONATION')
               AND d.tq_answer = 'MILD'
               AND d.tq_date_key > '20100117') z
           ON ftq.donation_number = z.donation_number
       LEFT OUTER JOIN
       (SELECT a4.donation_number, dq4.question_key, a4.tq_answer
          FROM edwnew.fact_donation_tq a4, edwnew.dim_question dq4
         WHERE     a4.question_key = dq4.question_key
               AND dq4.question_code = 'C9'
               AND dq4.questionnaire_class = 'DONATION') t2
           ON (t2.donation_number = ftq.donation_number)
       LEFT OUTER JOIN
       (SELECT a5.donation_number, dq5.question_key, a5.tq_answer
          FROM edwnew.fact_donation_tq a5, edwnew.dim_question dq5
         WHERE     a5.question_key = dq5.question_key
               AND dq5.question_code = '13'
               AND dq5.questionnaire_class = 'DONATION') t3
           ON (t3.donation_number = ftq.donation_number)
 WHERE     (q.question_code = '19' AND q.questionnaire_class = 'DONATION')
       AND (ftq.tq_answer LIKE 'BS%' OR ftq.tq_answer = 'MILD');

-- ORA-00905: missing keyword

