Eliminate zero from multiple columns which value is complete zero-oracle sql-CodePudding

I am trying to exclude the rows with zero values from all months showing in the below table.

This is sample data; there will be thousand of products with partial or full zero in between 12 months in real scenario. There will also be more months (columns) in real scenario.

The expected result will be look like this.

CodePudding user response：

In case the affected columns are always natural numbers, you can sum them and check whether they are > 0.

SELECT * FROM yourtable WHERE jan   feb   march   april > 0;

If they can be negative, but are always numbers, you can do the same based on their absolute values.

SELECT * FROM yourtable WHERE ABS(jan)   ABS(feb)   ABS(march)   ABS(april) > 0;

CodePudding user response：

In real scenario, you'll just have to put more columns into the where clause.

Sample data:

SQL> with test (prod_nm, jan, feb, mar, apr) as
  2    (select 'GRANULES' , 500, 200, 100, 500 from dual union all
  3     select 'INJECTION',   0,   0, 300, 550 from dual union all
  4     select 'VET-A'    ,   0,   0,   0, 300 from dual union all
  5     select 'VET-B'    ,   0,   0,   0,   0 from dual
  6    )

Query:

  7  select *
  8  from test
  9  where jan <> 0 or feb <> 0 or mar <> 0 or apr <> 0;

PROD_NM          JAN        FEB        MAR        APR
--------- ---------- ---------- ---------- ----------
GRANULES         500        200        100        500
INJECTION          0          0        300        550
VET-A              0          0          0        300

SQL>