ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to position in SQL with case in order by

Collapse
This topic is closed.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to position in SQL with case in order by

    I have this records

    ....+....1....+....2....+....3....+....4....+..
    Type RRN ( File) CONCAT

    B 145.352 B000000000145352
    B 145.395 B000000000145395
    O 145.353 O000000000145353
    E 145.392 E000000000145392
    E 145.394 E000000000145394

    The SQL

    SELECT type, RRN(file) , type || LPAD ( RRN(file , 15 ,
    '0') FROM ncprec ORDER BY
    Case when zetprg = 'B' then '1' when zetprg = 'C' then '2' when
    zetprg = 'O' then '3' when zetprg = 'E' then '4' when zetprg = 'P'
    then '5' end, RRN(NCPREC)


    If i got the key of the 3 record, O000000000145353, how can i get the position?
    If i put in the where clase:
    type|| LPAD ( RRN(file) , 15 , '0') >='O'||'000000000145353'
    it will only return tha line, becouse the next its bigger.






Working...
X