You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:22.0) Gecko/20100101 Firefox/22.0
Build Identifier:
We use SQLWorkbench as a client but the same problem happens using mclient.
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')
Date: 2013-07-30 15:56:32 +0200
From: Radovan Bičiště <<radovan.biciste>>
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes
Last updated: 2013-09-27 13:47:14 +0200
Comment 18943
Date: 2013-07-30 15:56:32 +0200
From: Radovan Bičiště <<radovan.biciste>>
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:22.0) Gecko/20100101 Firefox/22.0
Build Identifier:
We use SQLWorkbench as a client but the same problem happens using mclient.
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]
Reproducible: Always
Steps to Reproduce:
Actual Results:
Received the error:
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]
Expected Results:
successful end of the procedure run
Comment 18944
Date: 2013-07-30 16:07:13 +0200
From: Radovan Bičiště <<radovan.biciste>>
Created attachment 213
simple test case
Simplified test case.
Comment 18954
Date: 2013-07-31 10:01:43 +0200
From: @njnes
fixed, the correct interface requires another bit (abort on error).
Comment 18957
Date: 2013-07-31 10:49:29 +0200
From: MonetDB Mercurial Repository <>
Changeset 6b9d21862a6a made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=6b9d21862a6a
Changeset description:
Comment 18962
Date: 2013-07-31 12:52:49 +0200
From: Radovan Bičiště <<radovan.biciste>>
Simple workaround is to use combination of SUM and COUNT.
The text was updated successfully, but these errors were encountered: