CODEWITHSUNDEEP
sqlinformix
Error
Error

Reputation: 825

Syntax error (-201) why?

Why am I getting syntax error in this SPL procedure? Only message I got is that exists some error while using syntax check in 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;

Upvotes: 1

Views: 1217

Answers (2)

ceinmart
ceinmart

Reputation: 1836

The Informix manuals says :

The ORDER BY clause implies that the query returns more than one row. In SPL, the database server issues an error if you specify the ORDER BY clause without a FOREACH loop to process the returned rows individually within the SPL routine.

Upvotes: 2

SGeis
SGeis

Reputation: 266

You can't use the order by clause in this case. Without the order by clause you can compile your function. 

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;

I do not exactly understand what you intend with your loop, because you do n times the same sql. But if this is realy your intention you can write the results into a temp table and then return your values from the temp table.

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;

Upvotes: 3

Related Questions