Concatenate value into SELECT on a condition

Question

I need to insert into a table a list of concatinated values but the first one needs to be added if p_is_broker variable is true. For simplicity let's say it's true.

insert into exports (session_id, row_sequence, row_data)
select token,
       1, 'Manager'
          ||','||'Company'
          ||','||'Driver Name'
          ||','||'Registration'
          ||','||'Vehicle'
from dual
union
select token,
       rownum + 1,
       '"'||replace(manager,'"','""')
          ||'","'||replace(company,'"','""')
          ||'","'||replace(driver_name,'"','""')
          ||'","'||replace(registration,'"','""')
from table(cast (p_data as data_t));

How can I insert 'Manager' if p_is_broker is true?

I've tried something like this but it doesn't work.

 insert into exports (session_id, row_sequence, row_data)
 select token,
        1, (CASE WHEN p_is_broker THEN 'Manager' END)
            ||','||'Company'
            ||','||'Driver Name'
            ||','||'Registration'
            ||','||'Vehicle'
 from dual;

It becomes quite tricky as we want the first ||','|| if 'Manager' is there.

Answer 1

Move the comma inside the case:

insert into session_csv_Exports (session_id, row_sequence, row_data)
select
  token,
  1,
  (CASE WHEN p_is_broker = 'Y' THEN 'Manager,' ELSE '' END)
  ||'Company'
  ||','||'Driver Name'
  ||','||'Registration'
  ||','||'Vehicle'
from dual;

Also, I can’t think of any reason to concatenate text literals. You should simplify to:

insert into session_csv_Exports (session_id, row_sequence, row_data)
select
  token,
  1,
  (CASE WHEN p_is_broker THEN 'Manager,' ELSE '' END)
  || 'Company,Driver Name,Registration,Vehicle'
from dual;