-1

I have columns - ip, port, pair, pair_status, length, length_to_fault, add_date. I need to sort everything by port, and each port has a pair(A,B,C,D) atleast once. when its sorted, i need to sort even more - i need to sort each pair in exact port.

Currently i have select that does everything that i need but just with a length.

I want to change this fragment so it could check - if length = N/A, then it takes length_to_fault and if length_to_fault = N/A, then it takes length. My idea is just to combine these 2 columns into 1. Also each record has value on one column only(it can be length or length_to_fault). So far i have this-

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

This works perfectly, but there is records that has N/A in length, but value is inside length_to_fault so this select doesn't take that record. Is there a way to edit this fragment to include length_to_fault too? Maybe i can sum these two columns into one? Also length/length_to_fault is chars in database, so i must change it to float in this select.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Cris
  • 17
  • 8

1 Answers1

0

You can use a case expression:

Select d.*
from (select d.*,
             lead( (case when length <> 'N/A' then length else length_to_fault end)::float) over (partition by port_nbr, pair order by d.add_date) as next_length
      from diags d    
     ) d
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Is there a way to keep my select, but just add something that combines length and length_to_fault columns into one? both are char, but they must be floats. And they also can be N/A, so it could throw error about it and i dont know how to remove it – Cris Jul 23 '20 at 00:57
  • @Cris . . . This is combining the columns, at least for the `lead()`. – Gordon Linoff Jul 23 '20 at 00:58
  • But can i define that combined column somehow , so i can call it in groovy script? Im making groovy script and i need to pull out records from database... Maybe i can define that combined columns as lengthh and if i need to use length in code, i can just calll it with lengthh? – Cris Jul 23 '20 at 01:04
  • Wait - next_length is that length/length_to_fault combined? – Cris Jul 23 '20 at 01:24
  • @Cris . . . I think `next_length` has the two combined as you want them. – Gordon Linoff Jul 23 '20 at 02:04