Sum a case statement in SQL data source
trying to group and sum up a case statement, but keeps erroring out (Failed to retrieve data. Error ticket#.....). tried moving the parentheses around, but nothing's working. below is my query, any ideas?
SELECT
A. TRANDATE,
A. TRANID,
A. TRANSACTION_TYPE,
SUM (CASE
WHEN A. TRANSACTION_TYPE = 'Sales Order' AND B. ACCOUNT_ID = '124' THEN B. AMOUNT
WHEN A. TRANSACTION_TYPE in ('Invoice','Credit Memo') AND B. ACCOUNT_ID = '453' THEN B. AMOUNT
ELSE 0 END) as 'Header Amount'
FROM TRANSACTIONS A
LEFT JOIN TRANSACTION_LINES B on A. TRANSACTION_ID = B. TRANSACTION_ID
WHERE
A. TRANDATE > '2017/08/15' AND
A. TRANSACTION_TYPE in ('Invoice','Sales Order','Credit Memo')
GROUP BY
A. TRANDATE,
A. TRANID,
A. TRANSACTION_TYPE
Best Answer
-
Looks solid to me at quick glance. Except maybe the formatting on the date filter. I usually use something more like '2017-08-15'.
Aside from that, are your TRANSACTIONS and TRANSACTIONS_LINES datasets completed loaded, even with just the preview rows, before you run the transform? The error Failure to retrieve data indicates to me that something was wrong with the input and needs to be resolved.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Answers
-
Looks solid to me at quick glance. Except maybe the formatting on the date filter. I usually use something more like '2017-08-15'.
Aside from that, are your TRANSACTIONS and TRANSACTIONS_LINES datasets completed loaded, even with just the preview rows, before you run the transform? The error Failure to retrieve data indicates to me that something was wrong with the input and needs to be resolved.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 302 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 103 SQL DataFlows
- 633 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 760 Beast Mode
- 61 App Studio
- 41 Variables
- 699 Automate
- 181 Apps
- 457 APIs & Domo Developer
- 51 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 400 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive