hsql union all

Help
siraj
2013-02-12
2014-01-19
  • siraj
    siraj
    2013-02-12

    Hi,

    How to write hsql query with union all on dual tables
    Like in oracle:
    select 'a','b' from dual
    union
    select 'c','d' from dual

    How to achieve this is hsql

     
  • siraj
    siraj
    2013-02-12

    I am using hsql 2.2.9

     
  • Fred Toussi
    Fred Toussi
    2013-02-12

    If you just want to construct a table from constant values, used this

    values ('a','b'), ('c','d')

    The above is equivalent to a SELECT that returns two rows and can be used in other selects and joins, for example:

    select * from (values ('a','b'), ('c','d'))

    If you need the DUAL table, enable the Oracle compatibility mode:

    set database sql syntax ora true

    In this mode, the following query works fine:

    select 'a','b' from dual union select 'c','d' from dual

     
  • Fred Toussi
    Fred Toussi
    2013-03-14

    The aggregation with UNION ALL example doesn't throw an exception with the latest snapshot jar. Please try and report the result.