HANA - SQL Script - Basics

[Former Member]

Hi All

1.   There are two objects in HANA Table and Table Type can any body explain the difference between them

2. Also the difference between Inner Join and Referential Join in the context of attribute views and analytical views.

Thanks

Santosh

[former_member182277 · Accepted solution]

Hello Santosh,

Some points from my side:

1. While creating table, ROW Store, Column Store and Table type are the option.

For modelling the table should be Column Store type.

For more explanation please find the below link:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70d16119-ad21-2e10-de8b-eaaedf86b...

Table types are used to define parameters for a procedure.

2. Please find the link below for difference between Inner and referential join.
https://www.experiencesaphana.com/blogs/datamart/2011/10/17/sql-join-union--what-you-ever-wanted-to-...

Hope it is useful for you.

Regards,Neha

[Former Member · Accepted solution]

Hi Neha

  Thanks for the pointers, I have already gone through the links but I could not clearly understand the difference between Inner Join and Referential Join , both seems to be same for me .can you please explain it.

Thanks

Santosh

[former_member182277 · Accepted solution]

Hello Santosh,

I am not able to share the link as I am having the PDF version provided by SAP. So, I am pasting the stuff for you for better understanding.

Is semantically a inner join that assume that referential integrity is given which means that the left table always have an corresponding entry on the right table. It could be used in e.g. data foundation for header-item relations where it can be assumed that for each item a header exists. It can be seen as an optimized or faster inner join where the right table is not checked if no field from the right table is requested. That means that the Referential Joins will be only executed, when fields from both tables are requested. Therefore, if a field is selected from the right table it will act similar to inner join, and if no fields from the right table is selected it will act similar to a left outer join. From performance perspective, the Left Outer Join are almost equally fast as Referential Join, while the Inner Join is usually slower due to the fact, that the join is always executed.

Referential joins should be used with caution since it assumes that referential integrity is ensured. The only valid scenario for the Referential Join is that (a) it is 110% guaranteed that for each row in one table, there is at least one join partner in the other table, and (b) that holds true in both directions (c) at all times. If that is not the case then referential joins have the possibility to give incorrect calculations if the referential integrity is not met – meaning if a delivery header is created but the items is not processed until a later stage then any calculations that use referential joins will be incorrect.

*** Referential Join cannot be used if a filter is set on a field in the right table.

Hope it clear your doubt.

Regards,Neha

[former_member184768 · Accepted solution]

Hi Santosh,

From database concepts perspective, the inner join is a relationship between two tables, where the data from one table is joined to another table on the joining criteria.

For example, if you have a table Employee and Department, with common key as Dept id, then you can perform an inner join, which will combine the data from both the tables (data sets) and provide you the output where the join condition is satisfied. (Intersection of data sets)

For ex: if there are 5 departments in Department table and 10 employees in employee table. If out of 10 employees only 8 employees have department id assigned, then the inner join on these tables will result into 8 rows.

Referential join also works on the same principles, but enforces a referential constraint on the table, which is like parent-child relationship (Similar to employee - dept table). But it will ensure that NO employee exists in the employee table without having a VALID Department id in the department table.

Rest of the things from HANA DB perspective has been explained above by Neha.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Neha and Ravindra

  It almost cleared my doubt ,except one when we take Inner Join it displays intersection of both tables does it not mean that it satisfies referential integrity.This is the only point left out to clear my doubts on this topic also can you through some light on diff between table and table type.

Thanks

Santosh

[former_member215109 · Accepted solution]

A referential join is a left outer join if no field from the right table is selected. If a field from the right table is selected then you get the same answer on an inner join and referential join.

[Former Member · Accepted solution]

Hi Kraft BI team

  Thanks a lot now the difference between them is crystal clear and the limitation is thatwe can't apply filter on right table field on which we are performing join  am I correct?

Thanks

Santosh

[former_member182277 · Accepted solution]

Hello Santosh,

Yes, you are right. Referential Join cannot be used if a filter is set on a field in the right table.

Regards,Neha

[Former Member · Accepted solution]

Hello Neha

Also can you explain the difference between table and table type what is the use of table type is it similar to view ?

Thanks

Santosh

[former_member182277 · Accepted solution]

Hello Santosh,

Table types are used to define parameters for a procedure.

For modelling the table come into picture.

Regards,neha

[Former Member · Accepted solution]

Hi Neha

Do you mean the input and output parameters , in that case can't we use table as input parameter , I believe only scalar  data types can be used as output parameters .So table type can't be used as output parameter. why can't we use table as parameter?

Thanks

Santosh

[former_member182277 · Accepted solution]

Hello Santosh,

Input variables can be of scalar or table type.OUT parameters must have table type.

Please find the below link.It might helpful for you.

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/20514d3c-f4e1-2e10-55a5-dc9a3efc0...

Regards,

Neha

[Former Member · Accepted solution]

Hi Neha

Yes you are right, I was confused with the statement IN OUT parameters can be of only scalar type .but my doubt remains the same why can't we use table as parameters , only for the purpose of passing parameters to procedures we are using the table type object or is there any other usage for table type object.

[former_member182277 · Accepted solution]

Santosh,

As I already said that as of now table type is used to define parameters for a procedure.

Tables with type column is used for the Modelling purpose and table with type row is used for the system defined tables.

Regards,Neha

[Former Member · Accepted solution]

Hi Neha

  So to Conclude the difference is to just pass the parameters instead of table we are using table type. other than that there is no difference .Please correct me if I am wrong.

Thanks

Santosh

[former_member182277 · Accepted solution]

Yes, you can say that. As of now, table type is used for the parameters.

Regards,Neha

[Former Member · Accepted solution]

Hello Neha,

Does it mean, if i have any filter on my right table, then referential join doesn't work so it will fetch "0" records? is it?

Can you be more clear on this line?

Regards,

Krishna

[Former Member]

referential joins are to ensure the data integrity during insertion of data. if we have a referential join between dimension table customer and fact table sales on customer id field, then this means, we cannot insert any record with customer id that is not in customer table. these constraints help to maintain the data integrity.