cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.
Discovery is online this week, April 16 and 18. Join us for these exciting interactive sessions.
Choose Language Hide Translation Bar
ron_horne
Super User (Alumni)

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
ron_horne_0-1683372905784.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

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");

View solution in original post

6 REPLIES 6
Georg
Level VII

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.

 

Georg
ron_horne
Super User (Alumni)

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

but the solution there is not exactly applicable to redshift.

pmroz
Super User

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?

ron_horne
Super User (Alumni)

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

pmroz
Super User

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");
ron_horne
Super User (Alumni)

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.