Skip to content

Feature: Joining Databases in PowerSearch and Report Desk

← Elliott Version 8.6
Release Date: 6/13/2024
Elliott Versions: 8.6 and Higher

Background

PowerSearch and Report Desk provide powerful abilities for our users to easily access and customize fast, powerful, and complex searches and reports.  The default database for these searches and reports is the database for the current company.  Before this change, it was only possible to JOIN tables in a single Company database (or in the Root database only).  But there are some tables in the Root database, for example, that logically could be joined to the tables in a Company database.  For example, the Root table, SYCNTRY, contains a list of countries and their descriptions, etc., that could be used when displaying a customer or vendor to provide a standard description for the country code on file.

Joining the Root Database to a Company Database

We have developed a report called the Customer Country Exception List that lists the Customer records whose country code does not match any record in the SYCNTRY table in the Root database.  You can run this report by going to System Utilities / System Files Setup and selecting the Maintenance / Country File menu item, and then the List menu item:

Select the highlighted report above to begin Report Desk:


If you run the report, you will see a report that looks like this:


So how were we able to join the Root table SYCNTRY to the Company table ARCUSFIL?  To see how this is done, press the Modify... button) in a red box above.  This will bring up the Designer for this report:


Notice the first red box.  It is the SYCNTRY table in the Root database and all its columns.  But, how did it get there?  The secret sauce is in the SQL Template for this report.  Press the Template... button in red above:


The table join that crosses into another database has "ROOT." in front of the table name in the right-most red box above.  That tells Report Desk (or PowerSearch) to look for the table in the database referred to as "ROOT."  During the creation of the SQL SELECT statement, The variable "ROOT" is replaced by the right side of ROOT=<database> entry in the [Database] section of EL860.Cfg.

With the database section like this:

[Database]
SE-Server=DB1
Root=ELI86ROOT
Company01-DSN=ELI86DATA
Company90-DSN=ELI86DATA90

The SELECT statement will be transformed to this:

SELECT CUS_NO, CUS_NAME, CUS_STREET1, CUS_STREET2,
      CUS_CITY, CUS_ST, CUS_ZIP, CUS_COUNTRY
  FROM ARCUSFIL
  LEFT JOIN ELI86ROOT.SYCNTRY ON SYCNTRY.SYCNTRY_SHORT_DESC = UPPER(CUS_COUNTRY)
WHERE CUS_COUNTRY <> ' '
  AND SYCNTRY.SYCNTRY_SHORT_DESC IS NULL
ORDER BY ARCUSFIL.CUS_NO ASC

Note that "ROOT." was changed to "ELI86ROOT.".  ELI86ROOT is the name of the Root database in the PSQL Control Center.

Potential Future: Joining Multiple Company Databases

Note: This capability is documented, but has not been implemented at this time.

To JOIN to a company that is different from the current company, use a database variable in the form of "Companynn," where nn is a 2-digit company number.  For example, when running from the Company 01, add a JOIN statement like this to access Company90 tables:

LEFT JOIN Company90.IMITMFIL ON Company90.IMITMFIL.ITEM_NO = CPORDLIN.LINE_IMT_ITEM_NO

Database Variables
The following database variables will get resolved from the entries in the [Database] section of EL860.Cfg:
  • ROOT. -- This gets replaced by the ROOT= value..
  • COMPANYnn. -- This gets replaced by the corresponding COMPANYnn-DSN= value.

Programs: EL860ES, EL860RD, ELRWSupport
JEG




Feedback and Knowledge Base

(thinking…)