Better ways of concating the value of columns

All we need is an easy explanation of the problem, so here it is.

I have a table with below structure and sample data:

create table CUSTOMER_TEST
(
  customer_num Number,
  rel_one      Number,
  rel_two      Number,
  rel_three    Number,
  rel_four     Number
)

   Customer_num    Rel_one     Rel_two        Rel_three        Rel_four 
 --------------- ----------- -------------  --------------  --------------
    1               7           12             1000               5
    2               2            1               0                12
    3              12           99              13                0

The desired result is this :

   Customer_num    Rel_one     Rel_two        Rel_three        Rel_four      Relation_Code
 --------------- ----------- -------------  --------------  --------------  ***************
    1               7           12             1000               5           L07R12C99S05
    2               2            1               0                12          L02R01C00S12
    3              12           99              13                0           L12R99C13S00

What I’ve written is this :

select   customer_num,
         rel_one , 
         rel_two,
         rel_three,
         rel_four,
       'L' ||
       case 
         when   rel_one > 99 then '99'
           else lpad(  rel_one,2,0) end ||
       'R' ||
       case
         when   rel_two > 99 then '99'
           else lpad(  rel_two,2,0) end ||
       'C' ||
        case
         when   rel_three > 99 then '99'
           else lpad(  rel_three,2,0) end  || 
        'S' ||
                case
                 when   rel_four > 99 then '99'
                   else lpad(  rel_four,2,0) end as relation_code            
                  
from customer_test ;

Does anybody have a better idea ? rather than using this much case statement ..

Thanks in advance

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

select   customer_num,
         rel_one , 
         rel_two,
         rel_three,
         rel_four,
       'L' || lpad(LEAST(rel_one,   99), 2, 0) ||
       'R' || lpad(LEAST(rel_two,   99), 2, 0) ||
       'C' || lpad(LEAST(rel_three, 99), 2, 0) ||
       'S' || lpad(LEAST(rel_four,  99), 2, 0)  as relation_code                  
from customer_test ;

I hope that none value can be NULL or negative.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0