DB2 – Column Aliases with Line Breaks

aliasdb2db2-9.7dbeaver

Using DB2 9.7, is it possible to produce a result set where the column-headers contain line-breaks? Something like:

select field_name as "Line1<br>Line2" from table_name

I want the column-header to look like this:

Line1
Line2

However, this is not for HTML. Instead I'm wanting it to look this way when I run the query (raw) in my sql editor (Dbeaver) and in the CSV file I export from Dbeaver (so that the columns are narrower — to prevent horizontal scrolling while viewing the result-set).

Best Answer

DB2 will allow you to use a new line character in a quoted identifier:

$ db2 'select ibmreqd "foo
> bar" from sysibm.sysdummy1'

foo
bar
-------
Y      

  1 record(s) selected.

The question is, will Dbeaver let you enter a new line character where you want it.

Related Solutions

Sql-server – Subqueries’ aliases same as main queries’ aliases

It's OK for nested subqueries to use the same aliases as used in the parent query, although it might be a bit confusing for someone reading the code. The name space for aliases on a nested subquery is separate from the name space on the parent. For example the query below has a nested subquery b that also has an alias b used within it. This would be potentially confusing to the programmer but fine with the DBMS engine:

   select a.foo
          ,b.bar
          ,b.BarCount
      from (select b.bar
                  ,count (*) as BarCount
              from BarTable b
              join OtherTable o
                on b.OtherTableID = o.OtherTableID
             group by b.bar) b
      join Foobar a
        on a.bar = b.bar

On a correlated subquery you have access to the parent's aliases, so the aliases must be unique across the parent query and correlated subquery. If we take a correlated subquery such as the one below we have a single, global name space shared between the parent query and the correlated subquery:

select a.foo
      ,b.bar
  from Foobar a
  join Bar b
    on b.FooBarID = a.FooBarID
 where not exists
       (select 1
          from Bar b2
         where b2.BarCategoryID = b.BarCategoryID
           and b2.BarDate > b.BarDate)

The correlated subquery does not have an alias as it does not participate in a join as such¹. The references b and b2 for bar are both available to the subquery as correlated subqueries share their namespace for aliases with the parent.

¹ Note that the optimiser may choose to use join operators within the plan behind the scenes, although the actual operation specified is a correlated subquery and not a join against a nested subquery.

Sql-server – Issue with SQL Server Aliases on a multi-instance installation

If you want aliases to work from the client then you have to create them on the client. Install the SQL Server client (from memory the feature is called Client Tools) and use Configuration Manager to create the aliases (32-bit or 64-bit to match the client application).

Best Answer

Related Solutions

Sql-server – Subqueries’ aliases same as main queries’ aliases

Sql-server – Issue with SQL Server Aliases on a multi-instance installation

Related Question