Joins & Views

 

Joins

 

There are four types of joins

·       Inner Join

·       Outer Join 

·       Exists Join

·       Notexists Join

 

Inner Join: Inner Join will return records from both Outer table and Inner table, for only the records which are available in Inner table. Inner Join will also return duplicate records.

 

Outer Join: Outer Join will return all the records from both outer table and Inner table. Outer Join will also return duplicate records.

 

Exists Join: Exists Join will return records only from Outer table which are available in Inner Table. It will not return any duplicate records. 

 

NotExists Join: NotExists Join will return records only from Outer table which are not available in Inner Table. It will not return any duplicate records.

 

We can get the data/or the result set based on the criteria from multiple tables

 

Inner join - Will fetch all the matching rows between the parent table/master table and the related table.

static void InnerJoin(Args _args)
{

ATTCustTable  custTable;

ATTCustTrans  custTrans;

while select custTable
join custTrans where custTrans.CustId == custTable.CustId
{
        info(custTable.CustId);
        info(strfmt('%1',custTrans.AmountMST));
}

}

 

 

Exist join - Will only help to retrieve the information of master table, the search engine will only check whether the record exists or not in the secondary table, if a match is found it will break and goes to the next record for fetching.

Example: Give me the list of customers who have done the transactions

static void EXISTSJoin(Args _args)
{

ATTCustTable  custTable;

ATTCustTrans  custTrans;

while select custTable
exists join custTrans where custTrans.CustId == custTable.CustId
{
       info(custTable.CustId);

       info(strfmt('%1',custTrans.AmountMST)); // this cannot be retrieved and the value will be 0 as we cannot get the info of the secondary table

 }

}

 

Not exist join - Will only help to retrieve the information of master table, the search engine will only check whether the record exists or not in the secondary table, if a match is NOT found it will break and goes to the next record for fetching.

Example : Give me the list of customers who have not done the transactions

Static void NotJoin(Args _args)
{

ATTCustTable  custTable;

ATTCustTrans  custTrans;

while select custTable
notexists join custTrans where custTrans.CustId == custTable.CustId
{

         info(custTable.CustId);

         info(strfmt('%1',custTrans.AmountMST)); // this cannot be retrieved and the value will be 0 as we cannot get the info of the secondary table

    }
}

 

Outer join - Will only help to retrieve the information of master table and the related table though a match is not found.

Example : Give me the list of customers who have done or not done the transactions

 

static void OuterJoin(Args _args)
{

ATTSCustTablecustTable;

ATTCustTranscustTrans;

while select custTable
outer join custTrans where custTrans.CustId == custTable.CustId
{

      Info(custTable.CustId);

      info(strfmt('%1',custTrans.AmountMST));
}

    }

 

Views

 

Views are read only

Views will help to see the data from multiple table fields in a single window

We cannot perform any DML operations on view [update, delete, insert], only select query will get executed automatically when you open the view

Views are used on reports and can also be used on forms, we cannot create any index or delete actions on a view

We can never store the data in the view [ax OR FROM BACKEND], because only select query will be triggered while opening the view

 

Example:

Create New view, Name it as "CustTableView" and label as "Customer and transactions view"

Expand the view and you will find important node called 'MetaData", In the metadata - we need to add all the tables in the datasources node

Drag and drop ATTCustTable to the datasource node.

Expand the newly created datasource node and you will find one more datasource node -

Drag and drop ATTCustTrans table under this.

 

Right click on the newly created child datasource and set the relations property to "Yes"

Go to fields node and drag some fields from first datasource and some fields from child/second data datasource

 

Right click on open the view

you will find that all the linked records will be displayed in the view with no DML operations

The record id in the view will be always the parent datasource recid and recversion

will be always 0

 

You can use this view on the form.

Create a new form and add this view as a datasource , Pull some fields to the grid from the datasource and you will notice that all DML operations are disabled