In-Database Conditional Join Anyone?

[BobbyD]

Hi Experts. I'm new to Alteryx but surely others have asked this question in the past?

I need to perform an in-database left outer join conditional on a date field being within a range e.g.

Where A.Date >=B.DateFrom and <= B.DateTo

I've been using the "Join In-Db" tool which gets me part way there, but cant find a sensible way to do this.  Cartesian joins and filters are not the answer for me.

Any suggestions?  Coming from a SAS and SQL background, I cant understand how this functionality is not obvious..

[alexnajm · Accepted solution]

Wouldn't this condition be added in the Filter In-DB tool? Or prior in the Connect In-DB tool?

[Raj]

I agree with @alexnajm that this is a combination of Filter and then Join Tool.

[BobbyD]

Thanks for the quick reply Alex. I had to move the data in-db in order to use the only tool I could find to do a previous left outer join and I'm now joining to data that is sourced in-db.  I'll take a look at the Filter In-Db tool to see if that helps..

[cjaneczko]

You can do this all with the In-DB tool without a filter or join tool. You can structure the SQL statement in the IN-DB tool to JOIN the two tables as well as use the WHERE clause you proposed in your question. 

[BobbyD]

That sounds great, I'm happy to code the query if needed, which In-DB tool are you referring to?

[BobbyD · Accepted solution]

Ah, I see what you mean, I can code up the query using the Connect DB tool.  That feels like an omission by Alteryx but I'll use it...

[cjaneczko]

Correct, its the Connect IN-DB. I have a few workflows that are joined via the one tool using a similar WHERE statement related to dates. All of the SQL to join and "filter" are done right in the Connect IN-DB. If you weren't savvy in SQL you can also use the Visual Query Builder to setup the joins and filters. 

[BobbyD]

Hi all. back again I'm afraid..

The Connect In-DB tool works great for multiple left outer joins but as soon as I include a <= conditional join, the tool fails with an "Error parsing XML" error.

I'm using an ODBC DSN Connection - is this appropriate?

The full query including <= conditional join works perfectly directly in SQL Server