DV Group by query returns null instead of static value

Solution Verified - Updated -

Issue

  • 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

Environment

  • 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, tools, and much more.

Current Customers and Partners

Log in for full access

Log In

New to Red Hat?

Learn more about Red Hat subscriptions

Using a Red Hat product through a public cloud?

How to access this content