cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
%3CLINGO-SUB%20id%3D%22lingo-sub-629386%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%E5%9B%9E%E5%A4%8D%EF%BC%9ASQL%20-%20%E7%84%A1%E6%B3%95%E5%B0%87%E5%A4%9A%E5%80%8B%E5%91%BD%E4%BB%A4%E6%8F%92%E5%85%A5%E6%BA%96%E5%82%99%E5%A5%BD%E7%9A%84%E8%AA%9E%E5%8F%A5%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-629386%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3E%E8%A6%AA%E6%84%9B%E7%9A%84%E7%BE%85%E6%81%A9%EF%BC%8C%E5%88%B0%E7%9B%AE%E5%89%8D%E7%82%BA%E6%AD%A2%E6%88%91%E9%82%84%E6%B2%92%E6%9C%89%E9%80%99%E6%A8%A3%E5%81%9A%EF%BC%8C%E4%BD%86%E5%9C%A8%E9%80%99%E8%A3%A1%E4%BD%A0%E8%A6%81%E6%B1%82%E5%B0%8D%E5%85%A9%E5%80%8B%E8%A1%A8%E9%80%B2%E8%A1%8C%E6%96%B0%E7%9A%84%20sql%20%E6%9F%A5%E8%A9%A2%EF%BC%8C%E6%88%91%E6%83%B3%EF%BC%8C%E5%AE%83%E7%84%A1%E6%B3%95%E8%99%95%E7%90%86%E3%80%82%20%E6%82%A8%E6%98%AF%E5%90%A6%E5%98%97%E8%A9%A6%E6%94%B9%E7%82%BA%E5%9F%B7%E8%A1%8C%20sql%EF%BC%9F%3C%2FP%3E%3CP%3E%E5%8F%A6%E4%B8%80%E7%A8%AE%E5%8F%AF%E8%83%BD%E6%80%A7%E6%98%AF%E5%85%A9%E5%80%8B%E6%9F%A5%E8%A9%A2%E4%B9%8B%E9%96%93%E7%9A%84%E8%81%AF%E5%90%88%E5%8F%AA%E6%9C%89%E4%B8%80%E5%80%8B%E3%80%82%20%E4%BD%86%E9%80%99%E9%9C%80%E8%A6%81%E4%BB%A5%E7%9B%B8%E5%90%8C%E9%A0%86%E5%BA%8F%E5%AE%8C%E5%85%A8%E7%9B%B8%E5%90%8C%E7%9A%84%E5%88%97%E3%80%82%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-629450%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%E5%9B%9E%E5%A4%8D%EF%BC%9ASQL%20-%20%E7%84%A1%E6%B3%95%E5%B0%87%E5%A4%9A%E5%80%8B%E5%91%BD%E4%BB%A4%E6%8F%92%E5%85%A5%E6%BA%96%E5%82%99%E5%A5%BD%E7%9A%84%E8%AA%9E%E5%8F%A5%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-629450%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3E%E5%A6%82%E6%9E%9C%E6%88%91%E4%BD%BF%E7%94%A8%E4%BB%A5%E4%B8%8B%E8%85%B3%E6%9C%AC%E5%98%97%E8%A9%A6%E5%AE%83%EF%BC%8C%E6%88%91%E6%9C%83%E5%BE%97%E5%88%B0%E5%90%8C%E6%A8%A3%E7%9A%84%E9%8C%AF%E8%AA%A4%EF%BC%9A%3C%2FP%3E%0A%3CPRE%3E%3CCODE%20class%3D%22%20language-jsl%22%3E%2F%2F%20connect%20to%20database%20-%20open%20data%20base%0ADBConnection%20%3D%20Create%20Database%20Connection(%0A%22ODBC%3ADSN%3Ddatabase-project%3BEPWD%3D.....%3B%22)%3B%0A%0Asql_events%20%3D%20%0A%22%5C%5B%0Aselect%20*%20from%20%22public%22.%22logs%22%20%20%22t1%22%3B%0Aselect%20*%20from%20%22public%22.%22logs%22%20%20%22t1%22%3B%0A%5D%5C%22%0Adt1%20%3D%20execute%20sql%20(%20DBConnection%2C%20%20eval%20insert%20(%20sql_events%20%2C%20%22%C2%A4%22)%20%2C%20%20%22Events%20from%20logs%22%20%20)%3B%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%E8%A9%B2%E5%95%8F%E9%A1%8C%E5%8F%AF%E8%83%BD%E9%A1%9E%E4%BC%BC%E6%96%BC%E6%AD%A4%E8%A8%8E%E8%AB%96%EF%BC%9A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fcommunity.jmp.com%2Ft5%2FDiscussions%2FSQL-query-to-ODBC-datasource-SQL-server-with-temporary-tables%2Ftd-p%2F457582%22%20target%3D%22_blank%22%3E%20https%3A%2F%2Fcommunity.jmp.com%2Ft5%2FDiscussions%2FSQL-query-to-ODBC-datasource-SQL-server-with-temporary-tables%2Ftd-p%2F457582%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%E4%BD%86%E9%82%A3%E8%A3%A1%E7%9A%84%E8%A7%A3%E6%B1%BA%E6%96%B9%E6%A1%88%E4%B8%A6%E4%B8%8D%E5%AE%8C%E5%85%A8%E9%81%A9%E7%94%A8%E6%96%BC%E7%B4%85%E7%A7%BB%E3%80%82%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-629513%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%E5%9B%9E%E5%A4%8D%EF%BC%9ASQL%20-%20%E7%84%A1%E6%B3%95%E5%B0%87%E5%A4%9A%E5%80%8B%E5%91%BD%E4%BB%A4%E6%8F%92%E5%85%A5%E6%BA%96%E5%82%99%E5%A5%BD%E7%9A%84%E8%AA%9E%E5%8F%A5%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-629513%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3E%E9%80%9A%E5%B8%B8%EF%BC%8C%E6%82%A8%E4%B8%8D%E8%83%BD%E9%80%9A%E9%81%8E%E4%B8%80%E6%AC%A1%E8%AA%BF%E7%94%A8%20EXECUTE%20SQL%20%E4%BE%86%E5%9F%B7%E8%A1%8C%E5%85%A9%E6%A2%9D%20SQL%20%E8%AA%9E%E5%8F%A5%E3%80%82%E5%85%A9%E7%A8%AE%E8%AA%AA%E6%B3%95%E9%83%BD%E6%98%AF%E4%B8%80%E6%A8%A3%E7%9A%84%EF%BC%9B%E4%B9%9F%E8%A8%B1%E4%BD%A0%E5%8F%AA%E6%98%AF%E8%88%89%E5%80%8B%E4%BE%8B%E5%AD%90%EF%BC%9F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%E7%82%BA%E4%BB%80%E9%BA%BC%E8%A6%81%E5%90%8C%E6%99%82%E9%81%8B%E8%A1%8C%E5%85%A9%E5%80%8B%20SQL%20%E8%AA%9E%E5%8F%A5%EF%BC%9FEXECUTE%20SQL%20%E7%82%BA%E6%AF%8F%E5%80%8B%20SQL%20%E8%AA%9E%E5%8F%A5%E8%BF%94%E5%9B%9E%E4%B8%80%E5%80%8B%E6%95%B8%E6%93%9A%E9%9B%86%E3%80%82%3C%2FP%3E%0A%3CP%3E%E7%82%BA%E4%BB%80%E9%BA%BC%E4%B8%8D%E5%8F%AA%E9%81%8B%E8%A1%8C%E5%85%A9%E5%80%8B%20EXECUTE%20SQL%20%E5%91%BD%E4%BB%A4%E5%91%A2%EF%BC%9F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-630361%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%E5%9B%9E%E5%A4%8D%EF%BC%9ASQL%20-%20%E7%84%A1%E6%B3%95%E5%B0%87%E5%A4%9A%E5%80%8B%E5%91%BD%E4%BB%A4%E6%8F%92%E5%85%A5%E6%BA%96%E5%82%99%E5%A5%BD%E7%9A%84%E8%AA%9E%E5%8F%A5%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-630361%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3E%E4%BD%A0%E6%98%AF%E5%B0%8D%E7%9A%84%3CA%20href%3D%22https%3A%2F%2Fcommunity.jmp.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F4550%22%20target%3D%22_blank%22%3E%40pmroz%3C%2FA%3E%20%EF%BC%8C%E9%80%99%E5%8F%AA%E6%98%AF%E5%85%A9%E5%80%8B%E8%AA%9E%E5%8F%A5%E7%9A%84%E4%B8%80%E5%80%8B%E4%BE%8B%E5%AD%90%E3%80%82%3CBR%20%2F%3E%E6%9C%89%E6%99%82%E6%88%91%E9%9C%80%E8%A6%81%E5%85%A9%E6%A2%9D%E8%AA%9E%E5%8F%A5%E4%BE%86%E5%89%B5%E5%BB%BA%E5%AD%90%E6%A8%A3%E6%9C%AC%EF%BC%88%E8%87%A8%E6%99%82%E8%A1%A8%EF%BC%89%E6%88%96%E5%AE%9A%E7%BE%A9%E5%87%BD%E6%95%B8%E3%80%82%3CBR%20%2F%3E%E5%9C%A8%E5%A4%A7%E5%A4%9A%E6%95%B8%E6%83%85%E6%B3%81%E4%B8%8B%EF%BC%8C%E6%88%91%E8%A8%AD%E6%B3%95%E4%BD%BF%E7%94%A8%E2%80%9CWith%E2%80%9D%E8%AA%9E%E5%8F%A5%E8%80%8C%E4%B8%8D%E6%98%AF%E8%87%A8%E6%99%82%E8%A1%A8%EF%BC%8C%E4%BD%86%E4%B8%A6%E9%9D%9E%E7%B8%BD%E6%98%AF%E5%A6%82%E6%AD%A4%E3%80%82%E7%89%B9%E5%88%A5%E6%98%AF%E7%95%B6%E4%BD%BF%E7%94%A8%E2%80%9CWith%E2%80%9D%E8%AA%9E%E5%8F%A5%E9%9C%80%E8%A6%81%E9%9D%9E%E5%B8%B8%E8%A4%87%E9%9B%9C%E7%9A%84%E5%AD%90%E6%9F%A5%E8%A9%A2%E6%99%82%EF%BC%8C%E9%80%99%E6%98%AF%E4%B8%8D%E5%85%81%E8%A8%B1%E7%9A%84%E3%80%82%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-630374%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%E5%9B%9E%E5%A4%8D%EF%BC%9ASQL%20-%20%E7%84%A1%E6%B3%95%E5%B0%87%E5%A4%9A%E5%80%8B%E5%91%BD%E4%BB%A4%E6%8F%92%E5%85%A5%E6%BA%96%E5%82%99%E5%A5%BD%E7%9A%84%E8%AA%9E%E5%8F%A5%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-630374%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3E%E9%80%99%E7%A8%AE%E6%96%B9%E6%B3%95%E5%8F%AF%E8%83%BD%E6%9C%89%E5%8A%A9%E6%96%BC%EF%BC%9A%3C%2FP%3E%0A%3CP%3E%E8%AA%BF%E7%94%A8%E5%9F%B7%E8%A1%8C%E8%B6%85%E7%B4%9A%E8%A4%87%E9%9B%9C%E6%9F%A5%E8%A9%A2%E7%9A%84%20PL%2FSQL%20%E9%81%8E%E7%A8%8B%EF%BC%8C%E4%B8%A6%E5%89%B5%E5%BB%BA%E4%B8%80%E5%80%8B%E8%87%A8%E6%99%82%E8%A1%A8%E3%80%82%3C%2FP%3E%0A%3CP%3E%E7%84%B6%E5%BE%8C%E5%BE%9E%E8%87%A8%E6%99%82%E8%A1%A8%E4%B8%AD%E9%81%B8%E6%93%87%20*%E3%80%82%3C%2FP%3E%0A%3CP%3E%E9%80%99%E6%98%AF%E4%B8%80%E4%BA%9B%E5%9F%B7%E8%A1%8C%E9%A1%9E%E4%BC%BC%E6%93%8D%E4%BD%9C%E7%9A%84%E4%BB%A3%E7%A2%BC%EF%BC%9A%3C%2FP%3E%0A%3CPRE%3E%3CCODE%20class%3D%22%20language-jsl%22%3Eevent_sql%20%3D%20evalinsert(%0A%22DECLARE%20%0A%20l_proc_name%20VARCHAR2%20(100)%20%3A%3D%20'Get_Reference_PTs'%3B%20%0A%20l_user_SID%20%20NUMBER%20%20%20%3A%3D%20SYS_CONTEXT('USERENV'%2C%20'SID')%3B%20%0A%20l_user_name%20VARCHAR2%20(30)%20%3A%3D%20UPPER(SYS_CONTEXT('USERENV'%2C%20'OS_USER'))%3B%20%0ABEGIN%20%0A%0A%20%20%20%20INSERT%20INTO%20quality_attrib%20(SID%2C%20OS_USER%2C%20Completion_Time%2C%20Progress_Text)%20%0A%20%20%20%20%20%20%20%20VALUES%20(l_user_SID%2C%20l_user_name%2C%20SYSTIMESTAMP%2C%20'START%20Get_Reference_PTs')%3B%20%20%0A%20%20%20%20%20%20%20%20COMMIT%3B%20%0A%0A%20INSERT%20INTO%20Ref_PT_List%20(SID%2C%20OS_USER%2C%20PT_Name)%20%0A%20(SELECT%20DISTINCT%20l_user_SID%2C%20l_user_name%2C%20mu.pt_name%20%0A%20FROM%20info%20ci%2C%20%0A%20%20%20drug%20cd%2C%20%0A%20%20%20%20%20%20reaction%20cr%2C%20%0A%20%20%20%20%20%20meddra_uta%20mu%2C%20%20%0A%20%20WHERE%20cd.case_id%20%3D%20ci.case_id%20%0A%20%20%20%20AND%20cr.case_id%20%3D%20ci.case_id%20%0A%20%20%20%20AND%20cr.reported_meddra_uta_id%20%3D%20mu.meddra_uta_id%20%0A%20%20%20%20AND%20cd.cdd_generic_id%20%3D%20rd.cdd_generic_id%20%0A%20%20%20%20AND%20ci.report_type_code_id%20%3D%208%20%20%0A%20%20%20%20AND%20rd.calendar_id%20%3D%20%20%5Eone_calendar_id%5E%0A%20%20%20%20AND%20cd.drug_character_code_id%20IN%20(31%2C%2032)%0A%20%5Ept_clause%5E%0A%20%5Edate_clause%5E%0A%20%5Eevent_type_clause%5E%0A%20)%3B%20%0A%20COMMIT%3B%20%0A%0A%20%20%20%20INSERT%20INTO%20quality_attrib%20(SID%2C%20OS_USER%2C%20Completion_Time%2C%20Progress_Text)%20%0A%20%20%20%20%20%20%20%20VALUES%20(l_user_SID%2C%20l_user_name%2C%20SYSTIMESTAMP%2C%20'END%20Get_Reference_PTs')%3B%20%20%0A%20%20%20%20%20%20%20%20COMMIT%3B%20%0A%0AEXCEPTION%20%0A%20%20%20%20WHEN%20OTHERS%20%0A%20%20%20%20%20%20%20%20THEN%20%0A%20%20%20%20%20%20%20%20log_error_message(SQLCODE%2C%20SQLERRM%2C%20l_proc_name)%3B%20%0AEND%3B%20%22)%3B%0A%0AExecute%20SQL(dbc%2C%20event_sql)%3B%0A%0AExecute%20SQL(dbc%2C%20%22select%20*%20from%20ref_pt_list%22)%3B%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-636729%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%E5%9B%9E%E5%A4%8D%EF%BC%9ASQL%20-%20%E7%84%A1%E6%B3%95%E5%B0%87%E5%A4%9A%E5%80%8B%E5%91%BD%E4%BB%A4%E6%8F%92%E5%85%A5%E6%BA%96%E5%82%99%E5%A5%BD%E7%9A%84%E8%AA%9E%E5%8F%A5%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-636729%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3E%E9%9D%9E%E5%B8%B8%E6%84%9F%E8%AC%9D%3CA%20href%3D%22https%3A%2F%2Fcommunity.jmp.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F4550%22%20target%3D%22_blank%22%3E%40pmroz%3C%2FA%3E%3CBR%20%2F%3E%E5%B0%87%E9%80%99%E5%85%A9%E5%80%8B%E8%AA%9E%E5%8F%A5%E6%8B%86%E5%88%86%E7%82%BA%E5%96%AE%E7%8D%A8%E7%9A%84%20Execute%20SQL%20%E8%AA%9E%E5%8F%A5%E6%98%AF%E5%AE%8C%E7%BE%8E%E7%9A%84%E3%80%82%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-629346%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ESQL%20-%20%E7%84%A1%E6%B3%95%E5%B0%87%E5%A4%9A%E5%80%8B%E5%91%BD%E4%BB%A4%E6%8F%92%E5%85%A5%E6%BA%96%E5%82%99%E5%A5%BD%E7%9A%84%E8%AA%9E%E5%8F%A5%E4%B8%AD%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-629346%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3E%E8%A6%AA%E6%84%9B%E7%9A%84%E8%AB%96%E5%A3%87%E6%9C%83%E5%93%A1%EF%BC%8C%3CBR%20%2F%3E%E7%95%B6%E4%BD%BF%E7%94%A8%20jmp%20%E6%9F%A5%E8%A9%A2%20amazon%20redshift%20%E6%99%82%EF%BC%8C%E6%88%91%E9%9C%80%E8%A6%81%E4%B8%80%E8%B5%B7%E7%99%BC%E9%80%81%E5%85%A9%E5%80%8B%E8%AA%9E%E5%8F%A5%EF%BC%8C%E4%BD%86%E7%94%B1%E6%96%BC%E6%9F%90%E7%A8%AE%E5%8E%9F%E5%9B%A0%EF%BC%8C%E6%88%91%E6%94%B6%E5%88%B0%E4%B8%80%E6%A2%9D%E9%8C%AF%E8%AA%A4%E6%B6%88%E6%81%AF%EF%BC%9A%3CBR%20%2F%3E%3CBR%20%2F%3E%E4%B8%8D%E5%B9%B8%E7%9A%84%E6%98%AF%EF%BC%8C%E6%88%91%E7%9A%84%E6%9F%A5%E8%A9%A2%E9%9D%9E%E5%B8%B8%E8%A4%87%E9%9B%9C%EF%BC%8C%E4%B8%A6%E4%B8%94%E4%B8%8D%E8%83%BD%E5%83%85%E4%BD%BF%E7%94%A8%E6%AF%8F%E5%80%8B%E5%96%AE%E7%8D%A8%E8%A1%A8%E7%9A%84%E2%80%9CWhere%E2%80%9D%E8%AA%9E%E5%8F%A5%E3%80%82%3CBR%20%2F%3E%E5%A6%82%E6%9E%9C%E6%9C%89%E4%BA%BA%E7%9F%A5%E9%81%93%E5%A6%82%E4%BD%95%E5%85%8B%E6%9C%8D%E9%80%99%E5%80%8B%E5%95%8F%E9%A1%8C%EF%BC%8C%E9%82%A3%E5%B0%87%E6%9C%83%E5%BE%88%E6%9C%89%E5%B9%AB%E5%8A%A9%E3%80%82%3C%2FP%3E%0A%3CP%3E%E8%AC%9D%E8%AC%9D%20%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ron_horne_0-1683372905784.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22ron_horne_0-1683372905784.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22ron_horne_0-1683372905784.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Fcommunity.jmp.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F52607i74EF26A22BE2365F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ron_horne_0-1683372905784.png%22%20alt%3D%22ron_horne_0-1683372905784.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-629346%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CLINGO-LABEL%3E%E9%AB%98%E7%B4%9A%E7%B5%B1%E8%A8%88%E5%BB%BA%E6%A8%A1%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3E%E6%B6%88%E8%B2%BB%E8%80%85%E5%92%8C%E5%B8%82%E5%A0%B4%E7%A0%94%E7%A9%B6%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3E%E6%95%B8%E6%93%9A%E5%AD%98%E5%8F%96%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3E%E5%AF%A6%E9%A9%97%E8%A8%AD%E8%A8%88%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3E%E5%A4%A7%E8%A6%8F%E6%A8%A1%E5%AE%9A%E5%88%B6%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Choose Language Hide Translation Bar
View Original Published Thread

