Troubleshooting on Dynamic SQL in MySQL

pratikrrana · 2022-05-16T17:33:58+00:00

There was an error rendering this rich post.

Hello,

I am having trouble on running below Dynamic SQL query. Can someone have a look and troubleshoot what am I doing incorrectly? Also, is there any way I can able to print Dynamic SQL query?

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'sum(case when x.question_text = ''',

question_text,

''' then x.answer_text end) AS ',

question_text, '`'

)

) INTO @sql

from

(SELECT DISTINCT aa.application_id,aq.question_text,aa.answer_text

from tablea aq

inner join tableb aa on aa.application_question_id = aq.id

where aq.program_id = 136)x;

SET @sql

= CONCAT('SELECT aa.application_id, ', @sql, '

from tablea aq

inner join tableb aa on aa.application_question_id = aq.id

group by aa.application_id;');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Find more posts tagged with

Accepted answers

All comments

pratikrrana

This is the error I am getting:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Manufacturing - Does your business either enable or stand to benefit from the ut' at line 1

jackb1117

What is the error message or unexpected behavior you are seeing?

GrantSmith

@pratikrrana

I'd recommend breaking your SQL down into chunks.

Start by looking at what's being returned from your first query:

SELECT

 GROUP_CONCAT(DISTINCT

  CONCAT(

  'sum(case when x.question_text = ''',

  question_text,

  ''' then x.answer_text end) AS ',

  question_text, '`'

  )

 )
from

(SELECT DISTINCT aa.application_id,aq.question_text,aa.answer_text

from tablea aq

inner join tableb aa on aa.application_question_id = aq.id

where aq.program_id = 136)x;

Also looking at this you have an unmatched back tick (`) which may cause some issues. Also, are you certain you want SUM here and not COUNT since SUM is for numerical values but it appears you're wanting to count the number of answers in the answer_text column.

The error you're receiving appears to be saying that you're attempting to utilize some text as actual SQL which it doesn't like. Make sure your string is quoted properly.

jackb1117

What is the error message or unexpected behavior you are seeing?

pratikrrana

@GrantSmith, thanks for your response. I did able to run query successfully but only thing is that I can't able to display whole question_text_orig as column header. With using below query, it running fine using displaying first 5 characters as question_text.

I have tried using group_concat_max_len as well but its still giving me below error.

Error Message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Manufacturing - Does your business either enable or stand to benefit from the ut' at line 1

SQL Query:

SET session group_concat_max_len=9999999999;

DROP TEMPORARY TABLE tmp_application_data;

CREATE TEMPORARY TABLE tmp_application_data

SELECT DISTINCT aa.application_id,aq.question_text as question_text_orig,left(aq.question_text,5) as question_text,aa.answer_text

from table1 aq

inner join table2 aa on aa.application_question_id = aq.id

where aq.program_id = 136;

-- select * from tmp_application_data;

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'max(case when question_text = ''',

question_text,

''' then answer_text end) ',

question_text

)

) INTO @sql

from

tmp_application_data;

SET @sql = CONCAT('SELECT application_id, ', @sql, '

FROM tmp_application_data

GROUP BY application_id');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

pratikrrana

@jackb1117, this is the error message I am getting.

Error Message:

Please refer my previous comment as progress on discussion topic.