oracle clob不截取直接查询所有长度

H愚公移山H

于 2024-03-13 11:09:07 发布

阅读量202

点赞数 3

文章标签：数据库

本文链接：https://blog.csdn.net/HAN_789/article/details/136674487

版权

SUBSTR(RICH_TEXT_VALUE, 1, DBMS_LOB.GETLENGTH(RICH_TEXT_VALUE))

SELECT
	ITEM_ID,
	ATTR_ID,
	COUNT(*) COUUNTVALUE ,
	VALUE
FROM
	(
	SELECT
		BASE_LINE_ID,
		ITEM_ID,
		ATTR_ID,
		VALUE||NVL2(RICH_TEXT_VALUE,SUBSTR(RICH_TEXT_VALUE, 1, DBMS_LOB.GETLENGTH(RICH_TEXT_VALUE)),'')||NVL2(EXTERNAL_LINKS_VALUE,SUBSTR(EXTERNAL_LINKS_VALUE, 1, DBMS_LOB.GETLENGTH(EXTERNAL_LINKS_VALUE)),'') AS VALUE
	FROM
		SE_REQ_BASE_LINE_ITEM_VALUE SRBLIV
	WHERE
		BASE_LINE_ID = 324400
		AND (VALUE IS NOT NULL OR RICH_TEXT_VALUE IS NOT NULL OR EXTERNAL_LINKS_VALUE IS NOT NULL)
		AND ITEM_ID IN (1611034,
		16110348)
		AND ATTR_ID IN (536076)
UNION ALL
	SELECT
		BASE_LINE_ID,
		ITEM_ID,
		ATTR_ID,
		VALUE||NVL2(RICH_TEXT_VALUE,SUBSTR(RICH_TEXT_VALUE, 1, DBMS_LOB.GETLENGTH(RICH_TEXT_VALUE)),'')||NVL2(EXTERNAL_LINKS_VALUE,SUBSTR(EXTERNAL_LINKS_VALUE, 1, DBMS_LOB.GETLENGTH(EXTERNAL_LINKS_VALUE)),'') AS VALUE
	FROM
		SE_REQ_BASE_LINE_ITEM_VALUE SRBLIV
	WHERE
		BASE_LINE_ID = 324401
		AND (VALUE IS NOT NULL OR RICH_TEXT_VALUE IS NOT NULL OR EXTERNAL_LINKS_VALUE IS NOT NULL)
		AND ITEM_ID IN (1611034,
		16110348)
		AND ATTR_ID IN (536076) ) T
GROUP BY
	ATTR_ID ,
	T.ITEM_ID,
	VALUE

SELECT
	ITEM_ID,
	COUNT(1) COUNTE
FROM
	(
	SELECT
		ITEM_ID,
		CONTENT||SE_FILE_SERVER.MD5 AS CONTENT1
	FROM
		SE_REQ_BASE_LINE_ITEM_INFO SRBLII LEFT JOIN SE_FILE_SERVER SE_FILE_SERVER ON SRBLII.FILE_ID = SE_FILE_SERVER.FILEID
	WHERE
		DOCUMENT_ID =:lineIdBefore
		AND ITEM_ID IN (:lineItemIds)
UNION ALL
	SELECT
		ITEM_ID,
		CONTENT||SE_FILE_SERVER.MD5 AS CONTENT1
	FROM
		SE_REQ_BASE_LINE_ITEM_INFO SRBLII LEFT JOIN SE_FILE_SERVER SE_FILE_SERVER ON SRBLII.FILE_ID = SE_FILE_SERVER.FILEID
	WHERE
		DOCUMENT_ID =:lineIdAfter
		AND ITEM_ID IN (:lineItemIds)) E
GROUP BY
	ITEM_ID ,
	E.CONTENT1
ORDER BY
	ITEM_ID