Troubleshooting on Dynamic SQL in MySQL

pratikrrana
pratikrrana Member
edited March 2023 in SQL DataFlows

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;

Answers

  • pratikrrana
    pratikrrana Member

    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
    jackb1117 Contributor

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

  • GrantSmith
    GrantSmith Coach

    @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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jackb1117
    jackb1117 Contributor

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

  • pratikrrana
    pratikrrana Member

    @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
    pratikrrana Member

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

    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


    Please refer my previous comment as progress on discussion topic.

Categories