Adrenaline Dataflow: Inequality Correlated Subqueries Unsupported
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!
0
Answers
-
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"
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive