0

I seem to have hit a limit on generating the code for my crosstab stored procedure - is there a LIMT???

This extract works - note the limitation of week_id < 12

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(case when ref_date_conversion.week_id = ',
ref_date_conversion.week_id,
' then ztablemaster.net_stake else 0 end) `',
week_id, '`' 
)
) INTO @sql
from ref_date_conversion
Where week_id <12
;

However if I try and run it for any more weeks it bombs out. The code generated for @sql seems to just run out. I am guessing mysql limits itself to a certain string length???

Is there a work around?

S

To explain a bit more

the @sql generates this when I run it < 12

sum(case when ref_date_conversion.week_id = 1 then ztablemaster.net_stake else 0 end) `1`,
sum(case when ref_date_conversion.week_id = 2 then ztablemaster.net_stake else 0 end) `2`,
sum(case when ref_date_conversion.week_id = 3 then ztablemaster.net_stake else 0 end) `3`,
sum(case when ref_date_conversion.week_id = 4 then ztablemaster.net_stake else 0 end) `4`,
sum(case when ref_date_conversion.week_id = 5 then ztablemaster.net_stake else 0 end) `5`,
sum(case when ref_date_conversion.week_id = 6 then ztablemaster.net_stake else 0 end) `6`,
sum(case when ref_date_conversion.week_id = 7 then ztablemaster.net_stake else 0 end) `7`,
sum(case when ref_date_conversion.week_id = 8 then ztablemaster.net_stake else 0 end) `8`,
sum(case when ref_date_conversion.week_id = 9 then ztablemaster.net_stake else 0 end) `9`,
sum(case when ref_date_conversion.week_id = 10 then ztablemaster.net_stake else 0 end) `10`,
sum(case when ref_date_conversion.week_id = 11 then ztablemaster.net_stake else 0 end) `11`

When I run it for more than 12 to unlimited I get the @sql cut off as below - its as though I have gone through a string limitation? - maybe I have not sure - or what I can do on it

sum(case when ref_date_conversion.week_id = 1 then ztablemaster.net_stake else 0 end) `1`,
sum(case when ref_date_conversion.week_id = 2 then ztablemaster.net_stake else 0 end) `2`,
sum(case when ref_date_conversion.week_id = 3 then ztablemaster.net_stake else 0 end) `3`,
sum(case when ref_date_conversion.week_id = 4 then ztablemaster.net_stake else 0 end) `4`,
sum(case when ref_date_conversion.week_id = 5 then ztablemaster.net_stake else 0 end) `5`,
sum(case when ref_date_conversion.week_id = 6 then ztablemaster.net_stake else 0 end) `6`,
sum(case when ref_date_conversion.week_id = 7 then ztablemaster.net_stake else 0 end) `7`,
sum(case when ref_date_conversion.week_id = 8 then ztablemaster.net_stake else 0 end) `8`,
sum(case when ref_date_conversion.week_id = 9 then ztablemaster.net_stake else 0 end) `9`,
sum(case when ref_date_conversion.week_id = 10 then ztablemaster.net_stake else 0 end) `10`,
sum(case when ref_date_conversion.week_id = 11 then ztablemaster.net_stake else 0 end) `11`
sum(case when ref_date_conversion.week_id = 12 then
user1479891
  • 139
  • 1
  • 2
  • 15
  • What do you mean by "Bombs-out" - if mysql was exceeding a limit you would expect an error to be returned, if you get a crash then you may be running out of memory or some such. – Giles Oct 14 '14 at 10:01
  • 2
    I'm going to guess you have an issue with the `group_concat_max_len` - see this post http://stackoverflow.com/questions/20910166/mysql-concat-max-length You'll have to edit this setting because the default max length is 1024. – Taryn Oct 14 '14 at 10:11
  • Many thanks - that was the issue appreciate the time answering for me: I added the following line at the top of my proc: SET SESSION group_concat_max_len = 100000; – user1479891 Oct 14 '14 at 12:34

0 Answers0