I have small table

[db2inst1@dashmpp-head-0 - Db2wh ~]$ db2 "select * from TEX"

C1                   C2        
-------------------- ----------
                1000 2000      

  1 record(s) selected.

And I am trying to use case statement with a small calculation as follows:

SELECT c1,
CASE
    WHEN c1 > 0 THEN 10
    WHEN c1 = 30 THEN 5
    ELSE 1
END AS QuantityText,
c2,
100 / QuantityText as UTILIZATION
FROM TEX;

Syntax of this is query is correct but when I run this I get:

SQL0206N  "QUANTITYTEXT" is not valid in the context where it is used.  
SQLSTATE=42703

Without calculations the following query runs fine:

[db2inst1@dashmpp-head-0 - Db2wh ~]$ db2 "SELECT c1,
> CASE
>     WHEN c1 > 0 THEN 'The quantity is greater than 30'
>     WHEN c1 = 30 THEN 'The quantity is 30'
>     ELSE 'The quantity is under 30'
> END AS QuantityText,
> c2
> FROM TEX;"

C1                   QUANTITYTEXT                    C2        
-------------------- ------------------------------- ----------
                1000 The quantity is greater than 30 2000      

  1 record(s) selected.

I am new to sql and I am not sure what am I doing wrong here. How to use QuantityText in calculation? If I hardcode the text in the query it works fine.

What I tried:

1. added 100 / @QuantityText as UTILIZATION
2. added 100 / bigint(double(QuantityText)) as CPU_UTILIZATION

    SELECT c1,
    c2,
    100 / QuantityText as UTILIZATION FROM (
    CASE
      WHEN c1 > 0 THEN 10
      WHEN c1 = 30 THEN 5
      ELSE 1
    END AS QuantityText,
    TEX);

SQL0104N  An unexpected token "CASE       WHEN" was found following 
"UTILIZATION FROM ( ".  Expected tokens may include:  "<select>".  
SQLSTATE=42601

CodePudding user response：

You can use a table expression to "name" the expression you are producing, so you can use it further in external queries.

For example:

select
  *,
  100 / QuantityText as UTILIZATION -- used here
from (
  SELECT c1,
  CASE
    WHEN c1 > 0 THEN 10
    WHEN c1 = 30 THEN 5
    ELSE 1
  END AS QuantityText, -- named here
  c2
  FROM TEX
) x