时间戳

首页 > 解决方案 > DB2：无法设置“默认”值

sql - DB2：无法设置“默认”值

问题描述

我是新手DB2。所以请多多包涵。下面是用于获取数据的查询。目的是在未找到任何内容时设置“默认”值：

询问：

SELECT t1.col1,t1.col2,t1.col3,t1.col4,t1.col5 FROM TABLE t1 FETCH FIRST 5 ROWS ONLY;

我尝试了什么：

SELECT t1.col1,t1.col2,t1.col3,t1.col4 SET DEFAULT 'Data Missing',t1.col5 FROM TABLE t1 FETCH FIRST 5 ROWS ONLY;

SELECT t1.col1,t1.col2,t1.col3,COALESCE(t1.col4 SET,'Data Missing'),t1.col5 FROM TABLE t1 FETCH FIRST 5 ROWS ONLY;

SELECT t1.col1,t1.col2,t1.col3,COALESCE(t1.col4 SET,0),t1.col5 FROM TABLE t1 FETCH FIRST 5 ROWS ONLY;

SELECT t1.col1,t1.col2,t1.col3,t1.col4 NOT NULL DEFAULT 'Data Missing',t1.col5 FROM TABLE t1 FETCH FIRST 5 ROWS ONLY;

但是，这些都不起作用。

在 MySQL 中，我可以通过以下方式实现相同的目的：

SELECT t1.col1,t1.col2,t1.col3,t1.col4 NOT NULL DEFAULT 'Data Missing', t1.col5 FROM TABLE t1 LIMIT 5;

Update_1：在下面尝试过

select col1,
case
when col4 is null then 'Data Missing' else col4 end as col4
when col3 is null then 'Data Missing' else col3 end as col3
when col2 is null then 'Data Missing' else col2 end as col2
from my_table
fetch first 5 rows only;

错误：

1) [Code: -104, SQL State: 42601]  An unexpected token "WHEN" was found following "END AS COL4
".  Expected tokens may include:  "INTO".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.22.29

2) [Code: -727, SQL State: 56098]  An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-104", SQLSTATE "42601" and message tokens "WHEN|END AS COL4

标签： sqldb2

解决方案

假设 col4 是 char 类型：

select
    col1, col2, col3, coalesce(col4, 'Data Missing') as col4
  from my_table
  fetch first 5 rows only

按摩数据的更通用解决方案可能是：

select
    col1, col2, col3,
    case
      when col4 is null then 'Data Missing'
      -- you can add more "when" cases here.
      else col4
    end as col4
  from my_table
  fetch first 5 rows only

推荐阅读