Tables Join in Sage X3 adonix with Link instruction

The instruction that Sage X3 makes available to make a join between two tables is Link.

Here we see how to create a join between the TAB1 table and the TAB2 table;
TAB1 is the table with more detail,
The link is based on the TAB2 table.

Key index is KEY0, it’s made up of 2 fields which in this case correspond to the fields FIELD1TAB2 and FIELD2TAB2 of TAB2:

Link [F:TAB1] With [F:TAB2]KEY0~=FIELD1TAB2;FIELD2TAB2 As [YJOI]
& Where [F:TAB1]FIELD1=VALUE1 and [F:TAB1]FIELD2=VALUE2
& Order By [F:TAB1]FIELD1 Asc;[F:TAB1]FIELD2 Asc

Attention: the join statement must be written all on one line, or alternatively you can use the character & as seen here.

The keyword that defines the link between the two tables is With that corresponds with “ON” in an SQL statement:

SELECT * FROM TABA JOIN TABB JOIN ON TABA.FIELDA = TABB.FIELDB

To express a condition in FULL JOIN, use the ~= operator (tilde equal)
To express a condition in LEFT JOIN use the = operator (equal)

Before joining you must declare / open the tables you want to use,
and it is possible to declare the same table twice, with different abbreviations.

For example, the detail table is TABDETAIL and the and the table to be joined is TAB2:
this is the syntax (as already said everything goes on the same line, in brackets the optional parts):

LINK TABDETAIL
With CONDITION_JOIN[,CONDITION_JOIN]...[,CONDITION_JOIN]
As [JOIN_NAME]
[Where CONDITION_WHERE]
[Order By EXPRESSION_ORDER]

CONDITION_JOIN

[TAB2]KEY_NAME ~= EXPRESSION_LIST                #used = with the LEFT JOIN
[TAB2]KEY_NAME(INDEX_VALUE) ~= EXPRESSION_LIST   #used = with the LEFT JOIN

There must be at least one CONDITION_JOIN and maximum 11.
Up to 12 tables can be put in JOIN.
KEY_NAME is the name of a table index, so to speak those defined in the “Index” tab in the GESATB function.
As we can see, the join condition is conditioned to the use of fields that belong to at least one table index,
therefore you can not set a join on any field.

JOIN_NAME
In practice we give a name to the join class thus created, which can be used with a statement;
for example a FOR statment:

For [JOIN_NAME]
...
Next

Then any WHERE conditions and any sorting with ORDER BY must be added.

Let’s see some examples.

Inner join example

We try to recover the tax code of customers whose code starts with ‘AAA’.
We retrieve the tax code from the CRN field of the BPs table.
Not all BPs are customers, so we set up an inner join to get only records that match the BPCUSTOMER table.

If !clalev([F:BPR]) : Local File BPARTNER [BPR] : Endif
If !clalev([F:BPC]) : Local File BPCUSTOMER [BPC] : Endif  

Link [F:BPR] With [F:BPC]BPC0~=BPRNUM As [JOIN]
& Where left$([F:BPR]BPRNUM,5)='A0001'

For [JOIN]
Infbox [F:BPR]BPRNUM-'-'-[F:BPR]CRN
Next

If in the join condition remove the tilde ~ all BPs will be displayed, even those who are not customers.

Left join example with link one-to-many

We look for all the articles with their articles-site.
An article can have multiple matches in the table of articles-site (one to many relationship).
However, we also want to display all the articles that do not have any site articles.

If !clalev([F:ITM]) : Local File ITMMASTER [ITM] : Endif
If !clalev([F:ITF]) : Local File ITMFACILIT [ITF] : Endif

Link [F:ITM] With [F:ITF]ITF0(1)=ITMREF As [JOIN]
& Where left$([F:ITM]ITMREF,7)='ABCDFEG'

For [JOIN]
  Infbox [F:ITM]ITMREF-[F:ITF]STOFCY
Next

In this way, an article without an article-site will still be displayed.

Note that in this case we used the syntax [TAB2] KEY_NAME (INDEX_VALUE):
in fact we have set the join only on the first field of the ITF0 key which is as follows:

 ITF0 = & gt; ITMREF STOFCY +

The problem is that the link statement requires the use of a key in the join condition,
and you have to make do with the keys present or create a new one.

Per trovare molte alte informazioni sull’istruzione Link di Sage X3
To find more information on the Sage X3 Link statement click here.