0

incorta joins

hi all,

I have a small doubt on joins, what are a different type of joins have in incorta, by default which joins does it support, by default we use = means which is it considering,
why do we have <,>,<= >=  symbols in join conditions, the importance of these symbols 

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi venkatRao -- the "=" join in Incorta is most commonly used and behaves like a LEFT OUTER join.  Different "SQL" join behaviors like INNER and RIGHT OUTER can be accomplished in Incorta by the way filters are applied after data is brought into Incorta (eg. https://community.incorta.com/t/631psf/how-to-do-inner-join-in-incorta).  The other join conditions listed above are seldom used but can be helpful in certain situations.  They are mostly used when there are multiple join conditions between two tables.  For example, the screenshot attached ensures that the join to the Customer table gets the right "time slice" for customer records that might be slowly changing.

    Like
  • Incorta also provides the other variations of joins:

    1.  You can provide a filter on the parent table
    This is useful when the parent table is a table with multiple data sets and only a subset is applicable in the join, but the key used in the join may not be unique across sets.
    2. You can use range join as a second criteria
    For example, the parent table has a FROM and a TO column and the child table value can find a matching record from the parent with the range.  If the parent is a TYPE2 dimension table with both EFF FROM and EFF TO dates and the child table is a transaction table with a transaction date that can be used to find the AS WAS image of the dimension.
    3. FIRST VERSION or LAST VERSION from parent as a join filter
    Again the join key may not be uniquely identify a record from the parent table but you would like to join to the latest version of the matched record assuming that there is a column to identify the sort criteria.
    This is a special case of #1
    4.  Use > or < criteria to find a nearest record
    Again the join key may not be uniquely identify a record from the parent table.  Here the parent table may have a EEF FROM date to indicate when the record becomes effective and the child table has a transaction date.  We can specify the PARENT.EFF_FROM < CHILD.TRX_DATE to find the record with the greatest (latest) EFF_FROM  date as of when the transaction occurred.  

    In addition, joins can be applied on a formula column that does not exist in the source table if the formula column itself is not depending on other parent table.  This is for handling the case that we may need to cover the data type or get a portion, such as a substring from the source table column as the join key.

    Like
Like Follow
  • 1 yr agoLast active
  • 2Replies
  • 380Views
  • 3 Following

Product Announcement


We are happy to
announce Incorta 4.8 !!!