Adrenaline Dataflow: Inequality Correlated Subqueries Unsupported

MosesWynn · 2022-01-19T18:40:26+00:00

There was an error rendering this rich post.

Hello,

I'm attempting to perform a correlated subquery using an inequality as shown in the below example.

SELECT
    a."Opportunity ID",
    a."Owner ID",
    a."Created Date",
    a."Close Date",
    a."Snapshot Date",
    a."Last Modified Date",
    a."Next Step",
    a."Competitor",
    a."Amount",
    a."Stage",
    (SELECT
         COUNT("Opportunity ID")
     FROM "pushes"
     WHERE a."Opportunity ID" = pushes."Opportunity ID" AND
         pushes."Change Date" <= a."Snapshot Date") AS "Push Count"
FROM "pipeline_snapshots_min_cols" "a"

Unfortunately this is not supported in Adrenaline dataflows. I need to use Adrenaline though because the dataset has ~ 80 million rows and other methods take too long to process. Does anyone know of a different way that I could achieve the same results?

Thanks!

Find more posts tagged with

Accepted answers

All comments

MarkSnodgrass

I don't have a good dataset to test this with, but I wonder if this would work:

SELECT
    a."Opportunity ID",
    a."Owner ID",
    a."Created Date",
    a."Close Date",
    a."Snapshot Date",
    a."Last Modified Date",
    a."Next Step",
    a."Competitor",
    a."Amount",
    a."Stage",
    COUNT(pushes."Opportunity ID")
     AS "Push Count"
FROM "pipeline_snapshots_min_cols" "a"
LEFT JOIN "pushes" ON a."Opportunity ID" = pushes."Opportunity ID" AND
         pushes."Change Date" <= a."Snapshot Date"
GROUP BY a."Opportunity ID",
    a."Owner ID",
    a."Created Date",
    a."Close Date",
    a."Snapshot Date",
    a."Last Modified Date",
    a."Next Step",
    a."Competitor",
    a."Amount",
    a."Stage"

WHM

I have found these sub-selects very finicky in Adrenaline. a recent one I am working on will not preview but will run without error. I opened a ticket, so we shall see.