| Subject | Re: [firebird-support] Re: Stored procedure ? |
|---|---|
| Author | Ionut Ichim |
| Post date | 2006-12-04T08:11:24Z |
THANKS TO ALL !!!
----- Original Message ----
From: m24paul <paul.mercea@...>
To: firebird-support@yahoogroups.com
Sent: Monday, December 4, 2006 8:48:44 AM
Subject: [firebird-support] Re: Stored procedure ?
Hi
From yout example look like u don't need REPLICATION, u want
to 'copy' same data in same table for next month.
U can do like this :
CREATE PROCEDURE NEW_PROCEDURE (
i_year integer,
i_month integer)
as
declare variable v_value1 integer;
declare variable v_month integer;
declare variable v_year integer;
declare variable v_id integer;
begin
for select id, "year", "month", value1 from table
where ("year"=i_year) and ("month"=i_month) and value1<>0
into :v_id, v_year, :v_month, v_value1 do
begin
if (i_month=12) then
begin
i_year=iyear+ 1;
i_month=1;
end;
insert into table (id, "year", "month", value1)
values (:v_id, :i_year, :i_month, v_value1);
end;
end^
Regards,
Paul
____________ __
#ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;
}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;
}
#ygrp-vitnav{
padding-top:10px;
font-family:Verdana;
font-size:77%;
margin:0;
}
#ygrp-vitnav a{
padding:0 1px;
}
#ygrp-actbar{
clear:both;
margin:25px 0;
white-space:nowrap;
color:#666;
text-align:right;
}
#ygrp-actbar .left{
float:left;
white-space:nowrap;
}
.bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;
font-size:77%;
padding:15px 0;
}
#ygrp-ft{
font-family:verdana;
font-size:77%;
border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;
}
#ygrp-vital{
background-color:#e0ecee;
margin-bottom:20px;
padding:2px 0 8px 8px;
}
#ygrp-vital #vithd{
font-size:77%;
font-family:Verdana;
font-weight:bold;
color:#333;
text-transform:uppercase;
}
#ygrp-vital ul{
padding:0;
margin:2px 0;
}
#ygrp-vital ul li{
list-style-type:none;
clear:both;
border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;
color:#ff7900;
float:right;
width:2em;
text-align:right;
padding-right:.5em;
}
#ygrp-vital ul li .cat{
font-weight:bold;
}
#ygrp-vital a {
text-decoration:none;
}
#ygrp-vital a:hover{
text-decoration:underline;
}
#ygrp-sponsor #hd{
color:#999;
font-size:77%;
}
#ygrp-sponsor #ov{
padding:6px 13px;
background-color:#e0ecee;
margin-bottom:20px;
}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;
margin:0;
}
#ygrp-sponsor #ov li{
list-style-type:square;
padding:6px 0;
font-size:77%;
}
#ygrp-sponsor #ov li a{
text-decoration:none;
font-size:130%;
}
#ygrp-sponsor #nc {
background-color:#eee;
margin-bottom:20px;
padding:0 8px;
}
#ygrp-sponsor .ad{
padding:8px 0;
}
#ygrp-sponsor .ad #hd1{
font-family:Arial;
font-weight:bold;
color:#628c2a;
font-size:100%;
line-height:122%;
}
#ygrp-sponsor .ad a{
text-decoration:none;
}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;
}
#ygrp-sponsor .ad p{
margin:0;
}
o {font-size:0;}
.MsoNormal {
margin:0 0 0 0;
}
#ygrp-text tt{
font-size:120%;
}
blockquote{margin:0 0 0 4px;}
.replbq {margin:4;}
-->
____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com
[Non-text portions of this message have been removed]
----- Original Message ----
From: m24paul <paul.mercea@...>
To: firebird-support@yahoogroups.com
Sent: Monday, December 4, 2006 8:48:44 AM
Subject: [firebird-support] Re: Stored procedure ?
Hi
From yout example look like u don't need REPLICATION, u want
to 'copy' same data in same table for next month.
U can do like this :
CREATE PROCEDURE NEW_PROCEDURE (
i_year integer,
i_month integer)
as
declare variable v_value1 integer;
declare variable v_month integer;
declare variable v_year integer;
declare variable v_id integer;
begin
for select id, "year", "month", value1 from table
where ("year"=i_year) and ("month"=i_month) and value1<>0
into :v_id, v_year, :v_month, v_value1 do
begin
if (i_month=12) then
begin
i_year=iyear+ 1;
i_month=1;
end;
insert into table (id, "year", "month", value1)
values (:v_id, :i_year, :i_month, v_value1);
end;
end^
Regards,
Paul
>anyway).
> Thanks a lot ...
> Your procedure is ok .
> PK is ID, YEAR, MONTH, VALUE1.
> Sorry for my mistakes...I' m new in Fb.
> Thanks !!!
>
> ----- Original Message ----
> From: Alan McDonald <alan@...>
> To: firebird-support@ yahoogroups. com
> Sent: Sunday, December 3, 2006 1:41:34 PM
> Subject: RE: [firebird-support] Stored procedure ?
>
>
>
>
>
>
>
>
>
>
>
>
>
> >
>
> > Thanks again ....
>
> > example :
>
> > id, year, month,value1
>
> > 01 2006 10 4
>
> > 02 2006 10 3
>
> > 03 2006 10 0
>
> > after the procedure (procedure is run only once)
>
> > id, year, month,value1
>
> >
>
> > 01 2006 10 4
>
> >
>
> > 02 2006 10 3
>
> > 01 2006 11 4
>
> >
>
> > 02 2006 11 3
>
> > I dont't want to use a tool for that,I thought to a stored
>
> > procedure for a speed process,at least I'll do it with may
>
> > application code.
>
> >
>
> > Thanks !
>
> >
>
>
>
> I think you are meaning to use the duplicate (not replicate)?? ?
>
> and you want to duplicate with incremented month value?
>
> Replicate is a completely different thing in DB terminology (here
>INTO :ID, :YEAR, :MONTH,
>
>
> FOR SELECT ID, YEAR, MONTH, VALUE FROM MYTABLE
>(:ID, :YEAR, :MONTH+1,
> :VALUE DO BEGIN
>
> INSER INTO MYTABLE(ID, YEAR, MONTH, VALUE) VALUES
>serif;}
> :VALUE);
>
> END
>
>
>
> I assume you have a surrogate PK which is not ID
>
> Alan
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> <!--
>
> #ygrp-mlmsg {font-size:13px; font-family: arial,helvetica, clean,sans-
> #ygrp-mlmsg table {font-size:inherit; font:100% ;}arial,helvetica, clean,sans- serif;}
> #ygrp-mlmsg select, input, textarea {font:99%
> #ygrp-mlmsg pre, code {font:115% monospace;}____________ _________ _________ _________ _________ _________ _
> #ygrp-mlmsg * {line-height: 1.22em;}
> #ygrp-text{
> font-family: Georgia;
> }
> #ygrp-text p{
> margin:0 0 1em 0;
> }
> #ygrp-tpmsgs{
> font-family: Arial;
> clear:both;
> }
> #ygrp-vitnav{
> padding-top: 10px;
> font-family: Verdana;
> font-size:77% ;
> margin:0;
> }
> #ygrp-vitnav a{
> padding:0 1px;
> }
> #ygrp-actbar{
> clear:both;
> margin:25px 0;
> white-space: nowrap;
> color:#666;
> text-align:right;
> }
> #ygrp-actbar .left{
> float:left;
> white-space: nowrap;
> }
> .bld{font-weight: bold;}
> #ygrp-grft{
> font-family: Verdana;
> font-size:77% ;
> padding:15px 0;
> }
> #ygrp-ft{
> font-family: verdana;
> font-size:77% ;
> border-top:1px solid #666;
> padding:5px 0;
> }
> #ygrp-mlmsg #logo{
> padding-bottom: 10px;
> }
>
> #ygrp-vital{
> background-color: #e0ecee;
> margin-bottom: 20px;
> padding:2px 0 8px 8px;
> }
> #ygrp-vital #vithd{
> font-size:77% ;
> font-family: Verdana;
> font-weight: bold;
> color:#333;
> text-transform: uppercase;
> }
> #ygrp-vital ul{
> padding:0;
> margin:2px 0;
> }
> #ygrp-vital ul li{
> list-style-type: none;
> clear:both;
> border:1px solid #e0ecee;
> }
> #ygrp-vital ul li .ct{
> font-weight: bold;
> color:#ff7900;
> float:right;
> width:2em;
> text-align:right;
> padding-right: .5em;
> }
> #ygrp-vital ul li .cat{
> font-weight: bold;
> }
> #ygrp-vital a {
> text-decoration: none;
> }
>
> #ygrp-vital a:hover{
> text-decoration: underline;
> }
>
> #ygrp-sponsor #hd{
> color:#999;
> font-size:77% ;
> }
> #ygrp-sponsor #ov{
> padding:6px 13px;
> background-color: #e0ecee;
> margin-bottom: 20px;
> }
> #ygrp-sponsor #ov ul{
> padding:0 0 0 8px;
> margin:0;
> }
> #ygrp-sponsor #ov li{
> list-style-type: square;
> padding:6px 0;
> font-size:77% ;
> }
> #ygrp-sponsor #ov li a{
> text-decoration: none;
> font-size:130% ;
> }
> #ygrp-sponsor #nc {
> background-color: #eee;
> margin-bottom: 20px;
> padding:0 8px;
> }
> #ygrp-sponsor .ad{
> padding:8px 0;
> }
> #ygrp-sponsor .ad #hd1{
> font-family: Arial;
> font-weight: bold;
> color:#628c2a;
> font-size:100% ;
> line-height: 122%;
> }
> #ygrp-sponsor .ad a{
> text-decoration: none;
> }
> #ygrp-sponsor .ad a:hover{
> text-decoration: underline;
> }
> #ygrp-sponsor .ad p{
> margin:0;
> }
> o {font-size:0; }
> .MsoNormal {
> margin:0 0 0 0;
> }
> #ygrp-text tt{
> font-size:120% ;
> }
> blockquote{margin: 0 0 0 4px;}
> .replbq {margin:4;}
> -->
>
>
>
>
>
>
>
>
>
>
____________ __
> Any questions? Get answers on any topic at www.Answers. yahoo.com.Try it now.
><!--
> [Non-text portions of this message have been removed]
>
#ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;
}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;
}
#ygrp-vitnav{
padding-top:10px;
font-family:Verdana;
font-size:77%;
margin:0;
}
#ygrp-vitnav a{
padding:0 1px;
}
#ygrp-actbar{
clear:both;
margin:25px 0;
white-space:nowrap;
color:#666;
text-align:right;
}
#ygrp-actbar .left{
float:left;
white-space:nowrap;
}
.bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;
font-size:77%;
padding:15px 0;
}
#ygrp-ft{
font-family:verdana;
font-size:77%;
border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;
}
#ygrp-vital{
background-color:#e0ecee;
margin-bottom:20px;
padding:2px 0 8px 8px;
}
#ygrp-vital #vithd{
font-size:77%;
font-family:Verdana;
font-weight:bold;
color:#333;
text-transform:uppercase;
}
#ygrp-vital ul{
padding:0;
margin:2px 0;
}
#ygrp-vital ul li{
list-style-type:none;
clear:both;
border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;
color:#ff7900;
float:right;
width:2em;
text-align:right;
padding-right:.5em;
}
#ygrp-vital ul li .cat{
font-weight:bold;
}
#ygrp-vital a {
text-decoration:none;
}
#ygrp-vital a:hover{
text-decoration:underline;
}
#ygrp-sponsor #hd{
color:#999;
font-size:77%;
}
#ygrp-sponsor #ov{
padding:6px 13px;
background-color:#e0ecee;
margin-bottom:20px;
}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;
margin:0;
}
#ygrp-sponsor #ov li{
list-style-type:square;
padding:6px 0;
font-size:77%;
}
#ygrp-sponsor #ov li a{
text-decoration:none;
font-size:130%;
}
#ygrp-sponsor #nc {
background-color:#eee;
margin-bottom:20px;
padding:0 8px;
}
#ygrp-sponsor .ad{
padding:8px 0;
}
#ygrp-sponsor .ad #hd1{
font-family:Arial;
font-weight:bold;
color:#628c2a;
font-size:100%;
line-height:122%;
}
#ygrp-sponsor .ad a{
text-decoration:none;
}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;
}
#ygrp-sponsor .ad p{
margin:0;
}
o {font-size:0;}
.MsoNormal {
margin:0 0 0 0;
}
#ygrp-text tt{
font-size:120%;
}
blockquote{margin:0 0 0 4px;}
.replbq {margin:4;}
-->
____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com
[Non-text portions of this message have been removed]