Using GroupBy in Stored Procedures

Help
1

Is there any limitations in using GroupBy / OrderBy in Stored procedures in MemSQL?
We are getting the below error while executing the SQL in SPs -
“Feature ‘GROUP BY’ variable is not supported by memsql”

Please advise.

2

We don’t support GROUP BY of a MPSQL variable only (an expression containing a variable should be fine).

Can you share an example query?

3

Please find the below SQL query which we have used in stored procedure.

select cfg_id,cfg_nm,status_flag,count(platform_id) from (

select distinct cast(((total_qualified_parts/total_parts) * 100) as UNSIGNED INTEGER) as risk_percentage,A.cfg_id,A.cfg_nm,B.platform_id,A.status_flag from

(select count(distinct rpm.part_number) as total_parts,cfg_id,cfg_nm,rpm.status_flag,dw_commodity_id from dm_rp_matrix rpm

inner join dsc_partlifecyclemaster partlc on partlc.part_number = rpm.part_number

inner join dsc_itemlobmaster lob on lob.part_number = rpm.part_number

where rpm.dw_commodity_id = 151 and rpm.region = ‘DAO’ and rpm.site = ‘AUSTIN’

and partlc.lifecycle in (‘A Revision’) group by cfg_id,cfg_nm,rpm.status_flag,dw_commodity_id) A

inner join

(select count(distinct rpm.part_number) as total_qualified_parts,cfg_id,cfg_nm,rpm.platform_id,rpm.status_flag,dw_commodity_id from dm_rp_matrix rpm

inner join dsc_partlifecyclemaster partlc on partlc.part_number = rpm.part_number

inner join dsc_itemlobmaster lob on lob.part_number = rpm.part_number

inner join dsc_platformlifecyclemaster pltc on pltc.platform_id=rpm.platform_id

where rpm.dw_commodity_id = 151 and rpm.region = ‘DAO’ and rpm.site = ‘AUSTIN’

and partlc.lifecycle in (‘A Revision’)

group by cfg_id,cfg_nm,rpm.platform_id,rpm.status_flag,dw_commodity_id)B on A.cfg_id=B.cfg_id

inner join dm_dashboard_setting C on A.dw_commodity_id=C.dw_commodity_id

where ((total_qualified_parts/total_parts) * 100) < C.risk_platform_threshold ) D

group by cfg_id,cfg_nm,status_flag

4

which of the group-bys contains a MPSQL variable?

GROUP-BYs like this are not supported today:

create procedure group_by_arg(arg int) as
begin
    insert into t select * from t group by arg;
end //

Grouping by a constant is a special case in SQL as you likely already know, it means group by the column at that index in the project list - we don’t support doing that via a MPSQL variable which is why your getting the error.

5

If you absolutely must include the value of a variable in a GROUP BY clause then you could use dynamic SQL to first expand it into a constant literal in a string, then run the string (e.g. with EXECUTE IMMEDIATE).

1 Like
6

Can you share an example ?