- Архив списков рассылки pgsql-sql
От | Ehmann, Mary |
---|---|
Тема | |
Дата | |
Msg-id | 328D53D0EEF7D411822900508B6FDDE607F9B388@1upmc-msx2.isdip.upmc.edu обсуждение исходный текст |
Список | pgsql-sql |
<p><font face="Arial Narrow">Could someone please tell me why this is scoring all BUT my first row of data?</font><br /><fontface="Arial Narrow">Thank you.</font><br /><p><font face="Arial Narrow">CREATE PROCEDURE scoreBDI_sp AS</font><p><fontface="Arial Narrow">DECLARE X CURSOR</font><br /><font face="Arial Narrow">for SELECT * FROM BDI</font><br/><p><font face="Arial Narrow">DECLARE @STUDYID int</font><br /><font face="Arial Narrow">DECLARE @ADATE datetime</font><br/><font face="Arial Narrow">DECLARE @TPOINT smallint</font><br /><font face="Arial Narrow">DECLARE @PORCnvarchar</font><br /><font face="Arial Narrow">DECLARE @FNAME nvarchar</font><br /><font face="Arial Narrow">DECLARE@LNAME nvarchar</font><br /><font face="Arial Narrow">DECLARE @BDI1 smallint</font><br /><font face="ArialNarrow">DECLARE @BDI2 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI3 smallint</font><br /><fontface="Arial Narrow">DECLARE @BDI4 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI5 smallint</font><br/><font face="Arial Narrow">DECLARE @BDI6 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI7smallint</font><br /><font face="Arial Narrow">DECLARE @BDI8 smallint</font><br /><font face="Arial Narrow">DECLARE@BDI9 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI10 smallint</font><br /><font face="ArialNarrow">DECLARE @BDI11 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI12 smallint</font><br /><fontface="Arial Narrow">DECLARE @BDI13 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI14 smallint</font><br/><font face="Arial Narrow">DECLARE @BDI15 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI16smallint</font><br /><font face="Arial Narrow">DECLARE @BDI17 smallint</font><br /><font face="Arial Narrow">DECLARE@BDI18 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI19 smallint</font><br /><font face="ArialNarrow">DECLARE @BDI20 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI21 smallint</font><br /><fontface="Arial Narrow">DECLARE @TOTAL float</font><br /><font face="Arial Narrow">DECLARE @NUMANS float</font><br /><fontface="Arial Narrow">DECLARE @BDITOT float</font><br /><font face="Arial Narrow">DECLARE @CLEANED char</font><br /><fontface="Arial Narrow">DECLARE @NEW char</font><p><font face="Arial Narrow">SET @TOTAL=0</font><br /><font face="ArialNarrow">SET @BDITOT=0</font><br /><font face="Arial Narrow">SET @NUMANS=0</font><p><font face="Arial Narrow">OPENX</font><p><font face="Arial Narrow">FETCH NEXT FROM X INTO @STUDYID, @ADATE, @TPOINT, @PORC, @FNAME, @LNAME,@BDI1, @BDI2, @BDI3, @BDI4, @BDI5, @BDI6, @BDI7, @BDI8, @BDI9, @BDI10, @BDI11, @BDI12, @BDI13, @BDI14, @BDI15, @BDI16,@BDI17, @BDI18, @BDI19, @BDI20, @BDI21, @TOTAL, @NUMANS, @BDITOT, @CLEANED, @NEW</font><p><font face="Arial Narrow">WHILE(@@FETCH_status=0) BEGIN</font><p> <font face="Arial Narrow">IF @BDI1 <> 999 </font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI1</font><br/> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <fontface="Arial Narrow"> END </font><p> <font face="Arial Narrow">IF @BDI2 <> 999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI2</font><br/> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <fontface="Arial Narrow"> END</font><br /><font face="Arial Narrow"> </font><br /> <font face="Arial Narrow">IF @BDI3 <> 999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="ArialNarrow"> SET @TOTAL=@TOTAL+@BDI3</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br/> <font face="Arial Narrow"> END</font><br /><font face="Arial Narrow"> </font><br/> <font face="Arial Narrow"> IF @BDI4 <> 999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI4</font><br /> <fontface="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <font face="Arial Narrow"> END</font><br/> <font face="Arial Narrow"> </font><br /> <font face="Arial Narrow"> IF @BDI5 <>999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI5</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br/> <font face="Arial Narrow"> END</font><br /> <font face="Arial Narrow"> </font><br /> <font face="Arial Narrow"> IF @BDI6 <> 999</font><br /> <font face="ArialNarrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI6</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <font face="Arial Narrow"> END</font><br/> <font face="Arial Narrow"> </font><br /> <font face="Arial Narrow"> IF @BDI7 <>999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI7</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br/> <font face="Arial Narrow"> END</font><br /> <font face="Arial Narrow"> </font><br /> <font face="Arial Narrow"> IF @BDI8 <> 999</font><br /> <font face="ArialNarrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI8</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <font face="Arial Narrow"> END</font><br/> <font face="Arial Narrow"> </font><br /> <font face="Arial Narrow"> IF @BDI9 <>999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI9</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br/> <font face="Arial Narrow"> END</font><br /> <font face="Arial Narrow"> </font><br /> <font face="Arial Narrow"> IF @BDI10 <> 999</font><br /> <font face="ArialNarrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI10</font><br/> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <fontface="Arial Narrow"> END</font><br /> <font face="Arial Narrow"> </font><br /> <font face="ArialNarrow"> IF @BDI11 <> 999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI11</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <font face="Arial Narrow"> END</font><br /> <font face="ArialNarrow"> </font><br /> <font face="Arial Narrow"> IF @BDI12 <> 999</font><br /> <fontface="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI12</font><br/> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <fontface="Arial Narrow"> END</font><br /> <font face="Arial Narrow"> </font><br /> <font face="ArialNarrow"> IF @BDI13 <> 999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI13</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <font face="Arial Narrow"> END</font><br /> <fontface="Arial Narrow"> </font><br /> <font face="Arial Narrow"> IF @BDI14 <> 999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI14</font><br/> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <fontface="Arial Narrow"> END</font><br /> <font face="Arial Narrow"> </font><br /> <font face="ArialNarrow"> IF @BDI15 <> 999 </font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI15</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <font face="Arial Narrow"> END</font><br /> <font face="ArialNarrow"> </font><br /> <font face="Arial Narrow"> IF @BDI16 <> 999</font><br /> <fontface="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI16</font><br/> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <fontface="Arial Narrow"> END</font><br /> <font face="Arial Narrow"> </font><br /> <font face="ArialNarrow"> IF @BDI17 <> 999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI17</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <font face="Arial Narrow"> END</font><br /> <font face="ArialNarrow"> </font><br /> <font face="Arial Narrow"> IF @BDI18 <> 999</font><br /> <fontface="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI18</font><br/> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <fontface="Arial Narrow"> END</font><br /> <font face="Arial Narrow"> </font><br /> <font face="ArialNarrow"> IF @BDI19 <> 999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI19</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <font face="Arial Narrow"> END</font><br /> <font face="ArialNarrow"> </font><br /> <font face="Arial Narrow"> IF @BDI20 <> 999</font><br /> <fontface="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI20</font><br/> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <fontface="Arial Narrow"> END</font><br /> <font face="Arial Narrow"> </font><br /> <font face="ArialNarrow"> IF @BDI21 <> 999</font><br /> <font face="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @TOTAL=@TOTAL+@BDI21</font><br /> <font face="Arial Narrow"> SET @NUMANS=@NUMANS+1</font><br /> <font face="Arial Narrow"> END</font><br /> <font face="ArialNarrow"> </font><br /> <font face="Arial Narrow"> IF @NUMANS > 16 </font><br /> <fontface="Arial Narrow"> BEGIN</font><br /> <font face="Arial Narrow"> SET @BDITOT=round(@TOTAL * (21.0 /@NUMANS),0)</font><br /> <br /> <font face="Arial Narrow">END</font><br /> <font face="Arial Narrow"> ELSE</font><br /> <font face="Arial Narrow">BEGIN</font><br/> <font face="Arial Narrow">SET @BDITOT=999</font><br /> <br /> <font face="Arial Narrow">END </font><br /> <br /><font face="Arial Narrow">update BDIset BDITOT=@BDITOT, TOTAL=@BDITOT, NUMANS=@NUMANS, NEW='N' WHERE [ID]=@studyid and ADATE=@adate and TPOINT=@TPOINT ANDPORC=@PORC</font><p> <br /> <br /><font face="Arial Narrow">fetch next from X into @STUDYID, @ADATE, @TPOINT,@PORC, @FNAME, @LNAME, @BDI1, @BDI2, @BDI3, @BDI4, @BDI5, @BDI6, @BDI7, @BDI8, @BDI9, @BDI10, @BDI11, @BDI12, @BDI13,@BDI14, @BDI15, @BDI16, @BDI17, @BDI18, @BDI19, @BDI20, @BDI21, @TOTAL, @NUMANS, @BDITOT, @CLEANED, @NEW</font><p><fontface="Arial Narrow">SET @TOTAL=0</font><br /><font face="Arial Narrow">SET @NUMANS=0</font><br /><br /><p><fontface="Arial Narrow">END</font><br /><p><font face="Arial Narrow">CLOSE X</font><br /><font face="Arial Narrow">DEALLOCATEX</font>
В списке pgsql-sql по дате отправления: