How to JOIN two tables on serperate databases?

Ignition
1

I need help with an SQL Query between two tables in separate databases.

I want to return all rows from PacakageRecord table on our Bizerba database where both the ArticleNumber from our PackageRecord matches the ItemNumber from OrderStartStop table on our Ignition_Historian_MSSQL database.

I noticed the query builder inside ignition doesn't allow joining between databases, or doesn't look like it does. Is there a way to specify where to look for all the information?

here is what I have currently

SELECT PackageRecord.ActualNetWeightValue, PackageRecord.ArticleNumber
FROM PackageRecord
JOIN OrderStartStop ON PackageRecord.ArticleNumber = OrderStartStop.ItemNumber;
2

I suspect that you're going to have to script multiple queries or named queries and script the join.

The part of your question, " both the ArticleNumber" seems to be incomplete as there isn't a second requirement specified.

3

If the databases are on the same server, you should be able to join like so (semi-psuedocode below):

SELECT
    *
FROM Bizerba.<schema>.PacakageRecord pr
INNER JOIN Ignition_Historian_MSSQL.<schema>.OrderStartStop oss
    ON oss.ItemNumber = pr.ArticleNumber
WHERE ...

If they're not on the same server, you can do this is by creating a linked server and then write your query similar to above. We've done this before with SQL Server and it works exactly as you would expect. I also don't ever use the Database Query Browser tool for anything non-trivial, just use an RDBMS if available and it'll give you suggestions, table names, etc.

You should be able to just write a named query once you get the syntax figured out. For most of the Named Queries I create, there is a corresponding stored procedure in the database that I then just EXEC from the Named Query, but this depends on your requirements and setup (this is more of a description, not a prescription).

4

You might find the various join() functions in my Integration Toolkit helpful.