CODEWITHSUNDEEP
sqldb2ibm-midrange
Slime recipe
Slime recipe

Reputation: 2283

DB2 AS/400 iseries Use alias in where clause

I'm trying to use the alias of a column in the where clause. for example:

SELECT col1 AS alias1, col2 + col3 as sums 
FROM my_table
WHERE sums > 10

But then I get an error message saying:

Column sums not in specified tables.

Is there anyway I can do this?

Upvotes: 3

Views: 8319

Answers (2)

James Allman
James Allman

Reputation: 41208

The where-clause is processed before the select-clause in a statement:

The WHERE clause specifies an intermediate result table that consists of those rows of R for which the search-condition is true. R is the result of the FROM clause of the statement.

Re-write the where-clause to reference the actual column name:

...
WHERE A.lmSTAS = 'CCC'
...

A common-table-expression can be used to pre-process the select-clause. For example:

WITH A AS (SELECT 
    lmITNO AS Item_Number, 
    lmBANO AS Lot, 
    lmSTAS AS Status, 
    lmRORN AS Order_Number 
FROM MVXJDTA.MILOMA)
SELECT A.* FROM A WHERE A.Status = 'CCC'
FETCH FIRST 1000 ROWS ONLY

The columns in a CTE can also be renamed by listing them after the table-identifier. For example:

WITH A (Item_Number, Lot, Status, Order_Number)
AS (SELECT 
    lmITNO, 
    lmBANO, 
    lmSTAS, 
    lmRORN
FROM MVXJDTA.MILOMA)
SELECT A.* FROM A WHERE A.Status = 'CCC'
FETCH FIRST 1000 ROWS ONLY

Upvotes: 7

a1ex07
a1ex07

Reputation: 37382

If you really want to use alias in WHERE, not the column itself, you can do it with derived table :

SELECT a.* 
FROM
(
  SELECT lmITNO AS Item_Number, lmBANO AS Lot, lmSTAS AS Status, 
  lmRORN AS Order_Number 
  FROM MVXJDTA.MILOMA 
) a
WHERE a.Status = 'CCC'
....

Upvotes: 8

Related Questions