Scriptella: Simulating Query Inner/Outer Join in ETL script

Question

I have a requirement where my two tables are located into two different databases.

So essentially I cannot write join query. Now the question is how can I write a ETL file which essentially helps me simulating the inner join in scriptella.

Order table
{OrderID, CustomerID, OrderDate}
Customer Table
{CustomerID, CustomerName}

Would like to simulate following query in scriptella:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID

Thanks, Deepak

Answer 1

You basically need 2 nested queries to emulate inner joins. First query (Q1) selects all rows from one table. For each found row from Q1 a second query (Q2) is executed. The results are sent to the inner script element inside Q2.

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
    <!-- db1 has Customers table -->
    <connection id="db1" url="jdbc:database1" user="user1" password="pwd1" classpath="external.jar"/>

    <!-- db2 has Orders table -->
    <connection id="db2" url="jdbc:database2" user="user2" password="pwd2" classpath="somedriver.jar"/>

    <!-- just for logging -->
    <connection id="log" driver="text"/>

    <query connection-id="db1">
         <!-- SELECT CustomerID/name for all rows from Customer -->
         SELECT CustomerID, CustomerName from Customer

         <!-- For each selected customer row, select matching orders -->
         <query connection-id="db2">
             SELECT OrderID, OrderDate FROM Orders WHERE CustomerID=?CustomerID
             <!-- Log each match. You can also make INSERT to any DB here -->
             <script connection-id="log">
                  Row $rownum: Orders.OrderID=$OrderID, Customers.CustomerName=$CustomerName, Orders.OrderDate=$OrderDate
             </script>
        </query>
    </query>
<etl>