[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select qta.description name, count(*) terr_count
from jtf_terr_denorm_rules_all jtdr
, jtf_qual_types qta
where jtdr.source_id = lc_source_id
and jtdr.terr_id = jtdr.related_terr_id
and jtdr.qual_type_id = qta.qual_type_id
group by qta.description
order by 1;
select qta.description name, count(*) terr_count
from jtf_terr_denorm_rules_all jtdr
, jtf_qual_types_all qta
where jtdr.source_id = lc_source_id
and jtdr.terr_id = jtdr.related_terr_id
and jtdr.qual_type_id = qta.qual_type_id
and jtdr.resource_exists_flag = 'Y'
group by qta.description
order by 1;
select seed.name, count(*) value_count
from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
, jtf_seeded_qual_all_b seed
, jtf_qual_usgs_all jqu
where jtv.terr_qual_id = jtq.terr_qual_id
and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
jtf_terr_usgs_all jtu
where jtu.source_id = lc_source_id
and jtu.terr_id = jt.terr_id
and jt.start_date_active <= SYSDATE
and NVL(jt.end_date_active, SYSDATE) >= SYSDATE )
and jtq.qual_usg_id = jqu.qual_usg_id
and jqu.seeded_qual_id = seed.seeded_qual_id
and jqu.org_id = -3113
and (seed.org_id = -3114 OR seed.org_id is null)
group by seed.name
order by 2 desc;
select seed.name, count(*) value_count
from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
, jtf_seeded_qual_all_b seed
, jtf_qual_usgs_all jqu
where jtv.terr_qual_id = jtq.terr_qual_id
and jtq.qual_usg_id = jqu.qual_usg_id
and jqu.seeded_qual_id = seed.seeded_qual_id
and jqu.org_id = -3113
and (seed.org_id = -3114 OR seed.org_id is null)
group by seed.name
order by 2 desc;
select seed.name, count(*) value_count
from ( SELECT /*+ ORDERED */
r.rowid rule_rowid
, q.rowid qual_rowid
, v.rowid val_rowid
, r.terr_id terr_id
, r.absolute_rank absolute_rank
, r.related_terr_id related_terr_id
, r.top_level_terr_id top_level_terr_id
, r.num_winners num_winners
, r.source_id source_id
, q.terr_qual_id terr_qual_id
, q.qual_usg_id qual_usg_id
, v.terr_value_id terr_value_id
, v.comparison_operator comparison_operator
, v.low_value_char_id low_value_char_id
FROM jtf_terr_denorm_rules_all r
, jtf_terr_qual_all q
, jtf_terr_values_all v
WHERE r.source_id = -1001
AND q.terr_id = r.related_terr_id
AND v.terr_qual_id = q.terr_qual_id
AND q.qual_usg_id <> -1102
AND NOT ( q.qual_usg_id = -1012 AND
( v.comparison_operator = 'LIKE' OR v.comparison_operator = 'BETWEEN' )
)) mv
, jtf_qual_usgs_all jqu
, jtf_seeded_qual_all_b seed
where mv.qual_usg_id = jqu.qual_usg_id
and jqu.seeded_qual_id = seed.seeded_qual_id
and jqu.org_id = -3113
and (seed.org_id = -3114 OR seed.org_id is null)
group by seed.name
order by 2 desc;
select seed.name, jtv.comparison_operator operator, count(*) value_count
from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
, jtf_qual_usgs_all jqu
, jtf_seeded_qual_all_b seed
where jtv.terr_qual_id = jtq.terr_qual_id
and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
jtf_terr_usgs_all jtu
where jtu.source_id = lc_source_id
and jtu.terr_id = jt.terr_id
and jt.start_date_active <= SYSDATE
AND NVL(jt.end_date_active, SYSDATE) >= SYSDATE )
and jtq.qual_usg_id = jqu.qual_usg_id
and jqu.seeded_qual_id = seed.seeded_qual_id
and jqu.org_id = -3113
and (seed.org_id = -3114 OR seed.org_id is null)
group by seed.name, jtv.comparison_operator
order by 3 desc;
select seed.name, jtv.comparison_operator operator, count(*) value_count
from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
, jtf_qual_usgs_all jqu
, jtf_seeded_qual_all_b seed
where jtv.terr_qual_id = jtq.terr_qual_id
and jtq.qual_usg_id = jqu.qual_usg_id
and jqu.seeded_qual_id = seed.seeded_qual_id
and jqu.org_id = -3113
and (seed.org_id = -3114 OR seed.org_id is null)
group by seed.name, jtv.comparison_operator
order by 3 desc;
select jta.name terr_name ,seed.name qual_name, jtv.comparison_operator operator, count(*) value_count
from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
, jtf_terr_all jta
, jtf_qual_usgs_all jqu
, jtf_seeded_qual_all_b seed
where jtv.terr_qual_id = jtq.terr_qual_id
and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
jtf_terr_usgs_all jtu
where jtu.source_id = lc_source_id
and jtu.terr_id = jt.terr_id
and jt.start_date_active <= SYSDATE
AND NVL(jt.end_date_active, SYSDATE) >= SYSDATE )
and jtq.qual_usg_id = jqu.qual_usg_id
and jqu.seeded_qual_id = seed.seeded_qual_id
and jta.terr_id = jtq.terr_id
and jqu.org_id = -3113
and (seed.org_id = -3114 OR seed.org_id is null)
and UPPER(jtv.comparison_operator) NOT IN ('=','LIKE','BETWEEN')
group by jta.name, seed.name, jtv.comparison_operator
order by 3 desc;
select seed.name, mv.comparison_operator operator, count(*) value_count
from ( SELECT /*+ ORDERED */
r.rowid rule_rowid
, q.rowid qual_rowid
, v.rowid val_rowid
, r.terr_id terr_id
, r.absolute_rank absolute_rank
, r.related_terr_id related_terr_id
, r.top_level_terr_id top_level_terr_id
, r.num_winners num_winners
, r.source_id source_id
, q.terr_qual_id terr_qual_id
, q.qual_usg_id qual_usg_id
, v.terr_value_id terr_value_id
, v.comparison_operator comparison_operator
, v.low_value_char_id low_value_char_id
FROM jtf_terr_denorm_rules_all r
, jtf_terr_qual_all q
, jtf_terr_values_all v
WHERE r.source_id = -1001
AND q.terr_id = r.related_terr_id
AND v.terr_qual_id = q.terr_qual_id
AND q.qual_usg_id <> -1102
AND NOT ( q.qual_usg_id = -1012 AND
( v.comparison_operator = 'LIKE' OR v.comparison_operator = 'BETWEEN' )
)) mv
, jtf_qual_usgs_all jqu
, jtf_seeded_qual_all_b seed
where mv.qual_usg_id = jqu.qual_usg_id
and jqu.seeded_qual_id = seed.seeded_qual_id
and jqu.org_id = -3113
and (seed.org_id = -3114 OR seed.org_id is null)
group by seed.name, mv.comparison_operator
order by 3 desc;
select source_id, meaning, lookup_code
from jtf_sources_all
order by 1 desc;
JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
select count(*)
into l_percent_count
from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
, jtf_qual_usgs_all jqu
where jtv.terr_qual_id = jtq.terr_qual_id
and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
jtf_terr_usgs_all jtu
where jtu.source_id = l_source_id
and jtu.terr_id = jt.terr_id
and jt.start_date_active <= SYSDATE
AND NVL(jt.end_date_active, SYSDATE) >= SYSDATE )
and jtq.qual_usg_id = jqu.qual_usg_id
and jqu.org_id = -3113
and SUBSTR(jtv.low_value_char,0,1) = '%'
and jtv.comparison_operator = 'LIKE';
SELECT COUNT(*)
into l_parties
FROM hz_parties;
SELECT COUNT(*)
into l_party_sites
FROM hz_party_sites;
SELECT COUNT(*)
into l_locations
FROM hz_locations;
select count(*)
into l_percent_count
from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
, jtf_qual_usgs_all jqu
where jtv.terr_qual_id = jtq.terr_qual_id
and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
jtf_terr_usgs_all jtu
where jtu.source_id = l_source_id
and jtu.terr_id = jt.terr_id
and jt.start_date_active <= SYSDATE
AND NVL(jt.end_date_active, SYSDATE) >= SYSDATE )
and jtq.qual_usg_id = jqu.qual_usg_id
and jqu.org_id = -3113
and SUBSTR(jtv.low_value_char,0,1) = '%'
and jtv.comparison_operator = 'LIKE';
select count(*)
into l_active_sales_terrs
from jtf_terr_all jta, jtf_terr_usgs_all jtua
where jtua.terr_id = jta.terr_id
and jtua.source_id = l_source_id
and jta.start_date_active <= SYSDATE
AND NVL(jta.end_date_active, SYSDATE) >= SYSDATE;
select count(*)
into l_num_values
from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
where jtv.terr_qual_id = jtq.terr_qual_id
and jtq.terr_id IN ( select jt.terr_id from jtf_terr_all jt,
jtf_terr_usgs_all jtu
where jtu.source_id = l_source_id
and jtu.terr_id = jt.terr_id
and jt.start_date_active <= SYSDATE
AND NVL(jt.end_date_active, SYSDATE + 1) >= SYSDATE );
select count(*)
into l_terr_res_assign
from jtf_terr_rsc_all jtr
where jtr.start_date_active <= SYSDATE
AND NVL(jtr.end_date_active, SYSDATE) >= SYSDATE
AND EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.source_id = l_source_id );
SELECT COUNT (*)
into l_active_reps FROM (
select DISTINCT jtr.resource_id, jtr.resource_type
from jtf_terr_rsc_all jtr
where jtr.start_date_active <= SYSDATE
AND NVL(jtr.end_date_active, SYSDATE) >= SYSDATE
AND EXISTS ( SELECT jtdr.terr_id
FROM jtf_terr_denorm_rules_all jtdr
WHERE jtdr.source_id = l_source_id ) );