SQL for master data

The BP data model as tables

Partner, addresses, tax numbers and roles are separate tables joined by keys. This is a simplified teaching subset; check real structures in SE11 or ADT before relying on a field.

Joins: keep the gaps visible

For data quality use LEFT JOIN: 'partner without address' is exactly the finding you want, and an inner join hides it. Two SAP traps: on a raw schema, join and filter on CLIENT; and ADRC holds history and international versions, so filter on current validity and nation = ' ' or rows multiply.

SELECT b.partner, b.name_org1, a.city1, a.post_code1, a.country,
       t.taxnum AS gstin
FROM but000 b
LEFT JOIN but020 l
       ON l.client = b.client AND l.partner = b.partner
LEFT JOIN adrc a
       ON a.client = l.client AND a.addrnumber = l.addrnumber
      AND a.nation = ' '                 -- default version
      AND a.date_to >= CURRENT_DATE      -- currently valid
LEFT JOIN dfkkbptaxnum t
       ON t.client = b.client AND t.partner = b.partner
      AND t.taxtype = 'IN3'              -- check the GST tax type in your system
WHERE b.client = '100'
  AND b.type = '2';                      -- organisations

Window functions: duplicates without losing detail

GROUP BY ... HAVING COUNT(*) > 1 collapses rows, so you lose which partners are involved. COUNT(*) OVER (PARTITION BY taxnum) writes the group size onto every row; ROW_NUMBER() OVER (PARTITION BY taxnum ORDER BY crdat) numbers rows within the group, and row 1 is the survivor candidate. HANA, PostgreSQL, SQL Server, Oracle and SQLite all support this.

Build the duplicate query step by step

Try it: write the duplicate query

WITH flagged AS (
  SELECT partner, taxnum, crdat,
    COUNT(*) ______GROUP BYPARTITION BYINNER JOINWHEREHAVINGOVERORDER BY (PARTITION BY taxnum) AS dup_count,
    ROW_NUMBER() OVER (______GROUP BYPARTITION BYINNER JOINWHEREHAVINGOVERORDER BY taxnum ______GROUP BYPARTITION BYINNER JOINWHEREHAVINGOVERORDER BY crdat) AS rn
  FROM bp
)
SELECT * FROM flagged
______GROUP BYPARTITION BYINNER JOINWHEREHAVINGOVERORDER BY dup_count > 1;

Try it: pick the window function

A data-quality query pack

Keep one query per rule, each returning a rule ID and a count, combined with UNION ALL over a shared base CTE (the LEFT JOIN above). Run it after every load and you get a report-ready table, using the same rule IDs as your Python profile. SAP often stores empty fields as spaces or empty strings, so test for both, not just NULL.

From HANA SQL to CDS views to OData

In S/4HANA, application logic does not query raw tables with native SQL. It is modelled in CDS view entities, which handle the client automatically and build on released views like I_BusinessPartner and I_Supplier. A CDS view can be exposed as OData, which is how the API in lesson 4-3 gets its data. Prototype in SQL, productise in CDS.

Try it: where should the logic live?

Check yourself