SQL: AVG function works in SQL but it does not in procedure

29 Jul 2013


      Hello,
We are unable to solve the following problem.
This SQL runs fine as standalone:
INSERT INTO crmkartastitek (
       cislo_karty,
       stitek_id,
       stitek_nazev,
       stitek_upresneni,
       stitek_hodnota_decimal
     )
     select cislo_karty,170,'KEO','',round(avg(obrat),2)
     from
      (select ckp.cislo_karty, ckp.id_expedice, sum(ckp.pcena_sd) as obrat
       from crmkartapohyby ckp
       join crmkartaaktivita ck on ckp.cislo_karty = ck.cislo_karty
       where ckp.pohyb in ('VR', 'VV')
         and ck.aktivita between 1 and 3 -- omezení na LM a L3M
       group by ckp.cislo_karty, ckp.id_expedice
      ) as exp_pripady
     group by cislo_karty;

But when wrapped in procedure raises an error as follows:
CREATE PROCEDURE crm_segm_keo_rl(p_interval INT, p_datum timestamp)
BEGIN
   -- hlavička procedury
   DECLARE v_proc VARCHAR(250);
   SET v_proc = 'crm_segm_kpn_rl';
   -- nastav id pravidla
   DECLARE v_stitek_id int;
   DECLARE v_stitek_nazev,v_stitek_upresneni VARCHAR(200);
   -- nastav štítek
   SET v_stitek_id = 170;
   SELECT nazev,upresneni INTO v_stitek_nazev,v_stitek_upresneni FROM 
crmregstitek WHERE stitek_id = v_stitek_id;
   -- proveď výpočet pouze pokud spouštím měsíční přepočet
   IF p_interval = crm_segm_vrat_spusteni(v_stitek_id) THEN
     INSERT INTO crmkartastitek (
       cislo_karty,
       stitek_id,
       stitek_nazev,
       stitek_upresneni,
       stitek_hodnota_decimal
     )
     select 
cislo_karty,v_stitek_id,v_stitek_nazev,v_stitek_upresneni,round(avg(obrat),2) 

     from
      (select ckp.cislo_karty, ckp.id_expedice, sum(ckp.pcena_sd) as obrat
       from crmkartapohyby ckp
       join crmkartaaktivita ck on ckp.cislo_karty = ck.cislo_karty
       where ckp.pohyb in ('VR', 'VV')
         and ck.aktivita between 1 and 3 -- omezení na LM a L3M
       group by ckp.cislo_karty, ckp.id_expedice
      ) as exp_pripady
     group by cislo_karty;
   END IF;
END;
Procedure is created fine.

call crm_segm_keo_rl(3,timestamp '2013-07-01 00:00:00');
Causes:
An error occurred when executing the SQL command:
call crm_segm_keo_rl(3,timestamp '2013-07-01 00:00:00')

TypeException:user.crm_segm_keo_rl[362]:'aggr.subavg' undefined in: 
_546:bat[:any,:dbl] := aggr.subavg(_543:bat[:oid,:dbl], 
_209:bat[:oid,:oid], r1_209:bat[:oid,:oid], _535:bit) [SQL State=22000]
Next: TypeException:user.s4_24[5]:'user.crm_segm_keo_rl' undefined in: 
_9:void := user.crm_segm_keo_rl(_5:int, _7:timestamp) [SQL State=22000]
Next: program contains errors [SQL State=39000]


Are we missing something ?
Thank you,
Radovan
--
Radovan Biciste
Hradec Kralove
Czech Republic

SQL: AVG function works in SQL but it does not in procedure

Radovan Bičiště