DV Group by query returns null instead of static value

Solution Verified - Updated -


  • Running a query that has a NVL check on null values to return a static value returns the null when a GROUP BY clause is used


  • Red Hat JBoss Data Virtualization (DV) 6.3.8
  • View transformation:
SELECT  col1, col2, NVL(col3, 'Unspecified') AS col3, NVL(col4, 'Unspecified') AS col4 FROM mytable1
  • Selecting from table returns correctly:
SELECT col1, col2, col3, col4 from mytable2
col1    col2        col3        col4
1   John Doe    Unspecified Unspecified
2   Jane Doe    Unspecified Unspecified
3   Jim Smith   Unspecified Unspecified
4   Jane Smith  Unspecified Unspecified
  • Static value is ignored with GROUP BY:
SELECT  col1, col2, col3, col4 FROM mytable2 GROUP BY col1,col2,col3,col4
col1    col2        col3        col4
1   John Doe    <null>      <null>
2   Jane Doe    <null>      <null>
3   Jim Smith   <null>      <null>
4   Jane Smith  <null>      <null>

Subscriber exclusive content

A Red Hat subscription provides unlimited access to our knowledgebase of over 48,000 articles and solutions.

Current Customers and Partners

Log in for full access

Log In