How to reference another table in In-DB workflow formula?

[skyfire165]

I have a table from SQL in-DB coming in and I want to create a new field with a formula. However in this formula, I am referencing another table and I do not know how to do that using in-DB alteryx tools. Here is an example of the SQL query I am trying to convert to Alteryx:

select 

case when (Select code from TableA a where a.id = b.id) = "AAA") = 'A'

         when (Select code from TableC c where c.id = b.id) = "CCC") = 'C'

from TableB b

This is a very simple example and might not make 100% sense, but the point is I'm not sure how to reference things from another table like TableA and TableC when writing a formula for a new field in TableB.

Thanks

[LordNeilLord]

Hey @skyfire165 

InDB uses the native language of the DB you are connecting to, so you need to write the formula as if you were doing in a SQL query

[skyfire165]

But the problem is, the Connect In-DB tool only allows me to connect to one table in the database (i think?). If I use this to connect to tableB and I want to add a new field (using Formula In-DB?)  which references tableA and tableC from this database, I don't know how to do this since I believe the Alteryx workflow is only connected to tableB and has no info of tableA and tableC.

[JoeS]

You will just need to refer to the other tables using the full location. 

On my SQL server, I just add this to the formula and it works.

case 
when exists (Select * from [Alteryx].[dbo].[Transactions_UK] a where a.Customer_ID = [Customer ID])  then 'Match'
else 'NonMatch'
end

[skyfire165]

What do you mean the full location? Do you mean using the Connect in-DB tool to send in other tables rather than just one table? Because I am not sure how to do that or if that is possible, but it would make things easier.

Thanks

[JoeS · Accepted solution]

Sorry, what I meant is making sure that the table name isn't referred to on it's own in the formula. So to have the database, schema and table name. 

Like my example 

[Alteryx].[dbo].[Transactions_UK]

You don't need to do anything with other Connect In-DB tools, you can just refer to it in the formula tool. It's just the case statement that goes in there though, you don't need the select, as that's handle by Alteryx for you.

[skyfire165]

Hi, so I tried to use your expression in the in-DB formula tool and I get this error:

"Could not find server 'Alteryx' in sys.servers. Verify that the correct server name was specified."

Is the Alteryx and dbo supposed to be the name of the database servers i'm working on?

[skyfire165]

It looks like your solution is working now that I replaced it with my server names! Thanks a lot

[JoeS]

@skyfire165 

Yeah, sorry it was probably a bit misleading that I called my database "Alteryx" in SQL.

Glad you managed to get it working for you now!