cs.jo in()
Description:
Attach foreign key style join with a record sequence to the cursor and return the original cursor.
Syntax:
cs.join(C:.,T:K,x:F,…; …;…)
Note:
The function attaches a computation to cursor cs, which matches field C,… in cursor cs with the key of parameter T, on which expression x is computed, and adds an F field that is assigned by results of x to cs, and return the original cursor. It supports the multicursor.
K can be omitted or represented by #. When omitted, K is T’s key by default; when written as #, K is the sequence number of a record of table T, which means foreign key numberization. Simply put, primary key values of the dimension table are natural numbers starting from 1, which are row numbers corresponding to table records. In this case, we can directly locate dimension table records according to key values by row numbers. This helps speed up association with the dimension table and increase performance.
If there is an F field in cs, just modify the existing field of ch. Use the latest time calculated through now() when time key value is not specified.
This is a delayed function.
Parameter:
|
cs |
A cursor/A multicursor |
|
C |
Cursor cs’s foreign key; separate multiple fields in a composite key with the colon |
|
T |
A table sequence/A record sequence |
|
K |
T’s key |
|
x |
T’s field expression |
|
Field name in expression x. |
Option:
|
@i |
Delete a record with a non-matching foreign key value; by default, a non-matching record will be represented by null |
|
@o(Fi; C:.,T:K,x:F,…; …;…) |
Use the original record as the value of Fi field to generate a new record |
|
@d |
If parameters x:F are absent, only perform a filtering over the cursor by deleting its records where the foreign key is matched |
|
@m |
Enable a merge join when cs is ordered by C and T is ordered by K |
Return value:
Cursor
Example:
When parameter K is absent:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 10 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor, whose data is as follows:
|
|
2 |
=connect("demo").query("SELECT top 40 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose key is STATEID:
|
|
3 |
=A1.join(STATEID,A2,CAPITAL) |
Attach a computation to cursor A1, which creates association between CITIES table and STATECAPITAL table through the foreign key specified by parameter K, whose value is by default the latter’s key, STATEID, and adds field CAPITAL in STATECAPITAL to the cursor, and return the original cursor. |
|
4 |
=A1.fetch() |
Fetch data from cursor A1 where A3’s computation is executed (it would be better to fetch data in batches when there is a huge amount of data):
|
When parameter K is written as #:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 10 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor whose data is as follows:
|
|
2 |
=connect("demo").query("SELECT top 40 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose key is STATEID:
|
|
3 |
=A1.join(STATEID,A2:#,CAPITAL) |
Attach a computation to cursor A1 and return the original cursor; as STATEID field values are natural numbers beginning from 1 that strictly correspond to record numbers in STATECAPITAL table, we can write parameter K as #, which means sequence numbers in the table can be directly used to increase the computing efficiency. |
|
4 |
=A1.fetch() |
Fetch data from cursor A1 where A3’s computation is executed:
|
When @i option works:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 10 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor whose data is as follows:
|
|
2 |
=connect("demo").query("SELECT top 40 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose key is STATEID:
|
|
3 |
=A1.join@i(STATEID,A2,CAPITAL) |
Attach a computation to cursor A1, which, with @i option, deletes the whole record from the cursor if the foreign key does not match – by default the corresponding value is recorded as null, and return the original cursor. |
|
4 |
=A1.fetch() |
Fetch data from cursor A1 where A3’s computation is executed:
|
When @i option works and parameters x:F are absent:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 10 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor whose data is as follows:
|
|
2 |
=connect("demo").query("SELECT top 40 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose key is STATEID:
|
|
3 |
=A1.join@i(STATEID,A2) |
Attach a computation to cursor A1, which, with @i option, performs a filtering operation only on CITIES table when parameters x:F are absent , and return the original cursor. |
|
4 |
=A1.fetch() |
Fetch data from cursor A1 where A3’s computation is executed:
|
When @d works:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 10 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor whose data is as follows:
|
|
2 |
=connect("demo").query("SELECT top 40 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose key is STATEID:
|
|
3 |
=A1.join@d(STATEID,A2) |
Attach a computation to cursor A1, which, with @d option, deletes the whole record in the cursor that matches the foreign key when parameters x:F are absent to achieve a filtering operation on CITIES table, and return the original cursor. |
|
4 |
=A1.fetch() |
Fetch data from cursor A1 where A3’s computation is executed:
|
When value of parameter x does not exist in T:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 10 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor whose data is as follows:
|
|
2 |
=connect("demo").query("SELECT top 40 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose key is STATEID:
|
|
3 |
=A1.join(STATEID,A2,abc) |
Attach a computaton to cursor A1, which records the corresponding field as null if parameter x value does not exist in A2, and return the original cursor. |
|
4 |
=A1.fetch() |
Fetch data from cursor A1 where A3’s computation is executed:
|
When @o works:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 10 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor whose data is as follows:
|
|
2 |
=connect("demo").query("SELECT top 40 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose key is STATEID:
|
|
3 |
=A1.join@o(cities;STATEID,A2,CAPITAL) |
Attach a computation to cursor A1, which, with @o option, generates new records by making the cursor’s records values of cities field, and return the original A1. |
|
4 |
=A1.fetch() |
Fetch data from cursor A1 where A3’s computation is executed:
|
Modify the existing field when F field already exists in cs:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT top 10 CID,NAME,POPULATION,STATEID FROM CITIES") |
Return a cursor whose data is as follows:
|
|
2 |
=connect("demo").query("SELECT top 40 * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence whose key is STATEID:
|
|
3 |
=A1.join(STATEID,A2,CAPITAL:NAME) |
Attach a computation to cursor A1, which modifies the existing field as NAME field already exists in CITIES table, and return the original cursor. |
|
4 |
=A1.fetch() |
Fetch data from cursor A1 where A3’s computation is executed:
|
Related function: