Pentaho

 View Only

 Dude data base join step

Alejandro Ramos Alvarez's profile image
Alejandro Ramos Alvarez posted 02-09-2024 07:34

Hi,

I have this sql query:

SELECT ORIGIN_CONTRACT_ID, COUNT(*)
FROM(
    SELECT
        ORIGIN_CONTRACT_ID,
        CREATED,
        NTYPE
    FROM BILLING
    WHERE NTYPE IN (1, 73)
    AND ORIGIN_ID = 2
    AND EXTRACT(YEAR FROM CREATED) = '2023'
    AND CODE IN (SELECT TOKEN_ID FROM REQUEST WHERE CREATED >= CURRENT_DATE -50)
    )A
GROUP BY ORIGIN_CONTRACT_ID

And works fine in my dbeaver. Counts the records and groups them by field origin_contract_id. But in spoon in the step Database join:

SELECT ORIGIN_CONTRACT_ID, COUNT(*)
FROM(
    SELECT
        ORIGIN_CONTRACT_ID,
        CREATED,
        NTYPE
    FROM BILLING
    WHERE NTYPE IN (1, 73)
    AND ORIGIN_ID = 2
    AND EXTRACT(YEAR FROM CREATED) = '2023'
    AND CODE = ?
    )A
GROUP BY ORIGIN_CONTRACT_ID

The records are grouped by field code. Why ? I need group by origin_contract_id

Petr Prochazka's profile image
Petr Prochazka posted 02-09-2024 08:12

Hi @Alejandro Ramos Alvarez,

how is grouped by code?Could you post screen of result and what you expected?

How is set join step?

And be careful, Database Join step returns only first row of result set. My fault, it's another step.

Alejandro Ramos Alvarez's profile image
Alejandro Ramos Alvarez posted 02-09-2024 08:29

Hi @Petr Prochazka

In dbeaver and i expected:

In spoon:

TOKEN_ID = CODE

KTR:

Petr Prochazka's profile image
Petr Prochazka posted 02-09-2024 08:39

But results of 2 queries are not same. First query all token_id and group by origin_contract_id, but second call for every token_id and returns row(s).

IMHO if you group by all rows after join by origin_contract_id, you get expected values as first query.

Alejandro Ramos Alvarez's profile image
Alejandro Ramos Alvarez posted 02-09-2024 09:26

How do I group by the origin_contract_id after join by origin_contract_id? another step?. I need group by origin_contract_id and sum all token_id

Petr Prochazka's profile image
Petr Prochazka posted 02-09-2024 10:05

Yes of course. :-)

You can use this steps.

  • Group by - require rows sorted by group fields
  • Memory group by - consume all rows, after push rows by group fields with aggregations.
Petr Prochazka's profile image
Petr Prochazka posted 02-09-2024 10:07

Or you can use first query with another parameters, use step Table input.

Related Content