1

I keep getting errors when trying to execute my statement, saying the following:

ERROR [42703] [IBM][DB2/LINUXX8664] SQL0206N "HAO" is not valid in the context where it is used.

Error is referring to my filters as it works fine when commented out. See below:


SELECT
    ROUND(MINUTESDIFF(CURRENT_DATE,LAST_ACTION_DATE)/60,2) AS HAO
FROM [database]
WHERE   1=1
    AND QTY     >      0
    AND STATE   =      'Active'
    AND HAO     >      5

ORDER BY ONHOLD DESC

Can you not use aliases like "HAO" as filters? I think I've seen it done before so I must be doing something wrong, please let me know, Thanks!

1 Answers1

4

You can't create and use a field in same time in DB2 (other dbs do allow that).

You should do it this way:

SELECT HAO
FROM (
    SELECT
        ROUND(MINUTESDIFF(CURRENT_DATE,LAST_ACTION_DATE)/60,2) AS HAO,
        ONHOLD
    FROM [database]
    WHERE   1=1
        AND QTY     >      0
        AND STATE   =      'Active'
) TMP 
WHERE HAO     >      5
ORDER BY ONHOLD DESC

Or:

SELECT
    ROUND(MINUTESDIFF(CURRENT_DATE,LAST_ACTION_DATE)/60,2) AS HAO
FROM [database]
WHERE   1=1
    AND QTY     >      0
    AND STATE   =      'Active'
    AND ROUND(MINUTESDIFF(CURRENT_DATE,LAST_ACTION_DATE)/60,2) > 5
ORDER BY ONHOLD DESC
Joan Lara
  • 1,362
  • 8
  • 15
  • No databases (to the best of my knowledge) allow the use of aliases in the `WHERE` clause. That is how SQL is defined. – Gordon Linoff Mar 06 '20 at 13:44
  • 1
    @GordonLinoff Teradata do allow the use of aliases in the ```WHERE``` clause. – Joan Lara Mar 06 '20 at 13:48
  • . . I stand (well sit) correct. You are right: https://docs.teradata.com/reader/e79ET77~NzPDz~Ykinj44w/MKSYuTyx2UJWXzdHJf3~sQ. – Gordon Linoff Mar 06 '20 at 14:11
  • 1
    This is also available in Db2 when you run a query in NPS compatibility mode: https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.apdv.porting.doc/doc/r_sql_compat_where.html – Rob Wilson Mar 06 '20 at 14:23