Replacing hard coded value in a view which has CTEs

Question

Hi

I have a view defined which has a couple of CTE's, the section that I would like to replace can be seen below.

application_deployment_summary (software_id,systems_count,LatestVersion_systems_count) AS
(
     SELECT software_id, SUM(no_of_systems) AS systems_count, SUM(CASE WHEN sw_version_no >= 2.05 THEN no_of_systems ELSE 0 END) AS LatestVersion_systems_count
     FROM cte_breakdown
     WHERE server_tier NOT IN ('UAT')
     GROUP BY software_id
)

SELECT software_id, LatestVersion_systems_count * 100 / systems_count AS LatestVersion_Percentage
 FROM application_deployment_summary;

Please note that the above is a subsection of the query, the sw_version_no is currently hardcoded (2.05), I would like to read this from a table so that I can remove the hard coded value and use the value being held within the table. I have tried to use a subquery but got the error below.

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Answer 1

Hi @MrFlinstone ,

Would you please try CROSS JOIN, e.g.,

DECLARE @tbl_SWVersion TABLE (  
 		Id INT IDENTITY,  
        sw_version DECIMAL(5,2)  
    );  
              
INSERT INTO @tbl_SWVersion VALUES(2.05);  
  
SELECT cteb.software_id, SUM(cteb.no_of_systems) AS systems_count,   
SUM(CASE WHEN cteb.sw_version_no >= swv.sw_version THEN cteb.no_of_systems ELSE 0 END) AS LatestVersion_systems_count  
FROM @cte_breakdown as cteb  
CROSS JOIN (select TOP 1 * from @tbl_SWVersion Order By Id desc) as swv  
WHERE cteb.server_tier NOT IN ('UAT')  
GROUP BY cteb.software_id  

Thanks!

Answer 2

Hi @MrFlinstone ,

Please try the following:

DECLARE @tbl_SWVersion TABLE (  
  Id INT IDENTITY,  
        sw_version DECIMAL(5,2)  
    );  
              
INSERT INTO @tbl_SWVersion VALUES(2.05);  
  
Declare @swVersion AS DECIMAL(5,2);  
  
SET @swVersion =  (SELECT TOP 1 sw_version from @tbl_SWVersion Order By Id desc);  
  
; with application_deployment_summary (software_id,systems_count,LatestVersion_systems_count) AS  
 (  
      SELECT software_id, SUM(no_of_systems) AS systems_count, SUM(CASE WHEN sw_version_no >=  @swVersion THEN no_of_systems ELSE 0 END) AS LatestVersion_systems_count  
      FROM cte_breakdown  
      WHERE server_tier NOT IN ('UAT')  
      GROUP BY software_id  
 )  
      
 SELECT software_id, LatestVersion_systems_count * 100 / systems_count AS LatestVersion_Percentage  
  FROM application_deployment_summary;  

Note: please Declare the TYPE for sw_version and @swVersion same as sw_version_no. Thanks!

Answer 3

Glad your problem is solved.
If you have any question, please feel free to let me know.

Regards
Echo