title: Concatenating fields in select clause JOOQ
body: <p>I'm working in a query using JOOQ and I'm trying to output a column as a 
  concatenation (space separated) of other fields extracted in the same query.</p>
  
  <p>Getting into detail, with the next code I try to create a select statement with a column called fullAdress by grouping all the address lines contained in the address table. So, for each field, if it's not null or empty it will be concatenated to the result (actually no space is being added).</p>
  
  <pre><code>@Override
  protected List&lt;Field&lt;?&gt;&gt; selectCustomFields() {
      List&lt;Field&lt;?&gt;&gt; customSelect = new ArrayList&lt;Field&lt;?&gt;&gt;();
  
      // Fields to use in the concatenation
      Field&lt;?&gt; field1 = field("addr.AddressLine1"),   field2 = field("addr.AddressLine2"),field3 = field("addr.AddressLine3"),
               field4 = field("addr.AddressLine4"),   field5 = field("addr.PostalCode"),  field6 = field("addr.City"),
               field7 = field("addr.State"),          field8 = field("addr.County"),      field9 = field("addr.Country");
  
      // Create non null/empty conditions
      Condition condLine1 = field1.isNotNull().and(field1.length().ne(0));
      Condition condLine2 = field2.isNotNull().and(field2.length().ne(0));
      Condition condLine3 = field3.isNotNull().and(field3.length().ne(0));
      Condition condLine4 = field4.isNotNull().and(field4.length().ne(0));
      Condition condLine5 = field5.isNotNull().and(field5.length().ne(0));
      Condition condLine6 = field6.isNotNull().and(field6.length().ne(0));
      Condition condLine7 = field7.isNotNull().and(field7.length().ne(0));
      Condition condLine8 = field8.isNotNull().and(field8.length().ne(0));
      Condition condLine9 = field9.isNotNull().and(field9.length().ne(0));
  
      // Concat address lines when meets condition
      customSelect.add(concat(DSL.when(condLine1, field1),
                              DSL.when(condLine2, field2),
                              DSL.when(condLine3, field3),
                              DSL.when(condLine4, field4),
                              DSL.when(condLine5, field5),
                              DSL.when(condLine6, field6),
                              DSL.when(condLine7, field7),
                              DSL.when(condLine8, field8),
                              DSL.when(condLine9, field9))
                              .as("fullAddress"));
      return customSelect;
  }
  </code></pre>
  
  <p>JOOQ will generate the next from the previous select statement, which is giving a null value and not concatenating the fields correctly.</p>
  
  <pre><code>select 
    concat(
      cast(case when (
                 addr.AddressLine1 is not null
                 and char_length(cast(addr.AddressLine1 as char)) &lt;&gt; 0
               ) then addr.AddressLine1 end as char), 
      cast(case when (
                 addr.AddressLine2 is not null
                 and char_length(cast(addr.AddressLine2 as char)) &lt;&gt; 0
               ) then addr.AddressLine2 end as char), 
      cast(case when (
                 addr.AddressLine3 is not null
                 and char_length(cast(addr.AddressLine3 as char)) &lt;&gt; 0
               ) then addr.AddressLine3 end as char), 
      cast(case when (
                 addr.AddressLine4 is not null
                 and char_length(cast(addr.AddressLine4 as char)) &lt;&gt; 0
               ) then addr.AddressLine4 end as char), 
      cast(case when (
                 addr.PostalCode is not null
                 and char_length(cast(addr.PostalCode as char)) &lt;&gt; 0
               ) then addr.PostalCode end as char), 
      cast(case when (
                 addr.City is not null
                 and char_length(cast(addr.City as char)) &lt;&gt; 0
               ) then addr.City end as char), 
      cast(case when (
                 addr.State is not null
                 and char_length(cast(addr.State as char)) &lt;&gt; 0
               ) then addr.State end as char), 
      cast(case when (
                 addr.County is not null
                 and char_length(cast(addr.County as char)) &lt;&gt; 0
               ) then addr.County end as char), 
      cast(case when (
                 addr.Country is not null
                 and char_length(cast(addr.Country as char)) &lt;&gt; 0
               ) then addr.Country end as char)) as `fullAddress`
  from Address as `addr`
    ....
  </code></pre>
  
  <p>My questions are,</p>
  
  <ul>
  <li>how should I create my select statement correctly?</li>
  <li>how can I best add the space separator?</li>
  <li>is there any better alternative to JOOQ ( when = case ) condition clause?</li>
  </ul>