List:General Discussion« Previous MessageNext Message »
From:Joe Byers Date:August 25 2004 3:22pm
Subject:Create procedure problem
View as plain text  
I want to create a stored procedure that I pass a date variable and two 
database names.  I can not seem to figure the correct syntax to utilize 
these variables in the procedure.  I think I might need to build a 
sqlstring and use an exec command to execute the string.  Here is my 
procedure code:

# requires a table of paths called paths;

create procedure calclmp(in startdate date, in db1 varchar(32), in db2 
varchar(32) );
begin
drop table if exists DB1.lmp_daily, DB1.t6, DB1.tmp_month_hr;
create table DB1.lmp_daily like DB2.pjm_lmp_daily;

/*create table DB1.lmp_daily (_FREQ_ int, lmp_mean real,lmp_std 
real,class_type varchar(7))*/
insert into DB1.lmp_daily
    select if(si.hourtype=1,"OnPeak","OffPeak") as class_type,si.date, 
p.source,p.sink,
        count(si.price-so.price) as _FREQ_, sum(si.price-so.price) as 
lmp_sum,
        avg(si.price-so.price) as lmp_mean,
           
sqrt(variance(si.price-so.price)*count(si.price-so.price)/(count(si.price-so.price)-1)) 
as lmp_std
       from DB1.paths as p,DB2.lmprices_t as si, DB2.lmprices_t as so
       where p.source=so.pnodeid and p.sink=si.pnodeid
          and si.date>= STARTDATE and so.date>= STARTDATE
        and si.date=so.date and si.hour=so.hour and si.hourtype =so.hourtype
       group by si.date, p.source, p.sink, class_type;
insert into DB1.lmp_daily
    select "24H" as class_type, si.date, p.source,p.sink, 
count(si.price-so.price) as _FREQ_,
           sum(si.price-so.price) as lmp_sum,
        avg(si.price-so.price) as lmp_mean,
           
sqrt(variance(si.price-so.price)*count(si.price-so.price)/(count(si.price-so.price)-1)) 
as lmp_std
       from DB1.paths as p,DB2.lmprices_t as si, DB2.lmprices_t as so
       where p.source=so.pnodeid and p.sink=si.pnodeid
          and si.date>= STARTDATE and so.date>= STARTDATE
        and si.date=so.date and si.hour=so.hour /*and si.hourtype 
=so.hourtype */
       group by si.date, p.source, p.sink;

create table DB1.t6 (lmp real, lmp_option_long real, lmp_option_short real)
    select date, source, sink, class_type,lmp_sum as lmp,
        /*max*/if(lmp_sum>0,lmp_sum,0) as lmp_option_long,
        /*min*/if(lmp_sum<0,lmp_sum,0) as lmp_option_short
    from DB1.lmp_daily;
create table DB1.tmp_month_hr (year int, month int, _FREQ_ int, lmp_sum 
real, lmp_mean real, lmp_std real,
        Freq_minus int, freq_plus int,
        lmp_option_long_mean real, lmp_option_long_sum real, 
lmp_option_long_std real,
        lmp_option_short_mean real, lmp_option_short_sum real, 
lmp_option_short_std real,
        lmp_min real, lmp_max real, lmp_option_long_min real, 
lmp_option_long_max real,
        lmp_option_short_min real, lmp_option_short_max real)
    select year(date) as year, month(date) as month, source, sink, 
class_type, count(lmp) as _FREQ_,
        avg(lmp) as lmp_mean, avg(lmp_option_long) as 
lmp_option_long_mean, avg(lmp_option_short) as lmp_option_short_mean,
         sum(lmp) as lmp_sum, sum(lmp_option_long) as 
lmp_option_long_sum, sum(lmp_option_short) as lmp_option_short_sum,
        sqrt(variance(lmp)*count(lmp)/(count(lmp)-1)) as lmp_std,
        
sqrt(variance(lmp_option_long)*count(lmp_option_long)/(count(lmp_option_long)-1)) 
as lmp_option_long_std,
        
sqrt(variance(lmp_option_short)*count(lmp_option_short)/(count(lmp_option_short)-1)) 
as lmp_option_short_std,
        min(lmp) as lmp_min,max(lmp) as lmp_max, min(lmp_option_long) as 
lmp_Option_long_min,
        max(lmp_option_long) as lmp_option_long_max, 
min(lmp_option_short) as lmp_Option_short_min,
        max(lmp_option_short) as 
lmp_option_short_max,count(if(lmp>0,lmp,null)) as Freq_plus,
        count(if(lmp<0,lmp,null)) as minus
    from DB1.t6
    group by year, month, source, sink, class_type;
drop table if exists DB1.t6;
end;

I appreciate any suggestions.

Thank you
Joe


Thread
Create procedure problemJoe Byers25 Aug
  • Re: Create procedure problemMartijn Tonies25 Aug