CODEWITHSUNDEEP
sqloracle-databaseoracle12c
JagaSrik
JagaSrik

Reputation: 718

How to get the first non zero / non null value and the last zero / non null value among multiple columns using oracle sql

This is how the data looks, here the

"ID"    "ACTIVE"    "SERVICEID" "MONTH" "D1"    "D2"    "D3"    "D4"    "D5"    "D6"    "D7"    "D8"    "D9"    "D10"   "D11"   "D12"   "D13"   "D14"   "D15"   "D16"   "D17"   "D18"   "D19"   "D20"   "D21"   "D22"   "D23"   "D24"   "D25"   "D26"   "D27"   "D28"   "D29"   "D30"   "D31"   
352917  1   "FEUSA0001U"    199603                                              24.48   23.76   24.24   24.82           24.84   25.24   25  25  25.5            25.76   25.88   25.62   25.24   25.62               
353793  1   "FEUSA00024"    199603                                              14.92   14.77   14.8    14.78           14.57   14.75   14.75   14.75   14.75           14.75   14.75   14.56   14.56   14.69               
377994  1   "FEUSA0001X"    199603                                              59.16   58.84   59.12   59.92           59.72   60  60  61.52   61.24           61.76   61.76   62.24   62.24   62.76               
377737  1   "FEUSA00026"    199603                                                   9.89   9.9 10.01           10.01   10.12   10.12   10.25   10.25           10.25   10.25   10.25   10.25   10.25

So for the first record i need to get back First non zero / non null as D6 or 6 and for the last record non zero/ not null value should be D20 or 20.

Let me know if you need more information on this.

Here is the DDL for the table 

CREATE TABLE "SERV" (
     "ID"           NUMBER,
     "ACTIVE"       NUMBER,
     "SERVICEID"    VARCHAR2(10 BYTE),
     "MONTH"        NUMBER(*,0),
     "D1"           NUMBER,
     "D2"           NUMBER,
     "D3"           NUMBER,
     "D4"           NUMBER,
     "D5"           NUMBER,
     "D6"           NUMBER,
     "D7"           NUMBER,
     "D8"           NUMBER,
     "D9"           NUMBER,
     "D10"          NUMBER,
     "D11"          NUMBER,
     "D12"          NUMBER,
     "D13"          NUMBER,
     "D14"          NUMBER,
     "D15"          NUMBER,
     "D16"          NUMBER,
     "D17"          NUMBER,
     "D18"          NUMBER,
     "D19"          NUMBER,
     "D20"          NUMBER,
     "D21"          NUMBER,
     "D22"          NUMBER,
     "D23"          NUMBER,
     "D24"          NUMBER,
     "D25"          NUMBER,
     "D26"          NUMBER,
     "D27"          NUMBER,
     "D28"          NUMBER,
     "D29"          NUMBER,
     "D30"          NUMBER,
     "D31"          NUMBER
)

DML sample records

Insert into SERV (ID,ACTIVE,SERVICEID,MONTH,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) values (352917,1,'FEUSA0001U',199603,null,null,null,null,null,null,null,null,null,null,null,24.48,23.76,24.24,24.82,null,null,24.84,25.24,25,25,25.5,null,null,25.76,25.88,25.62,25.24,25.62,null,null);
Insert into SERV (ID,ACTIVE,SERVICEID,MONTH,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) values (353793,1,'FEUSA00024',199603,null,null,null,null,null,null,null,null,null,null,null,14.92,14.77,14.8,14.78,null,null,14.57,14.75,14.75,14.75,14.75,null,null,14.75,14.75,14.56,14.56,14.69,null,null);
Insert into SERV (ID,ACTIVE,SERVICEID,MONTH,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) values (377994,1,'FEUSA0001X',199603,null,null,null,null,null,null,null,null,null,null,null,59.16,58.84,59.12,59.92,null,null,59.72,60,60,61.52,61.24,null,null,61.76,61.76,62.24,62.24,62.76,null,null);
Insert into SERV (ID,ACTIVE,SERVICEID,MONTH,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) values (377737,1,'FEUSA00026',199603,null,null,null,null,null,null,null,null,null,null,null,9.95,9.89,9.9,10.01,null,null,10.01,10.12,10.12,10.25,10.25,null,null,10.25,10.25,10.25,10.25,10.25,null,null);

Upvotes: 0

Views: 1356

Answers (2)

Fabricio Ardizon
Fabricio Ardizon

Reputation: 106

try this:

SELECT ID,
       ACTIVE,
       SERVICEID,
       MONTH,
       COALESCE(D1, D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) FIRST_NOTNULL,
       COALESCE(D31, D30,D29,D28,D27,D26,D25,D24,D23,D22,D21,D20,D19,D18,D17,D16,D15,D14,D13,D12,D11,D10,D9,D8,D7,D6,D5,D4,D3,D2,D1) LAST_NOTNULL
  FROM SERV

Regards!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You have a very poor data format. In general, having columns that are distinguished just by a number is a bad sign. The better structure is one row per value, rather than one column.

You can do what you want using a giant case expression:

select s.*,
       (case when s.d1 <> 0 then s.d1
             when s.d2 <> 0 then s.d2
             . . .
             when s.d31 <> 0 then s.d31
        end) as first_flag
from serv s;

Upvotes: 1

Related Questions