SQL - 無法將多個命令插入準備好的語句中

ron_horne
Super User (Alumni)

親愛的論壇會員,
當使用 jmp 查詢 amazon redshift 時,我需要一起發送兩個語句,但由於某種原因,我收到一條錯誤消息:

不幸的是,我的查詢非常複雜,並且不能僅使用每個單獨表的“Where”語句。
如果有人知道如何克服這個問題,那將會很有幫助。

謝謝
undefined

 

本文原文为English (US)撰写,为方便起见已翻译过。 当你回复时,它也会被翻译回English (US)。

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

回复:SQL - 無法將多個命令插入準備好的語句

這種方法可能有助於:

調用執行超級複雜查詢的 PL/SQL 過程,並創建一個臨時表。

然後從臨時表中選擇 *。

這是一些執行類似操作的代碼:

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");
6 REPLIES 6
Georg
Level VII

回复:SQL - 無法將多個命令插入準備好的語句

親愛的羅恩,到目前為止我還沒有這樣做,但在這裡你要求對兩個表進行新的 sql 查詢,我想,它無法處理。 您是否嘗試改為執行 sql?

另一種可能性是兩個查詢之間的聯合只有一個。 但這需要以相同順序完全相同的列。

 

本文原文为English (US)撰写,为方便起见已翻译过。 当你回复时,它也会被翻译回English (US)。

