Why are self-joins designed for tables in SAP Universe

[guozheng_chen]

Why are self-joins designed for tables in SAP Universe, and what are the benefits of this design? and are both aliases for , but when querying and , they originate from different alias tables. SELECT FACT_SHIPMENT.STT_NB, FACT_SHIPMENT_METRICS.GROSS_WEIGHT FROM AOR.AORFCT_SHIPMENT_UNION_V FACT_SHIPMENT_METRICS INNER JOIN AOR.AORFCT_SHIPMENT_UNION_V FACT_SHIPMENT ON (FACT_SHIPMENT_METRICS.SHIPMENT_KEY=FACT_SHIPMENT.SHIPMENT_KEY) WHERE FACT_SHIPMENT.STT_NB IN ('xxxxxxxx' )

[drs_macgyver]

I'm not sure I understand your question.  In looking at your SQL, it is not technically a self join but an alias of the AOR table joined to another alias of the AOR table.  In universes, a self join appears as a join but is just a condition forced every time the table is referenced in a query.

The join in your SQL implies that the AOR table has multiple levels of detail.  your case, the AORFCT_SHIPMENT_UNION_V as multiple detail levels about the shipment using the same SHIPMENT_KEY.  The way query is set up, you will only get the rows where STT_NB='xxxxxxx' from the FACT_SHIPMENT alias and you will get ALL of the details of the shipment, including when STT_NB='xxxxxxx' from the FACT_SHIPMENT_METRICS.  I don't know the business need but it seems to me you might want an additional condition to limit FACT_SHIPMENT_METRICS <> 'xxxxxxx'.

If this seems to answer your question, kindly mark this as an Accepted Solution.  If not, please provide more details and I'll give it my best effort.