0 Replies Latest reply on Sep 6, 2012 8:28 PM by Wolverine .

    Designing the correct filter condition when the SQL data is a Union of queries

    Wolverine .

      Hello troops,

      I am trying to investigate an issue of coming up with correct project count when my backend SQL script is a union of 5 statements.

       

      Defining the Problem:

      The data about compliance of a project comes from the first query (highlighted in yellow). To perform the UNION, I defined the Compliance Metric in the other queries as NULL. [CAST (NULL as VARCHAR (3)) AS "Compliance"]. I'm tasked to calculate the Compliance figures for 10 projects as a whole, that I'm able to successfully compute (refer to sheet 2, where 4 out of 10 projects are compliant -> hence giving me 40%).

       

      Question:

      I have a filter, by the name "Compliant Flag" that I need to fine tune in order to reflect correct calculation. Here's what I'm looking for:

       

      • If I select Compliant, give me 4 projects, and 100% as the Compliance% (TRUE-It gives me that)
      • If I select Non-Compliant, give me 2 projects, and 0% as the Compliance% (TRUE-It gives me that)
      • If I select NULL, give me 4 projects, and 0% as the Compliance% (Trying to figure out)

       

      Now, if I select NULL, it gives me 9 projects, instead of 4 (the reason being that the 9 projects appear in one or more queries apart from the highlighted query 1). Can someone help me with this filter condition, so that it displays the correct count of projects?

       

      Appreciate your time and help,

       

      Best,