Embedded Dynamic SQL to Execute an Update Statement

Database

Embedded Dynamic SQL to Execute an Update Statement

Hallo,

I am trying to randomly segment a table into groups where the number of groups can change, as well as the sample percentage of each group.   The number of samples and the distribution is coming from an Excel spreadsheet.

My hope was to execute dynamically embedded SQL passed in from the spreadsheet to update a field on the original table with the sampleid returned using the sample clause.

I am under the impression that I have to PREPARE the SQL statement and then EXECUTE it.   When I try to follow examples I have found on the forum and the web in general I am returning a 5882 error.   Below is an example of the code I am trying to implement...

SET v_sql = 'UPDATE EDWCRMTMP.UAC_EX_1321986_i FROM (SELECT E.acct, E.bill_id, SAMPLEID AS SID FROM EDWCRMTMP.UAC_EX_1321986_i E SAMPLE .5,.3,.2) TEMP SET df_SampleGroup = TEMP.SID WHERE TEMP.ACCT_ID = EDWCRMTMP.UAC_EX_1321986_i.ACCT AND TEMP._ID = EDWCRMTMP.UAC_EX_1321986_i.BILL_SYS_GEO_ID';

PREPARE s_sql FROM v_sql;

EXECUTE s_sql;

I appreciate any help the forum can provide.

Thanks,

Howard

Tags (1)
3 REPLIES
Junior Contributor

Re: Embedded Dynamic SQL to Execute an Update Statement

Hi Howard,

within an Excel sheet there's no need to prepare/execute, this is for Embedded SQL/Stored Procedures only.

So running the update directly should work.

Re: Embedded Dynamic SQL to Execute an Update Statement

Hi Dieter.

I am actually passing this to TeraData through a Unica flowchart.   It works fine if I pass a static query through to TeraData.

The excel front end is what the business is using to enter how they would like to distribute the leads for a given campaign.   I intend to have the distribution numbers or a SQL string built using those numbers stored in a table I can make available to the flowchart when it runs.   As I am typing this it hits me that I need to try another Unica mechanism that may allow me to pass in the SQL as a literal string that TeraData won't balk at.   Thanks for make me look at an easier way to potentially make this happen!

With that said, were you referring to executing the SQL by opening a connection in Excel using VBA?   I haven't tried to connect to our TeraData systems here through excel and am not sure if I have that ability but that may come in handy.   Is there a specialized connector or would I just use ODBC?

Vielen Dank!   Ich habe deinem posts mit intresse gevolged.  

Sorry if I butchered the German.   I appreciate all of the feedback that you (and the other contributors) have given the community and look forward to learning more about TeraData in the future.

Best regards,

Howard

Junior Contributor

Re: Embedded Dynamic SQL to Execute an Update Statement