Cannot drop Netezza table

[Sanny651]

I am trying to have Alteryx drop a table after the workflow is done.  If I run the following code in Aginity, it works fine:

DROP TABLE TEST.TEST.TEST_TABLE

I have tried putting that code in the "Post-SQL", and while the workflow runs without errors, it doesn't actually drop the table.  I am using the same credentials in both software platforms.  I've also tried using the "Connect In-DB" tool and used that as the only SQL, and there too it runs without errors, but fails to actually drop the table. 

Any suggestions?  I feel it's something basic that I'm overlooking, but I can't seem to figure it out.

Thank you in advance!

[Thableaus]

Hey @Sanny651

What is the DB connection you're working on?

I realize you're repeating the dbo twice on your statement "DROP TABLE TEST.TEST.TEST_TABLE". Shouldn't it be TEST.TEST_TABLE?

Could you please share a printscreen of your pre-sql and post-sql statement?

Cheers,

[Sanny651]

Hey @Thableaus

I tried to do this, but to no avail.  My thought is that since I have no export, I could use the In-DB tool.  It runs and does not give an error of any kind, but it doesn't drop the table.  I also tried using the regular input tool via the post-sql statement.  There too, it runs without error but doesn't drop the table.

[Thableaus]

@Sanny651

Drop statements won't work with Connect In-DB Tools, as they're meant to accept SELECT statements.

With an Input Tool as your Post-SQL statement, it should work. But the table must exist so the command can be executed. It depends how you're workflow is set up, maybe you're running this command before creating the table, we'd have to investigate to check deeper this issue.

Cheers,

[Sanny651]

@Thableaus

I tried that as well and it doesn't work.  It runs without errors, but the table is there before and after the workflow runs and does not actually drop.  For what it's worth, I'm running version 2018.1.4.44311. 

[Thableaus]

@Sanny651

Is this table created with your workflow?

If you share some of your workflow and the role that this TEST TABLE plays within it, we could understand better what's going on.

[Sanny651 · Accepted solution]

@Thableaus

PM'd you.  I will post a workflow in a bit pending your response.  Thanks mate!

[Sanny651]

Here's a mockup of the workflow.  refined, but it still doesn't work.

[Thableaus]

I've made some changes.

Let me know if it works!

Cheers,

[Sanny651]

@Thableaus

Unfortunately, this errors out.  If I remove the "WAITFOR DELAY '00:00:05'" text, and click Test Query it does not throw an error.  But, if I run it without that, it still doesn't drop the table.

I tried moving the delay portion of the code to the post-sql statement.  Here too, it runs without errors but does not drop the table.

For kicks, I tried dropping a different table (not the one I'm querying) and again, it doesn't throw an error but it doesn't drop the table.