nested case for arithmetic operators

drewfig
drewfig Member
in Magic ETL

Hi, 

 

I've been having troubles with MySQL dataflow trying to write a code basically doing something like this:

 

(CASE WHEN week = 1 THEN (a/b) END) - (c/d) * e

 

i tried writing it down like this:

 

(CASE WHEN `Week` = "1" THEN SUM((CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN(`8Q_Price`*`Tons`) END))
/ SUM((CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END)) END)  ----- (CASE when week = 1 THEN (a/b) END) ------

 

-

 

(SUM((CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN(`8Q_Price`*`Tons`) END))
/ SUM((CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END))) ----(c/d)----

 

*

 SUM((CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END)) ----e-----

 

I am not having syntax errors with this code but I keep on getting wrong answers even if I change the groupings. Can you guys recommend something?

 

Thanks!

 

p.s. a colleague of mine recommended to use a CREATE FUNCTION statement, will it work in our MySQL Dataflow?

Best Answer

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    Hey drewfig,

     

    No worries. I moved some of the parentheses around so this should work for what you described above.

     

    ((SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `8Q_Price`*`Tons` END)
    / 
    SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `Tons` END)) ----- (CASE when week = 1 THEN (a/b) END) ------
    -
    (SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `8Q_Price`*`Tons` END)
    / SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END))) ----(c/d)----
    *
    SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END) ----e-

     

    Hope this helps,

     

    Brian

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Answers

  • Property_Ninja
    Property_Ninja Contributor

    Hello Drewfig,

     

    Based on the order of operations, you want your algorithm to divide a and b then divide c and d then multiply the results of c/d by e and then subtract the results of (a/b) and (c/d*e). 

    (SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `8Q_Price`*`Tons` END)
    / 
    SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `Tons` END)) ----- (CASE when week = 1 THEN (a/b) END) ------
    -
    ((SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `8Q_Price`*`Tons` END)
    / SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END)) ----(c/d)----
    *
    SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END)) ----e-

     

    The above will work for what you described in your post.

     

    Hope this helps,

     

    Brian

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • drewfig
    drewfig Member

    Hey Brian,

     

    Thanks for taking time to check on my question. actually what we wanted to do is to subtract the results of (a/b) and (c/d) and then the result of the subtraction will be multiplied by e.

     

    It will be like ((a/b) - (c/d)) * e.

     

     

    I apologize for the confusion yesterday. can you help revising the code? thanks!

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    Hey drewfig,

     

    No worries. I moved some of the parentheses around so this should work for what you described above.

     

    ((SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `8Q_Price`*`Tons` END)
    / 
    SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `Tons` END)) ----- (CASE when week = 1 THEN (a/b) END) ------
    -
    (SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `8Q_Price`*`Tons` END)
    / SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END))) ----(c/d)----
    *
    SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END) ----e-

     

    Hope this helps,

     

    Brian

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Categories