Home » SQL & PL/SQL » SQL & PL/SQL » Code Sucks
Code Sucks [message #198] Sun, 27 January 2002 12:56 Go to next message
Rm69
Messages: 39
Registered: January 2002
Member
Need to rewrite procedure. Urgent my code sucks

PROCEDURE Mis_PersalInsertProc
AS
BEGIN

insert into mis_Persal(brokercd,bsp_1,bsp1_count,summary_date)
select bro386,sum(da1386),count(da1386),sysdate from pw38600p
where ah1386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-19','yyyy-mm-dd')
group by bro386;

-- 1 of 10, queries for hybrid consultants summed up at branch level using table pw38600p --
insert into mis_Persal(brokercd,hybrid_1,bsp1_count,summary_date)
select bro386,sum(da1386),count(da1386),sysdate from pw38600p
where bro386 ='hmc' and ah1386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,hybrid_2,bsp2_count,summary_date)
select bro386,sum(da2386),count(da2386),sysdate from pw38600p
where bro386 ='hmc' and ah2386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,hybrid_3,bsp3_count,summary_date)
select bro386,sum(da3386),count(da3386),sysdate from pw38600p
where bro386 ='hmc' and ah3386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,hybrid_4,bsp4_count,summary_date)
select bro386,sum(da4386),count(da4386),sysdate from pw38600p
where bro386 ='hmc' and ah4386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,hybrid_5,bsp5_count,summary_date)
select bro386,sum(da5386),count(da5386),sysdate from pw38600p
where bro386 ='hmc' and ah5386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386,da5386;

insert into mis_Persal(brokercd,hybrid_6,bsp6_count,summary_date)
select bro386,sum(da6386),count(da6386),sysdate from pw38600p
where bro386 ='hmc' and ah6386 not like'%african bank%'
and did386 >= to_date('2001-11-01','yyyy-mm-dd')
and did386 <= to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,hybrid_7,bsp7_count,summary_date)
select bro386,sum(da7386),count(da7386),sysdate from pw38600p
where bro386 ='hmc' and ah7386 not like'%african bank%'
and did386 >= to_date('2001-11-01','yyyy-mm-dd')
and did386 <= to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,hybrid_8,bsp8_count,summary_date)
select bro386,sum(da8386),count(da8386),sysdate from pw38600p
where bro386 ='hmc' and ah8386 not like'%african bank%'
and did386 >= to_date('2001-11-01','yyyy-mm-dd')
and did386 <= to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,hybrid_9,bsp9_count,summary_date)
select bro386,sum(da9386),count(da9386),sysdate from pw38600p
where bro386 ='hmc' and ah9386 not like'%african bank%'
and did386 >= to_date('2001-11-01','yyyy-mm-dd')
and did386 <= to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,hybrid_10,bsp10_count,summary_date)
select bro386,sum(da0386),count(da0386),sysdate from pw38600p
where bro386 ='hmc' and ah0386 not like'%african bank%'
and did386 >= to_date('2001-11-01','yyyy-mm-dd')
and did386 <= to_date('2001-11-20','yyyy-mm-dd')group by bro386;

-- 1 of 10, queries for mobile sales summed by branch--
insert into mis_Persal(brokercd,mob_sales_1,mob_1_count,summary_date)
select bro386,sum(da1386),count(da1386),sysdate from pw38600p
where bro386 ='credit' and ah1386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,mob_sales_2,mob_2_count,summary_date)
select bro386,sum(da2386),count(2386),sysdate from pw38600p
where bro386 ='credit' and ah2386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,mob_sales_3,mob_3_count,summary_date)
select bro386,sum(da3386),count(da3386),sysdate from pw38600p
where bro386 ='credit' and ah3386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,mob_sales_4,mob_4_count,summary_date)
select bro386,sum(da4386),count(da4386),sysdate from pw38600p
where bro386 ='credit' and ah4386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,mob_sales_5,mob_5_count,summary_date)
select bro386,sum(da5386),count(da5386),sysdate from pw38600p
where bro386 ='credit' and ah5386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,mob_sales_6,mob_6_count,summary_date)
select bro386,sum(da6386),count(da6386),sysdate from pw38600p
where bro386 ='credit' and ah6386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,mob_sales_7,mob_7_count,summary_date)
select bro386,sum(da7386),count(da7386),sysdate from pw38600p
where bro386 ='credit' and ah7386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,mob_sales_8,mob_8_count,summary_date)
select bro386,sum(da8386),count(da8386),sysdate from pw38600p
where bro386 ='credit' and ah8386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,mob_sales_9,mob_9_count,summary_date)
select bro386,sum(da9386),count(da9386),sysdate from pw38600p
where bro386 ='credit' and ah9386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,mob_sales_10,mob_10_count,summary_date)
select bro386,sum(da0386),count(da0386),sysdate from pw38600p
where bro386 ='credit' and ah0386 not like'%african bank%'
and did386 >= to_date('2001-11-01','yyyy-mm-dd')
and did386 <= to_date('2001-11-20','yyyy-mm-dd')group by bro386;

