Error Opening SELECT in Data Stream Out Tool

[Armand]

Hello Everyone,

I have an error with the Data Stream Out Tool when trying to bring the In-Database workflow out to Alteryx Designer. Can someone help me please to understand the Error and how to fix it ? I attached here the screenshot of the error from my computer.

Thank you in advance 

Armand

[OllieClarke]

Hi @Armand 

The In-DB tool errors are a little different to normal tools. They only arise at a point where the query is sent - this could be an output/stream out or a browse. 
In your case something has gone wrong before the stream out, but the error is showing at the point that alteryx realises. If I were you, I'd select all the in-db tools, and press ctrl+shift+b to add a browse tool to all of them. Then run your workflow and see which browse tools error - that should help identify where the cause of the error is.

Hope that helps,

Ollie

[apathetichell]

you should post the full error message too - cut and paste the exact error message into word or notepad - not much any one can tell from that snippet.

[Armand]

Hi @OllieClarke 

Thanks so much for your reply and your trick. I am trying to find out where the cause of the error is. I identify that the errors are located in some browse tools which I put in the attached .txt file here and the screenshot of the workflow. I will appreciate any suggestion to fix the errors. Thanks

Best regards

Armand

[Armand]

Hello @apathetichell 

Yes, you are right. I put the queries from the browse tools where the errors are located into a .txt file and the screenshot of the workflow for each of the identified browse tool that I attached into my reply to @OllieClarke . I will appreciate any suggestion to fix the problem. Thanks in advance.


Best regards
Armand

[OllieClarke · Accepted solution]

Hey @Armand 

Your error is in the in-db filter tool. The In-DB tools require you to write formulae in the syntax/language of the database to which you're connecting

DATETIMEDIFF(DateTimeNow(),[TSMAJ],'days') > 90

This is how you would do the filter in Alteryx.

If you're connecting to a Microsoft SQL Server, this would have to be:

DATEDIFF(day,"TSMAJ",GETDATE()) > 90

 This is why your workflow was erroring. 

The syntax/functions will change depending on your database and the language it uses.

Hope that helps,

Ollie

[Armand]

Hi @OllieClarke,

Thanks again for your suggestion. Indeed I am using Hive. I tried your solution using the formula bellow but it doesn't solve my problem. 

DATEDIFF(current_date(), [TSMAJ]) > 90

Maybe I mistaken somewhere in the formula. Do you find something wrong ? I also attached the screenshot of the workflow.

[apathetichell · Accepted solution]

DATEDIFF(current_date(),"TSMAJ")>90

(use '90' if 90 isn't a number.

But I believe HIVE uses CURRENT_DATE - not current_date() for the current date. These are not Alteryx issues - basically however you'd construct this Query in your usual SQL you should do that in the Alteryx In-DB tools. You can parameterize using "FIELDNAME" - not  [FIELDNAME] like you would on canvas.

[Armand]

Hello @apathetichell,

Thanks for your help. Finally, after following your tips, the formula works. The issue is now fixed. 

Best regards

Armand