我有小桌子
[db2inst1@dashmpp-head-0 - Db2wh ~]$ db2 "select * from TEX"
C1 C2
-------------------- ----------
1000 2000
1 record(s) selected.
我正在嘗試使用 case 陳述句進行小計算，如下所示：
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;
這是查詢的語法是正確的，但是當我運行它時，我得到：
SQL0206N "QUANTITYTEXT" is not valid in the context where it is used.
SQLSTATE=42703
沒有計算，以下查詢運行良好：
[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.
我是 sql 新手，我不確定我在這里做錯了什么。如何QuantityText在計算中使用？如果我對查詢中的文本進行硬編碼，它可以正常作業。
我嘗試了什么：
- 添加
100 / @QuantityText as UTILIZATION - 添加
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
uj5u.com熱心網友回復：
您可以使用表運算式來“命名”您正在生成的運算式，因此您可以在外部查詢中進一步使用它。
例如：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/425015.html