sqldb2

Why do I get SQLCODE -206 when I run a query to calculate median value of a column?


I am trying to find median of the field "bal" based on "key" in DB2. I get this error when I execute this code "Error: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=KEY, DRIVER=3.66.46, SQLState: 42703).

Appreciate your comments !!

        SELECT
           key,
           AVG(bal)
        FROM
        (
           SELECT
              key,
              bal,
              ROW_NUMBER() OVER (
                 PARTITION BY key 
                 ORDER BY bal ASC) AS RowAsc,
              ROW_NUMBER() OVER (
                 PARTITION BY key 
                 ORDER BY bal DESC) AS RowDesc
           FROM tab1
        ) x
        WHERE 
           RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
        GROUP BY key
        ORDER BY key;

Solution

  • Ensure that every reference to key becomes "key"

    SELECT
          "key"
        , AVG(bal)
    FROM (
          SELECT
                "key"
              , bal
              , ROW_NUMBER() OVER (PARTITION BY "key"
                                   ORDER BY bal ASC)  AS rowasc
              , ROW_NUMBER() OVER (PARTITION BY "key"
                                   ORDER BY bal DESC) AS rowdesc
          FROM tab1
    ) x
    WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
    GROUP BY
          "key"
    ORDER BY
          "key"
    ;