Skip to main content

Creating Aggregate Queries

DPL Studio ›› Data Selection: Queries>>Aggregate Queries>>

Creating an Aggregate Query​

Create a New Aggregate Query​

  1. Navigate to the Models menu.
  2. Select New Aggregate Query.

Add Tables or Queries​

  1. Drag and drop the required tables or queries into the workflow canvas.
  2. Select the required fields from each source.

aggregate queries

Configure Joins​

Define the relationship between the selected tables using:

  1. Join Types
  2. Join Conditions
  3. Join Relations

Join Types​

The Join Type determines how records from two tables are combined.

  1. Inner Join - Displays only matching records from both tables.

Example : If matching values exist in both tables, the corresponding records will be displayed.

  1. Left Outer Join

Displays:

a. All records from the left table

b. Matching records from the right table

If no matching record exists in the right table, NULL values will be displayed.

  1. Right Outer Join

Displays:

a. All records from the right table

b. Matching records from the left table

If no matching record exists in the left table, NULL values will be displayed.

  1. Full Outer Join

    a. Displays all records from both tables.

    b. Non-matching rows will contain NULL values on the opposite side.

  2. Cross Join

    a. Combines every row from the left table with every row from the right table.

Note:​

Cross Joins may generate very large datasets (Cartesian Product).

aggregate queries 1

Join Conditions​

Join Conditions define how columns from two tables are compared.

Available Join Conditions

  1. Equals (=) : Matches records with equal values
  2. Not Equals (<>) : Matches records with different values
  3. Greater Than (>) : Left value is greater than right value
  4. Less Than (<) : Left value is less than right value
  5. Greater Than or Equal To (>=) : Left value is greater than or equal to right value
  6. Less Than or Equal To (<=) : Left value is less than or equal to right value

aggregate queries 2

Join Relations​

Join Relations define how multiple join conditions are combined.

  1. AND Relation : All conditions must be true for the record to be included.

  2. OR Relation : At least one condition must be true for the record to be included.

aggregate queries 3

Finalizing the Aggregate Query​

  1. Review all join configurations.

  2. Click Preview to validate the combined dataset.

3.Save the query after verification.

aggregate queries 4