629346_en-US

liliconcom

Re: SQL - cannot insert multiple comands into prepared statement

Dear Ron, I did Not Do This so far, but here you are asking new sql query for two tables, I suppose, it can not handle that. Did you try execute sql instead?

Other possibility would be union between both queries to have one only. But this requires exactly the same columns in same order.

 

Re: SQL - cannot insert multiple comands into prepared statement

i get the same error if i try it using the following script:

// connect to database - open data base
DBConnection = Create Database Connection(
"ODBC:DSN=database-project;EPWD=.....;");

sql_events = 
"\[
select * from "public"."logs"  "t1";
select * from "public"."logs"  "t1";
]\"
dt1 = execute sql ( DBConnection,  eval insert ( sql_events , "¤") ,  "Events from logs"  );

the issue may be similar to this discussion:
https://community.jmp.com/t5/Discussions/SQL-query-to-ODBC-datasource-SQL-server-with-temporary-tables/td-p/457582

but the solution there is not exactly applicable to redshift.

Re: SQL - cannot insert multiple comands into prepared statement

Generally you can't execute two SQL statements with one call to EXECUTE SQL.  Both statements are the same; perhaps you were just showing an example?  

Why are you trying to run two SQL statements at once?  EXECUTE SQL returns a dataset for each SQL statement.

Why not just run two EXECUTE SQL commands?

Re: SQL - cannot insert multiple comands into prepared statement

you are right @pmroz , it was just an example of two statements.
sometimes i need two statements to create a sub-sample (temporary table) or define a function.
in most cases i manage to use the "With" statement instead of a temporary table but not always. especially when using the "With" statements requires very complex sub-queries that are not allowed.   .

Re: SQL - cannot insert multiple comands into prepared statement

This approach might help:

Call a PL/SQL procedure that does the super-complex query, and creates a temporary table.

Then select * from the temporary table.

Here's some code that does something like that:

event_sql = evalinsert(
"DECLARE 
 l_proc_name VARCHAR2 (100) := 'Get_Reference_PTs'; 
 l_user_SID  NUMBER   := SYS_CONTEXT('USERENV', 'SID'); 
 l_user_name VARCHAR2 (30) := UPPER(SYS_CONTEXT('USERENV', 'OS_USER')); 
BEGIN 

    INSERT INTO quality_attrib (SID, OS_USER, Completion_Time, Progress_Text) 
        VALUES (l_user_SID, l_user_name, SYSTIMESTAMP, 'START Get_Reference_PTs');  
        COMMIT; 

 INSERT INTO Ref_PT_List (SID, OS_USER, PT_Name) 
 (SELECT DISTINCT l_user_SID, l_user_name, mu.pt_name 
 FROM info ci, 
   drug cd, 
      reaction cr, 
      meddra_uta mu,  
  WHERE cd.case_id = ci.case_id 
    AND cr.case_id = ci.case_id 
    AND cr.reported_meddra_uta_id = mu.meddra_uta_id 
    AND cd.cdd_generic_id = rd.cdd_generic_id 
    AND ci.report_type_code_id = 8  
    AND rd.calendar_id =  ^one_calendar_id^
    AND cd.drug_character_code_id IN (31, 32)
 ^pt_clause^
 ^date_clause^
 ^event_type_clause^
 ); 
 COMMIT; 

    INSERT INTO quality_attrib (SID, OS_USER, Completion_Time, Progress_Text) 
        VALUES (l_user_SID, l_user_name, SYSTIMESTAMP, 'END Get_Reference_PTs');  
        COMMIT; 

EXCEPTION 
    WHEN OTHERS 
        THEN 
        log_error_message(SQLCODE, SQLERRM, l_proc_name); 
END; ");

Execute SQL(dbc, event_sql);

Execute SQL(dbc, "select * from ref_pt_list");

Re: SQL - cannot insert multiple comands into prepared statement

thank you very much @pmroz 
splitting the two statements into separate Execute SQL statements works perfect.

SQL - cannot insert multiple comands into prepared statement

dear members of the forum,
when using jmp to query amazon redshift i need to send two statements together but for some reason this i get an error saying:

unfortunately, my query is very elaborate and doesn't work with just a "Where" statement for each separate table.
if anyone has any idea how to overcome this issue it would be helpful.

Thanks

 

Advanced Statistical ModelingConsumer and Market ResearchData AccessDesign of ExperimentsMass Customization