8 Replies Latest reply on Jul 10, 2013 7:37 AM by philmodjunk

    ExecuteSQL - Nested Select

    kennethpeace

      Title

      ExecuteSQL - Nested Select

      Post

           I've been successful in using ExecuteSQL to do charting for Monthly Revenue. That's kind of straight forward. But lately when i attempt to do Average Monthly Revenue, it hit a roadblock. To do that, i need nested select. But i can't seem to get it to work. Pls see below statements. I hope someone can give me some advise. 

           Let([
           $Today = Get(CurrentDate);
           $TargetYear = Year($Today);
            
           $query = "SELECT * FROM
               (SELECT SUM(r.\"Final Price\") ListTotal
               FROM \"Class Schedule\" cs  JOIN Registration r 
                    ON YEAR(cs.\"Class Date\") = ?
                    AND cs.\"Class Schedule id\" = r.\"Class Schedule id\"
                    AND r.\"Payment Status\" = 'Paid' AND r.\"Registration Status\" = 'Registered'
              GROUP BY cs.\"Month in number\"
              ORDER BY cs.\"Month in number\"
              )
           ";
            
           $result = ExecuteSQL( $query; "" ; "" ; $TargetYear )
           ]; $result )
            
            
           ========== 
           Below is the scripts that works when i just display monthly SUM.
                Let([
                $Today = Get(CurrentDate);
                $TargetYear = Year($Today);
                 
                $query = "SELECT SUM(r.\"Final Price\") ListTotal
                    FROM \"Class Schedule\" cs  JOIN Registration r 
                         ON YEAR(cs.\"Class Date\") = ?
                         AND cs.\"Class Schedule id\" = r.\"Class Schedule id\"
                         AND r.\"Payment Status\" = 'Paid' AND r.\"Registration Status\" = 'Registered'
                   GROUP BY cs.\"Month in number\"
                   ORDER BY cs.\"Month in number\"
                ";
                 
                $result = ExecuteSQL( $query; "" ; "" ; $TargetYear )
                ]; $result )