title: How to write a dynamic SQL code in Teradata?
body: <p>I am trying to convert this code (which accepts a column name as input, does calculations on the input column and inserts data into a table). Now that I know that we can't pass table/column names through a stored proc or macro, I came to know that we can do it through a dynamic SQL. I am not sure how the syntax should be as I could not find a clear and easy example. Below is my code which needs to be converted:</p>
  
  <pre><code>INSERT INTO PROD_CE_WORK_SPACE.NPVAZ_CVM_CHECK_TEST_CASE_5
  
                      SELECT 
                                  'DURATION_CELL_CURR' AS COLMN,
                                  PW_END_DATE,
                                  'ACPT' AS TAB,
                                  MIN(DURATION_CELL_CURR) AS PER_MIN,
                                  MIN(CASE WHEN SEQNUM / 0.25 &gt;= CNT THEN DURATION_CELL_CURR END) AS PER_25,
                                  MIN(CASE WHEN SEQNUM / 0.50 &gt;= CNT THEN DURATION_CELL_CURR END) AS PER_50,
                                  MIN(CASE WHEN SEQNUM / 0.75 &gt;= CNT THEN DURATION_CELL_CURR END) AS PER_75,
                                  MAX(DURATION_CELL_CURR) AS PER_MAX
                      FROM (
                                  SELECT PC.*,
                                   ROW_NUMBER() OVER (ORDER BY DURATION_CELL_CURR) AS SEQNUM,
                                   COUNT(*) OVER () AS CNT
                                    FROM PROD_EXP_DL_CVM.ACPT_PROD_CVM PC
                                    WHERE PC.PW_END_DATE =  '2017-01-17'
                                  )A
                                  GROUP BY 1,2,3
  
  UNION ALL
  
                      SELECT 
                                  'DURATION_CELL_CURR' AS COLMN,
                                  PW_END_DATE,
                                  'PROD' AS TAB,
                                  MIN(DURATION_CELL_CURR) AS PER_MIN,
                                  MIN(CASE WHEN SEQNUM / 0.25 &gt;= CNT THEN DURATION_CELL_CURR END) AS PER_25,
                                  MIN(CASE WHEN SEQNUM / 0.50 &gt;= CNT THEN DURATION_CELL_CURR END) AS PER_50,
                                  MIN(CASE WHEN SEQNUM / 0.75 &gt;= CNT THEN DURATION_CELL_CURR END) AS PER_75,
                                  MAX(DURATION_CELL_CURR) AS PER_MAX
                      FROM (
                                  SELECT PC.*,
                                   ROW_NUMBER() OVER (ORDER BY DURATION_CELL_CURR) AS SEQNUM,
                                   COUNT(*) OVER () AS CNT
                                    FROM PROD_EXP_DL_CVM.PROD_CVM PC
                                    WHERE PC.PW_END_DATE =  '2017-01-17'
                                  )B
                                  GROUP BY 1,2,3
  </code></pre>
  
  <p>Below is my understanding of its conversion in dynamic SQL:</p>
  
  <pre><code>REPLACE PROCEDURE PROD_CE_WORK_SPACE.NPVAZ_CVM_CHECK_TEST_CASE (IN COL CHAR(50))
  (
  BEGIN REQUEST
  CALL DBC.SYSEXECSQL
  ('
  INSERT INTO PROD_CE_WORK_SPACE.NPVAZ_CVM_CHECK_TEST_CASE_5
                              SELECT
                              '||COL||' AS COLMN,
                              PW_END_DATE,
                              ''ACPT'' AS TAB,
                              MIN( '||COL||') AS PER_MIN,
                              MIN(CASE WHEN SEQNUM / 0.25 &gt;= CNT THEN  '||COL||' END) AS PER_25,
                              MIN(CASE WHEN SEQNUM / 0.50 &gt;= CNT THEN  '||COL||' END) AS PER_50,
                              MIN(CASE WHEN SEQNUM / 0.75 &gt;= CNT THEN  '||COL||' END) AS PER_75,
                              MAX( '||COL||') AS PER_MAX
                              FROM (
                                          SELECT PC.*,
                                           ROW_NUMBER() OVER (ORDER BY  '||COL||') AS SEQNUM,
                                           COUNT(*) OVER () AS CNT
                                            FROM PROD_EXP_DL_CVM.ACPT_PROD_CVM PC
                                            WHERE PC.PW_END_DATE =  '2017-01-17'
                                           )A
                              GROUP BY 1,2,3
  
              UNION ALL
  
                              SELECT 
                              '||COL||' AS COLMN,
                              PW_END_DATE,
                              ''PROD'' AS TAB,
                              MIN( '||COL||') AS PER_MIN,
                              MIN(CASE WHEN SEQNUM / 0.25 &gt;= CNT THEN  '||COL||' END) AS PER_25,
                              MIN(CASE WHEN SEQNUM / 0.50 &gt;= CNT THEN  '||COL||' END) AS PER_50,
                              MIN(CASE WHEN SEQNUM / 0.75 &gt;= CNT THEN  '||COL||' END) AS PER_75,
                              MAX( '||COL||') AS PER_MAX
                              FROM (
                                  SELECT PC.*,
                                   ROW_NUMBER() OVER (ORDER BY  '||COL||') AS SEQNUM,
                                   COUNT(*) OVER () AS CNT
                                    FROM PROD_EXP_DL_CVM.PROD_CVM PC
                                    WHERE PC.PW_END_DATE =  '2017-01-17'
                                  )B
                            GROUP BY 1,2,3
  ')
  
  END REQUEST;
  );
  </code></pre>
  
  <p>I understand I have to resolve many errors before this code is run-ready. So one of the first errors I am getting is below:</p>
  
  <p><a href="https://i.stack.imgur.com/gyaWK.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gyaWK.jpg" alt="enter image description here"></a></p>
  
  <p>Can anyone please help me with this. I have to calculate quantile distribution of 60+ such columns which doing manually is insane.
  Much appreciated.
  Piyush</p>