sqldb2db2-zos

DB2 Unable to select distinct column values from inner query

I have a simple DB2 SQL query:

SELECT DISTINCT a FROM (SELECT a, b FROM schema.table WHERE APPR_STATUS = 'Approved');

When I try sample queries like this over any table on a DB2 DB inside DBeaver, I get the following error:

SQL Error [56038]: ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=4.13.127

When I looked up for that particular SQLCODE in DB2 support sites, it mentioned this happens because the query won't run, though they don't specify why it won't. Can someone help me figure out why this query won't run?

Solution

  • You are missing the table expression alias as well:

    SELECT DISTINCT a FROM (
  SELECT a, b FROM schema.table WHERE APPR_STATUS = 'Approved'
) x

    See that x I added at the end?