SELECT
a~region , "区域编号
b~name AS zqymc, "区域名称
a~zjdjb , "节点级别
a~zjhwc , "计划完成时间
a~zsjwc , "实际完成时间
* '01' AS zyf , "月份
left( a~zsjwc, 5 ) AS zyf,
c~qzf "权重
FROM zftps0112 AS a
INNER JOIN zftpr0001 AS b ON b~code = a~region
LEFT JOIN zftps0067 AS c ON c~ndlvl = a~zjdjb AND c~dept = 'D100001399' AND c~pltyp = '10'
WHERE b~nidup = 'D100001399'
AND b~deptlevel = '280'
AND a~zjdjb IN @lrt_zjdjb
INTO CORRESPONDING FIELDS OF TABLE @lt_table.
在一次写CDS视图的时候遇到了一个问题
问题是这样子的,要取节点级别的描述,当_NDLVL有取到描述的时候,就取_NDLVL的描述,否则取_NDLVL1的描述
一开始写的是这样
case _NDLVL.ndlvlnam
when ‘ ’
then _NDLVL.ndlvlnam
else _NDLVL1.ndlvlnam
end as ndlvlnam,
这个取不出来我要的效果,因为视图里面虽然是空,但实际上不是空,似乎是null的值,改成下面这种就能自动判定_NDLVL.ndlvlnam是不是为空,不为空就取他自己,为空就取另一个
case _NDLVL.ndlvlnam
when _NDLVL.ndlvlnam
then _NDLVL.ndlvlnam
else _NDLVL1.ndlvlnam
end as ndlvlnam,
问题2,在select里面字符拼接出现超长报错超出255
原来的语句的
***区分拿地阶段的计划名称,和其他计划的计划名称
CASE
WHEN a~plphase = '01'
THEN f~land_name && c~tptypnam
ELSE b~fq_desc && c~tptypnam
END AS plnam, "计划名称
这f~land_nam 加上 c~tptypnam 的字符长度超过了255
SELECT
SINGLE
b~xm AS posid,
a~fqnum ,
a~tptyp ,
a~plphase ,
a~tpnum ,
e~tpver ,
b~dept ,
b~region ,
b~fq_desc AS fqnum_desc,
c~tptypnam, "计划类型
c~pltyp, "计划分类
d~ddtext AS plphasenam,
a~dk_posid,
f~land_name AS dk_posidnam,
'01' AS plstu, "状态
'编制' AS plstunam, "状态描述
***区分拿地阶段的计划名称,和其他计划的计划名称
CASE
WHEN a~plphase = '01'
THEN f~land_name && c~tptypnam
ELSE b~fq_desc && c~tptypnam
END AS plnam, "计划名称
1 AS plver, "版本
@lv_number AS plnum, "计划编号
'V' && @lv_datum && '_' AS plverdate, "计划日期
g~flag
FROM @lt_head AS a
LEFT JOIN zfcps_i_fqnum_all AS b ON b~fq = a~fqnum
LEFT JOIN zfcps_i_0146 AS c ON c~tptyp = a~tptyp "计划类型
LEFT JOIN dd07t AS d ON d~domvalue_l = a~plphase
AND d~domname = 'ZD_PS_PLPHASE'
INNER JOIN zftps0141 AS e ON e~tpnum = a~tpnum AND e~tpstu = '02'
LEFT JOIN zfcps_i_dk_posid AS f ON f~land_code = a~dk_posid
LEFT JOIN zftps0140 AS g ON g~dept = b~dept
AND g~pltyp = c~pltyp
AND g~region = b~region
INTO CORRESPONDING FIELDS OF @ls_planhead.
解决方法
替换成这样,字符串截取加拼接
CASE
WHEN a~plphase = '01'
THEN concat( substring( f~land_name, 1, 150 ), c~tptypnam )
ELSE b~fq_desc && c~tptypnam
END AS plnam, "计划名称