语法错误（-201）为什么？

error 发表于 Dev

错误

为什么在此SPL过程中出现语法错误？我收到的唯一消息是在Server Studio中使用语法检查时存在一些错误。

create function najboljihN(n smallint)
returning char(10) as jmbag, char(50) as prezime, char(50) as ime, decimal(3,2) as prosjek;

   define jmbag like student.jmbag;
   define prezime like student.prezime;
   define ime like student.ime;
   define prosjek decimal(3,2);
   define i integer;

  for i=1 to n step 1
 SELECT student.jmbag, 
   student.prezimestudent, 
   student.imestudent, 
   Avg(ocjena) 
 INTO   jmbag, prezime, ime, prosjek 
FROM   student 
   JOIN upisanpredmet 
     ON student.jmbag = upisanpredmet.jmbag 
GROUP  BY student.jmbag, 
      student.prezimestudent, 
      student.imestudent 
 ORDER  BY ocjena, 
      student.prezimestudent asc, 
      student.imestudent asc; 

 RETURN jmbag, prezime, ime, ocjena WITH RESUME;
 end for;

end function;

吉尼斯

在这种情况下，您不能使用order by子句。如果没有order by子句，则可以编译函数。

create function najboljihN(n smallint)
     returning char(10) as jmbag, char(50) as prezime, char(50) as ime, decimal(3,2) as prosjek;

        define jmbag like student.jmbag;
        define prezime like student.prezime;
        define ime like student.ime;
        define prosjek decimal(3,2);
        define i integer;

        for i = 1 to n step 1
      SELECT jmbag, 
        prezimestudent, 
         imestudent, 
        Avg(ocjena) 
      INTO   jmbag, prezime, ime, prosjek 
     FROM   student 
        JOIN upisanpredmet 
          ON student.jmbag = upisanpredmet.jmbag 
     GROUP  BY student.jmbag, 
           student.prezimestudent, 
           student.imestudent; 
     -- ORDER  BY ocjena, 
     --      student.prezimestudent asc, 
     --      student.imestudent asc; 

      RETURN jmbag, prezime, ime, prosjek WITH RESUME;
      end for;

     end function;

我不完全了解您对循环的意图，因为您执行了n次相同的sql。但是，如果确实如此，则可以将结果写入临时表，然后从临时表返回值。

CREATE FUNCTION najboljihN(n SMALLINT)
   RETURNING CHAR(10) AS jmbag, CHAR(50) AS prezime, CHAR(50) AS ime, DECIMAL(3, 2) AS prosjek;

   DEFINE jmbag LIKE student.jmbag;
   DEFINE prezime LIKE student.prezime;
   DEFINE ime LIKE student.ime;
   DEFINE prosjek DECIMAL(3, 2);
   DEFINE i INTEGER;

 -- CREATE Temp Table    
   CREATE TEMP TABLE t1(
            jmbag     CHAR(10),
            prezime   CHAR(50),
            ime       CHAR(50),
            prosjek   DECIMAL(3, 2))

 -- Fill Temp Table
   FOR i = 1 TO n step 1 

INSERT INT t1
       SELECT jmbag, prezimestudent, imestudent, Avg(ocjena) as prosjek
       FROM student JOIN upisanpredmet ON student.jmbag = upisanpredmet.jmbag
       GROUP BY student.jmbag, student.prezimestudent, student.imestudent;

   END FOR;

 -- Return from temp table
   FOREACH
       SELECT jmbag, prezimestudent, imestudent, prosjek 
    INTO jmbag, prezime, ime, prosjek
       FROM t1 ORDER BY prosjek

       RETURN jmbag, prezime, ime, prosjek WITH RESUME;

   END FOREACH;

END FUNCTION;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。