Error: cannot insert multiple commands into a prepared statement

GusGarofalo

I am Trying to insert a query from Amazon Redshift as data input. The query creates a temporary table and it gives me this error:
"Error SQLPrepare: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42601] ERROR: cannot insert multiple commands into a prepared statement"

The query looks somewhat like this:

DROP TABLE IF EXISTS Table1;

CREATE TEMPORARY TABLE Table1 AS

 

SELECT DISTINCT 

         rc.feat1

       , rc.feat2

       , rc.feat3

 

FROM database1 rc

 

WHERE 1=1 

  AND rc.feat1 ='x'

  AND rc.feat1 = 'z'

                            

GROUP BY   rc.feat1

       , rc.feat2

       , rc.feat3

;

 

DROP TABLE IF EXISTS Table2;

CREATE TEMPORARY TABLE Table2 AS

 

SELECT g.Feat1

      ,a.Feat1

      ,a.Feat2

      ,a.Feat3

      ,c.Feat1

 

FROM Table1 g

LEFT JOIN database2 c on c.Feat1 = g.Feat1 

LEFT JOIN database3 a on a.Feat1 = c.Feat1

;

 

SELECT

     g.*

    ,n.Feat1

    ,n.Feat2

    ,n.Feat3

 

FROM Table1 g

LEFT JOIN Table2 n on n.Feat1= g.Feat1 

It runs normally on Alation. Please advise

caltang

I am not an expert but based on your error, it is stating that you cannot run multiple commands - you are doing exactly that with DROP TABLE, CREATE TABLE, SELECT - which is why it is failing.

Not sure if you can wrap the whole thing as a stored proc and call that as a command.