0

I have 1 table where is 2 columns that i need to use at this moment( length and length_to_fault). If length has a null(N/A) value then i need to select value from length_to_fault and opposite. I also need to sort everything and i can do it with 1 column like this:

select d.* from (select d.*, lead(length::float) over (partition by port_nbr, pair order by add_date) as next_length from diags d where length != 'N/A') d

this select sorts everything except length_to_fault. If 1 record has value in length_to_fault , it will be ignored and it wont show up. Is there any suggestions? Maybe its possible to just make these 2 columns into 1? It sounds much logical. But how?

Cris
  • 17
  • 8
  • 2
    Please provide sample data and desired results. – GMB Jul 15 '20 at 13:13
  • 1
    [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Jul 15 '20 at 13:14
  • you can try that with Coalesce. – The AG Jul 15 '20 at 13:21
  • I'm rather baffled. I think your `lead()` should generate a type conversion error when the next value is `'N/A'`. Also you mention "sorting" but your question has no `order by`. And there is no column in the query called `length_to_fault`. – Gordon Linoff Jul 15 '20 at 13:31
  • There is length_to_fault in my table, but i dont know how to use it in my code. Everything works as i need it , but with only length. There is 1 problem : There is 2 columns - length and length_to_fault. If length is N/A, then length_to_fault has a value. And if length_to_fault is N/A, then length has value. I could just combine these 2 columns into 1 , but i dont know how – Cris Jul 15 '20 at 14:28

1 Answers1

1

I changed it to select d.* from (select d.*, lead(sum(length::float + length_to_fault::float)) over (partition by port_nbr, pair order by d.add_date) as next_length from diags d)d i get error : column "d.ip" must appear in the group by clause or be used in an aggregate function. I dont need to use ip column... I even dont know where to put that ip right now

Cris
  • 17
  • 8