-- 1 of 10, queries for esps summed at branch level --
insert into mis_Persal(brokercd,esps_1,esps_1_count,summary_date)
select bro386,sum(da1386),count(da1386),sysdate from pw38600p
where bro386 in ('esp','licens') and ah1386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')and
to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,esps_2,esps_2_count,summary_date)
select bro386,sum(da2386),count(da2386),sysdate from pw38600p
where bro386 in ('esp','licens') and ah2386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,esps_3,esps_3_count,summary_date)
select bro386,sum(da3386),count(da3386),sysdate from pw38600p
where bro386 in ('esp','licens') and ah3386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,esps_4,esps_4_count,summary_date)
select bro386,sum(da4386),count(da4386),sysdate from pw38600p
where bro386 in ('esp','licens') and ah4386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,esps_5,esps_5_count,summary_date)
select bro386,sum(da5386),count(da5386),sysdate from pw38600p where bro386 in ('esp','licens')
and ah5386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,esps_6,esps_6_count,summary_date)
select bro386,sum(da6386),count(da6386),sysdate from pw38600p where bro386 in ('esp','licens')
and ah6386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,esps_7,esps_7_count,summary_date)
select bro386,sum(da7386),count(da7386),sysdate from pw38600p
where bro386 in ('esp','licens') and ah7386 not like'%african bank%'
and did386 >= to_date('2001-11-01','yyyy-mm-dd')
and did386 <= to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,esps_8,esps_8_count,summary_date)
select bro386,sum(da8386),count(da8386),sysdate from pw38600p
where bro386 in ('esp','licens') and ah8386 not like'%african bank%'
and did386 >= to_date('2001-11-01','yyyy-mm-dd')
and did386 <= to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,esps_9,esps_9_count,summary_date)
select bro386,sum(da9386),count(da9386),sysdate from pw38600p
where bro386 in ('esp','licens') and ah9386 not like'%african bank%'
and did386 >= to_date('2001-11-01','yyyy-mm-dd')
and did386 <= to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,esps_10,esps_10_count,summary_date)
select bro386,sum(da0386),count(da0386),sysdate from pw38600p
where bro386 in ('esp','licens') and ah0386 not like'%african bank%'
and did386 >= to_date('2001-11-01','yyyy-mm-dd')
and did386 <= to_date('2001-11-20','yyyy-mm-dd')group by bro386;

-- 1 of 10, queries for psa summed at branch level --
insert into mis_Persal(brokercd,psa_1,psa_1_count,summary_date)
select bro386,sum(da1386),count(da1386),sysdate from pw38600p
where bro386 ='psa' and ah1386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,psa_2,psa_2_count,summary_date)
select bro386,sum(da2386),count(da2386),sysdate from pw38600p
where bro386 ='psa' and ah2386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,psa_3,psa_3_count,summary_date)
select bro386,sum(da3386),count(da3386),sysdate from pw38600p
where bro386 ='psa' and ah3386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,persal_psa_4,psa_4_count,summary_date)
select bro386,sum(da4386),count(da4386),sysdate from pw38600p
where bro386 ='psa' and ah4386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,psa_5,psa_5_count,summary_date)
select bro386,sum(da5386),count(da5386),sysdate from pw38600p
where bro386 ='psa' and ah5386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,psa_6,psa_5_count,summary_date)
select bro386,sum(da6386),count(da6386),sysdate from pw38600p
where bro386 ='psa' and ah6386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,psa_7,psa_7_count,summary_date)
select bro386,sum(da7386),count(da7386),sysdate from pw38600p
where bro386 ='psa' and ah7386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,psa_8,psa_8_count,summary_date)
select bro386,sum(da8386),count(da8386),sysdate from pw38600p
where bro386 ='psa' and ah8386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,psa_9,psa_9_count,summary_date)
select bro386,sum(da9386),count(da9386),sysdate from pw38600p
where bro386 ='psa' and ah9386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

insert into mis_Persal(brokercd,psa_10,psa_10_count,summary_date)
select bro386,sum(da0386),count(da0386),sysdate from pw38600p
where bro386 ='psa' and ah0386 not like'%african bank%'
and did386 between to_date('2001-11-01','yyyy-mm-dd')
and to_date('2001-11-20','yyyy-mm-dd')group by bro386;

end;

Report message to a moderator

Re: Code Sucks [message #255 is a reply to message #198] Fri, 01 February 2002 03:55 Go to previous message
SudaKa
Messages: 1
Registered: February 2002
Junior Member
You want that we re-writen your Procedure? And you will receive the money of it? But, what do you think that is, FAQ or the Red Cross? Work, man, WORK and think !!

Report message to a moderator

Previous Topic: Summary Scenario
Next Topic: passing PL/SQL
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 25 18:28:49 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.