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

  • AS
    AS Coach
    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"

Answers

  • AS
    AS Coach
    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"