Location not to be displayed if 0 in all columns

1. COALESCE Function :

   • Check that if a subquery returns NULL, it is treated as 0. This prevents errors when summing up the values.

2. WHERE Clause :

   • Adds a condition to check if the sum of all six columns is greater than 0. If the total is 0, the row is excluded from the result.

3. Subqueries :

   • Each column is calculated using a subquery. These subqueries remain unchanged but are now used in the WHERE clause for filtering.

To ensure that locations with 0 values in all six columns are not displayed, you need to filter out such rows. This can be achieved by adding a WHERE clause that checks if the sum of all six columns is greater than 0.

SELECT 
    T0."Location",
    (SELECT SUM(A1."LineTotal") 
     FROM OPCH A0 
     INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
     INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
     WHERE A0."CANCELED" NOT IN ('Y', 'C') 
       AND A2."U_A_M" = 'EVE_EXP' 
       AND A0."BPLId" = T0."BPLId" 
       AND A0."DocDate" >= FromDate 
       AND A0."DocDate" <= ToDate) AS "Accounts",
    (SELECT SUM(A1."LineTotal") 
     FROM OPCH A0 
     INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
     INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
     WHERE A0."CANCELED" NOT IN ('Y', 'C') 
       AND A2."U_A_M" = 'PR_MKT' 
       AND A0."BPLId" = T0."BPLId" 
       AND A0."DocDate" >= FromDate 
       AND A0."DocDate" <= ToDate) AS "Sale",
    -- Repeat similar subqueries for the other four columns here
    (SELECT SUM(A1."LineTotal") 
     FROM OPCH A0 
     INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
     INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
     WHERE A0."CANCELED" NOT IN ('Y', 'C') 
       AND A2."U_A_M" = 'OTHER_COL1' 
       AND A0."BPLId" = T0."BPLId" 
       AND A0."DocDate" >= FromDate 
       AND A0."DocDate" <= ToDate) AS "OtherCol1",
    (SELECT SUM(A1."LineTotal") 
     FROM OPCH A0 
     INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
     INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
     WHERE A0."CANCELED" NOT IN ('Y', 'C') 
       AND A2."U_A_M" = 'OTHER_COL2' 
       AND A0."BPLId" = T0."BPLId" 
       AND A0."DocDate" >= FromDate 
       AND A0."DocDate" <= ToDate) AS "OtherCol2",
    (SELECT SUM(A1."LineTotal") 
     FROM OPCH A0 
     INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
     INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
     WHERE A0."CANCELED" NOT IN ('Y', 'C') 
       AND A2."U_A_M" = 'OTHER_COL3' 
       AND A0."BPLId" = T0."BPLId" 
       AND A0."DocDate" >= FromDate 
       AND A0."DocDate" <= ToDate) AS "OtherCol3",
    (SELECT SUM(A1."LineTotal") 
     FROM OPCH A0 
     INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
     INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
     WHERE A0."CANCELED" NOT IN ('Y', 'C') 
       AND A2."U_A_M" = 'OTHER_COL4' 
       AND A0."BPLId" = T0."BPLId" 
       AND A0."DocDate" >= FromDate 
       AND A0."DocDate" <= ToDate) AS "OtherCol4"
FROM OBPL T0
WHERE 
    COALESCE((SELECT SUM(A1."LineTotal") 
              FROM OPCH A0 
              INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
              INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
              WHERE A0."CANCELED" NOT IN ('Y', 'C') 
                AND A2."U_A_M" = 'EVE_EXP' 
                AND A0."BPLId" = T0."BPLId" 
                AND A0."DocDate" >= FromDate 
                AND A0."DocDate" <= ToDate), 0) +
    COALESCE((SELECT SUM(A1."LineTotal") 
              FROM OPCH A0 
              INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
              INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
              WHERE A0."CANCELED" NOT IN ('Y', 'C') 
                AND A2."U_A_M" = 'PR_MKT' 
                AND A0."BPLId" = T0."BPLId" 
                AND A0."DocDate" >= FromDate 
                AND A0."DocDate" <= ToDate), 0) +
    COALESCE((SELECT SUM(A1."LineTotal") 
              FROM OPCH A0 
              INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
              INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
              WHERE A0."CANCELED" NOT IN ('Y', 'C') 
                AND A2."U_A_M" = 'OTHER_COL1' 
                AND A0."BPLId" = T0."BPLId" 
                AND A0."DocDate" >= FromDate 
                AND A0."DocDate" <= ToDate), 0) +
    COALESCE((SELECT SUM(A1."LineTotal") 
              FROM OPCH A0 
              INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
              INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
              WHERE A0."CANCELED" NOT IN ('Y', 'C') 
                AND A2."U_A_M" = 'OTHER_COL2' 
                AND A0."BPLId" = T0."BPLId" 
                AND A0."DocDate" >= FromDate 
                AND A0."DocDate" <= ToDate), 0) +
    COALESCE((SELECT SUM(A1."LineTotal") 
              FROM OPCH A0 
              INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
              INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
              WHERE A0."CANCELED" NOT IN ('Y', 'C') 
                AND A2."U_A_M" = 'OTHER_COL3' 
                AND A0."BPLId" = T0."BPLId" 
                AND A0."DocDate" >= FromDate 
                AND A0."DocDate" <= ToDate), 0) +
    COALESCE((SELECT SUM(A1."LineTotal") 
              FROM OPCH A0 
              INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
              INNER JOIN OITM A2 ON A1."ItemCode" = A2."ItemCode" 
              WHERE A0."CANCELED" NOT IN ('Y', 'C') 
                AND A2."U_A_M" = 'OTHER_COL4' 
                AND A0."BPLId" = T0."BPLId" 
                AND A0."DocDate" >= FromDate 
                AND A0."DocDate" <= ToDate), 0) > 0;

Hi! I understand that you are looking to refine your SQL query to exclude displaying records where the Location has zero values in all six columns. To achieve this in SQL, you can use a HAVING clause after performing the necessary calculations. The HAVING clause can conditionally filter records based on aggregated values, which in this case would be checking if all six columns have a sum greater than zero.

Here's an example of how you could modify your SQL query to account for this condition:

SELECT T0."Location",
    (SELECT SUM(A1."LineTotal") FROM OPCH A0 INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
    INNER JOIN Oitm A2 ON A1."ItemCode" = A2."ItemCode" AND A0."CANCELED" NOT IN ('Y','C') AND A2."U_A_M" = 'EVE_EXP' AND A0."BPLId" = T0."BPLId" AND A0."DocDate" >= FromDate AND A0."DocDate" <= ToDate) AS "Accounts",
    (SELECT SUM(A1."LineTotal") FROM OPCH A0 INNER JOIN PCH1 A1 ON A0."DocEntry" = A1."DocEntry"
    INNER JOIN Oitm A2 ON A1."ItemCode" = A2."ItemCode" AND A0."CANCELED" NOT IN ('Y','C') AND A2."U_A_M" = 'PR_MKT' AND A0."BPLId" = T0."BPLId" AND A0."DocDate" >= FromDate AND A0."DocDate" <= ToDate) AS "Sale"
FROM OBPL T0
GROUP BY T0."Location"
HAVING SUM("Accounts") + SUM("Sale") > 0;

In this revised query:

- The GROUP BY clause groups the results by Location.

- The HAVING clause ensures that only records with non-zero sums in both "Accounts" and "Sale" columns are returned.

By incorporating this modification, your SQL query will display only those records where the Location has non-zero values in at least one of the specified columns. This refinement should help achieve your goal of excluding records with zero values in all six columns. Let me know if you need further assistance or if you have any other questions!