Skip to main content

Relational Queries

Overview​

Relational queries retrieve data from multiple dimensions using TB_OBJECT views. These views define SQL SELECT statements that join related tables.

Key Concepts:

  • TB_OBJECT defines SQL views with joins
  • Core APIs execute view queries automatically
  • Views appear as read-only dimensions
  • Optimize complex joins and aggregations

TB_OBJECT Views​

View Definition​

TB_OBJECT Structure:

FieldPurposeExample
NAME_OBJView/table nameV_PRODUCTS_WITH_CATEGORY
COD_DIMAssociated dimensionPRD
TYPE_OBJ0=table, 1=view, 2=select1 (view)
VIEW_SCHEMASQL SELECT statementSELECT p.*, c.NAME...

Example View​

Products with Category Name:

INSERT INTO TB_OBJECT (
  NAME_OBJ,
  COD_DIM,
  TYPE_OBJ,
  VIEW_SCHEMA
) VALUES (
  'V_PRODUCTS_CATEGORY',
  'PRD',
  1,
  'SELECT p.PRD_ID, p.XPRD01, p.XPRD02, p.XPRD05,
          c.XCAT01 as CATEGORY_NAME
   FROM TB_ANAG_PRD00 p
   LEFT JOIN TB_ANAG_CAT00 c ON p.XPRD05 = c.CAT_ID
   WHERE p.TREC != ''C'''
);

Query the view:

GET /api/v4/core/PRD?source=productListWithCategory

Response includes joined data:

{
  "data": [
    {
      "PRD_ID": 123,
      "XPRD01": "Widget Pro",
      "XPRD02": 49.99,
      "XPRD05": "cat_elec",
      "CATEGORY_NAME": "Electronics"
    }
  ]
}

Common Join Patterns​

Pattern 1: Left Join (One-to-One)​

Product with Supplier:

VIEW_SCHEMA:
SELECT p.*, s.XSUP01 as SUPPLIER_NAME, s.XSUP02 as SUPPLIER_EMAIL
FROM TB_ANAG_PRD00 p
LEFT JOIN TB_ANAG_SUP00 s ON p.XPRD32 = s.SUP_ID
WHERE p.TREC != 'C'

Pattern 2: Multiple Joins​

Order with Customer and Items:

VIEW_SCHEMA:
SELECT o.ORD_ID, o.XORD01, o.XORD02,
       c.XCLI01 as CUSTOMER_NAME,
       COUNT(i.ORDITEM_ID) as ITEM_COUNT,
       SUM(i.XORDITEM07) as ORDER_TOTAL
FROM TB_ANAG_ORD00 o
LEFT JOIN TB_ANAG_CLI00 c ON o.XORD09 = c.CLI_ID
LEFT JOIN TB_ANAG_ORDITEM00 i ON o.ORD_ID = i.XORDITEM01
WHERE o.TREC != 'C'
GROUP BY o.ORD_ID, o.XORD01, o.XORD02, c.XCLI01

Pattern 3: Nested Subquery​

Products with Stock Level:

VIEW_SCHEMA:
SELECT p.*,
       (SELECT SUM(XSTK_QUANTITY)
        FROM TB_ANAG_STK00
        WHERE XSTK_PRODUCT_ID = p.PRD_ID) as TOTAL_STOCK
FROM TB_ANAG_PRD00 p
WHERE p.TREC != 'C'

Performance Considerations​

Indexed Joins​

✅ Good - Join on indexed fields:

LEFT JOIN TB_ANAG_CAT00 c ON p.XPRD05 = c.CAT_ID
-- Both fields are primary/foreign keys (indexed)

❌ Slow - Join on non-indexed fields:

LEFT JOIN TB_ANAG_CAT00 c ON p.XPRD39 = c.XCAT_NAME
-- String comparison without index

View Materialization​

For frequently accessed complex views, consider:

  • Database materialized views
  • Caching layer
  • Pre-computed aggregations

Best Practices​

✅ DO:​

Join on indexed fields:

ON p.XPRD33 = c.CAT_ID

Include TREC filter:

WHERE p.TREC != 'C' AND c.TREC != 'C'

Use LEFT JOIN for optional relationships:

LEFT JOIN TB_ANAG_SUP00 s ON p.XPRD34 = s.SUP_ID

❌ DON'T:​

Don't join on text fields:

-- ❌ Slow
ON p.XPRD01 = c.XCAT01

Don't create views without indexes:

-- ❌ Missing index on XPRD_CATEGORY
ON p.XPRD_CATEGORY = c.XCAT01

Summary​

  • ✅ Use TB_OBJECT to define SQL views
  • ✅ Views enable complex joins without application code
  • ✅ Always join on indexed fields
  • ✅ Include TREC filtering in view definition
  • ✅ Views are read-only (no POST/PUT/DELETE)

Next: Aggregations →