Location not to be displayed if 0 in all columns

Question

Hi I have below code and i want if Location has 0 value in all six columns then it should not be displayed. 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" = FromDate and A0."DocDate" Thanks

Tuhin Paul · Answer

COALESCE Function : Check that if a subquery returns NULL , it is treated as 0 . This prevents errors when summing up the values. 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. Subqueries : Each column is calculated using a subquery. These subqueries remain unchanged but are now used in the WHERE clause for filtering.

Tuhin Paul · Answer

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" = FromDate AND A0."DocDate" = FromDate AND A0."DocDate" = FromDate AND A0."DocDate" = FromDate AND A0."DocDate" = FromDate AND A0."DocDate" = FromDate AND A0."DocDate" = FromDate AND A0."DocDate" = FromDate AND A0."DocDate" = FromDate AND A0."DocDate" = FromDate AND A0."DocDate" = FromDate AND A0."DocDate" 0;

Eliana Blake · Answer

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" = FromDate AND A0."DocDate" 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!