ron_horne
Super User (Alumni)

回复:SQL - 無法將多個命令插入準備好的語句

如果我使用以下腳本嘗試它,我會得到同樣的錯誤:

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

該問題可能類似於此討論:
https://community.jmp.com/t5/Discussions/SQL-query-to-ODBC-datasource-SQL-server-with-temporary-tables/td-p/457582

但那裡的解決方案並不完全適用於紅移。

本文原文为English (US)撰写,为方便起见已翻译过。 当你回复时,它也会被翻译回English (US)。

pmroz
Super User

回复:SQL - 無法將多個命令插入準備好的語句

通常,您不能通過一次調用 EXECUTE SQL 來執行兩條 SQL 語句。兩種說法都是一樣的;也許你只是舉個例子?  

為什麼要同時運行兩個 SQL 語句?EXECUTE SQL 為每個 SQL 語句返回一個數據集。

為什麼不只運行兩個 EXECUTE SQL 命令呢?

本文原文为English (US)撰写,为方便起见已翻译过。 当你回复时,它也会被翻译回English (US)。

ron_horne
Super User (Alumni)

回复:SQL - 無法將多個命令插入準備好的語句

你是對的@pmroz ,這只是兩個語句的一個例子。
有時我需要兩條語句來創建子樣本(臨時表)或定義函數。
在大多數情況下,我設法使用“With”語句而不是臨時表,但並非總是如此。特別是當使用“With”語句需要非常複雜的子查詢時,這是不允許的。 .

本文原文为English (US)撰写,为方便起见已翻译过。 当你回复时,它也会被翻译回English (US)。

pmroz
Super User

回复:SQL - 無法將多個命令插入準備好的語句

這種方法可能有助於:

調用執行超級複雜查詢的 PL/SQL 過程,並創建一個臨時表。

然後從臨時表中選擇 *。

這是一些執行類似操作的代碼:

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

本文原文为English (US)撰写,为方便起见已翻译过。 当你回复时,它也会被翻译回English (US)。

ron_horne
Super User (Alumni)

回复:SQL - 無法將多個命令插入準備好的語句

非常感謝@pmroz
將這兩個語句拆分為單獨的 Execute SQL 語句是完美的。

本文原文为English (US)撰写,为方便起见已翻译过。 当你回复时,它也会被翻译回English (US)。