SQL Statement before "Write Data In - DB"

[cito]

Hello people,

Is it possible to nest the SQL code before I write the data into DB? (picture below)

The code looks like:

with cte as
 (
     select
         row_number() over 
         (
             partition by 
                 nbr 
             order by 
                 nbr
                 , 
                 (
                     case 
                         when country=country_active then 1 
                         when country=country_inactive then 2
                         else 3 
                     end
                 )
                 ,
                 (
                     case
                         when expiration_date_active >= expiration_date_inactive
                             then expiration_date_active
                             else expiration_date_inactive
                     end
                 ) desc
         ) as rn
         , *
     from
         [Sheet1$]
 )
 select * from cte
 where rn = 1
 order by nbr

Thank you in advance.

[CharlieS]

Hi @cito 

Is there a reason why you're using Data Stream In+Write Data In-DB instead of an Output tool? The Output tool can write to a database, plus it has an option for a pre SQL statement. 

[cito]

Hi @CharlieS,

I can change the output tool.

So, if I change it to the Output data, do I need to write the SQL code in "Pre" or "Post"  Create SQL Statement?

Thanks.

[cito]

So, I added the "Pre" SQL code but nothing happens.

Any idea what's happening or I have done something wrong?

[CharlieS]

That depends on the function of the statement, could you elaborate on the purpose?

It appears to be joining an active flag to the data? If so, it might be easier to resolve this in the workflow and join that information ahead of any db interaction so there's only the output to write.

[cito]

I will try to explain what is my goal here.

This would be a workflow from alteryx:

Now, my final output data should come from the code below:

with cte as
 (
     select
         row_number() over 
         (
             partition by 
                 nbr 
             order by 
                 nbr
                 , 
                 (
                     case 
                         when country=country_active then 1 
                         when country=country_inactive then 2
                         else 3 
                     end
                 )
                 ,
                 (
                     case
                         when expiration_date_active >= expiration_date_inactive
                             then expiration_date_active
                             else expiration_date_inactive
                     end
                 ) desc
         ) as rn
         , *
     
     from
         [Sheet1$]
 )
 select * into final_table
 from cte
 where rn = 1
 order by nbr

I put this code in "Post Create SQL Statement" but my final output table hasn't passed through this SQL code as I get wrong results. When I run this SQL code in Microsoft SQL Manager I get good results.

So, my question is,  where I need to put the SQL statement in alteryx in order to write these results?  

[cito]

"Post Create SQL Statement" has started working 😄

But now, I am not able to run DROP, TRUNCATE, DELETE table in the "Pre Create SQL Statement" since I have "INTO" clause in the "POST" statement:

             ) desc
         ) as rn
         , *
     
     from
         [Sheet1$]
 )
 select * into final_table
 from cte
 where rn = 1
 order by nbr

When I run the workflow I need to drop the table in the "Pre Create SQL Statement" step, but it doesn't work.

Don't know what is the reason of that.

[cito · Accepted solution]

Finally, I found the solution.

These are the steps that I did:

1. I use "Output Data" to write the data in Microsoft SQL database.

2. After this I had change settings in Output data configuration again:

and the "Pre" and the "Post Create SQL Statemen" works fine now.

[Ab3111]

I have 2 questions for you, if you could please help:

1.Is there a way to write a pre/post sql for the data in stream tool? I want to create a temporary table inside snowflake by passing the data from Alteryx, but I get the error:-

Error: Data Stream In (9): Error creating table "AYX21102779805c223f21abde4d5a31c7d1342ea2": Cannot perform CREATE TABLE. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.

Any inputs will be helpful.

2.Also, can I encrypt my columns inside alteryx and decrypt them back in snowflake? Is there any way of doing it?