我在PostgreSQL中有一个视图,我正在使用COALESCE函数从表中提取第一个NOT NULL字段。即
SELECT
SRC.SAP_SETNODE.SUBSET_CLS,
COALESCE(
SETNODE_13.SUBSET_NAME,
SETNODE_12.SUBSET_NAME,
SETNODE_11.SUBSET_NAME,
SETNODE_10.SUBSET_NAME,
SETNODE_9.SUBSET_NAME,
SETNODE_8.SUBSET_NAME,
SETNODE_7.SUBSET_NAME,
SETNODE_6.SUBSET_NAME,
SETNODE_5.SUBSET_NAME,
SETNODE_4.SUBSET_NAME,
SETNODE_3.SUBSET_NAME,
SETNODE_2.SUBSET_NAME,
SETNODE_1.SUBSET_NAME,
SRC.SAP_SETNODE.SUBSET_NAME,
SRC.SAP_SETNODE.SET_NAME)
AS prctr1,
在我有了这个字段后,我想创建一个名为prctr2的新字段,在那里我想做一个SPLIT PART&如果是LIKE"PC%",则在prctr1字段上显示OVERLAY函数
我试图对此做一个CASE声明,大致如下:
WHEN prctr1 LIKE 'PC%' THEN split_part( overlay(prctr1 placing '00000' from 1 for 2 ),'.',1)
ELSE prctr1
但我收到一条消息,说prctr1列不存在。有办法做到这一点吗?
只需将主查询封装在外部查询中,然后执行CASE语句,如下所示:
SELECT CASE WHEN prctr1 LIKE 'PC%' THEN split_part( overlay(prctr1 placing '00000' from 1 for 2 ),'.',1)
ELSE prctr1 end as pctrl1
FROM (
SELECT
SRC.SAP_SETNODE.SUBSET_CLS,
COALESCE(
SETNODE_13.SUBSET_NAME,
SETNODE_12.SUBSET_NAME,
SETNODE_11.SUBSET_NAME,
SETNODE_10.SUBSET_NAME,
SETNODE_9.SUBSET_NAME,
SETNODE_8.SUBSET_NAME,
SETNODE_7.SUBSET_NAME,
SETNODE_6.SUBSET_NAME,
SETNODE_5.SUBSET_NAME,
SETNODE_4.SUBSET_NAME,
SETNODE_3.SUBSET_NAME,
SETNODE_2.SUBSET_NAME,
SETNODE_1.SUBSET_NAME,
SRC.SAP_SETNODE.SUBSET_NAME,
SRC.SAP_SETNODE.SET_NAME)
AS prctr1
) as foo