• 日期: 2026-04-11
  • 标签: 常规

SELECT count( 0 ) FROM ( SELECT ( CASE WHEN po.EXEC_STATUS_HIS = '2' OR po.EXEC_STATUS_HIS = '3' THEN '1' ELSE '0' END ) AS 'nurseCheck', nvl2( b.APPOINTMENT_TIME_END, '1', '0' ) AS status, a.HEALTH_NUM AS 'healthNum', a.INPAT_NUM AS 'inpatNum', to_char( nvl( AUDITOR_DATE, c.EXAM_REPORT_DATE ), 'yyyy-mm-dd HH24:mi' ) AS 'examReportDate', to_char( c.EXAM_REPORT_DATE, 'yyyy-mm-dd' ) AS 'day', to_char( c.EXAM_REPORT_DATE, 'HH24:mi' ) AS 'time', to_char( nvl( a.REQ_DATE_TIME, a.gmt_create ), 'yyyy-mm-dd HH24:mi' ) AS 'reqDateTime', a.EXAM_CLASS || nvl2( a.EXAM_SUB_CLASS, '(' || a.EXAM_SUB_CLASS || ')', '' ) AS 'examClass', c.EXAM_REPORT_NUM AS 'examReportNum', d.ITEM_CODE AS 'examItemCode', nvl( b.EXAM_ITEM_NAME, d.ITEM_NAME ) AS 'examItemName', ( SELECT DEPT_CODE FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.DEPT_ID ) AS 'deptCode', ( SELECT DEPT_NAME FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.DEPT_ID ) AS 'deptName', ( SELECT DEPT_CODE FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.WARD_DEPT_ID ) AS 'wardDeptCode', ( SELECT DEPT_NAME FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.WARD_DEPT_ID ) AS 'wardDeptName', ( SELECT DEPT_CODE FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.EXEC_DEPT_ID ) AS 'execDeptCode', ( SELECT DEPT_NAME FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.EXEC_DEPT_ID ) AS 'execDeptName', ( SELECT DEPT_CODE FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = c.REPORT_DEPT_ID ) AS 'reportDeptCode', ( SELECT DEPT_NAME FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = c.REPORT_DEPT_ID ) AS 'reportDeptName', a.REQ_NUM AS 'reqNum', b.SERIAL_NUMBER AS 'serialNumber', c.REPORT_OBJECTIVE AS 'reportObjective', c.REPORT_SUBJECTIVE AS 'reportSubjective', c.EXAM_PARA AS 'examPara', to_char( c.AUDITOR_DATE, 'yyyy-mm-dd HH24:mi' ) AS 'auditorDate', CASE

	WHEN e.HOSPITAL_ID = 'DE3AC0D2E69E2F7FE0530100007FF571' THEN
	'本院' ELSE e.HOSPITAL_NAME 
	END AS 'hospitalName',
	b.APPOINTMENT_TIME AS 'appointmentTime',
	b.APPOINTMENT_TIME_END AS 'appointmentTimeEnd',
	(
	CASE
			
		WHEN b.APPOINTMENT_TIME IS NOT NULL THEN
		'1' 
		WHEN b.APPOINTMENT_TIME IS NULL 
		AND b.APPOINTMENT_STATUS IS NULL THEN
			'4' ELSE b.APPOINTMENT_STATUS 
		END 
		) AS 'appointmentStatus',
		po.EXEC_STATUS AS 'execStatus',
	CASE
			
		WHEN bb.DATA_VALUE_REMARK = '长期医嘱' 
		AND nvl( po.NEXT_END_DATETIME, po.PLAN_END_DATETIME ) < SYSDATE THEN
			'1' ELSE '0' 
			END AS 'stopStatus',
		po.inpatorderdata_id AS 'inpatorderdataId',
	CASE
			
		WHEN d.ITEM_CODE IN (
		SELECT
			b.item_code 
		FROM
			idr.TASK_NODE_CONDITION_DETAIL a
			LEFT JOIN sysmn.sys_service_item_dict b ON a.data_code = b.item_id 
		WHERE
			CONDITION_ID = (
			SELECT
				CONDITION_ID 
			FROM
				IDR.TASK_NODE_CONDITION 
			WHERE
				TASK_NODE_ID = (
				SELECT
					a.TASK_NODE_ID 
				FROM
					IDR.TASK_NODE a 
				WHERE
					a.TASK_ID = ( SELECT task_id FROM idr.task WHERE task_name = ( SELECT config_code FROM sysmn.system_config WHERE remark = 'YZ_TASK_ID' ) AND status = '1' ) 
					AND a.NODE_ID = ( SELECT config_code FROM sysmn.system_config WHERE remark = 'BL_NODE_ID' ) 
				) 
				AND element_id = ( SELECT config_code FROM sysmn.system_config WHERE remark = 'ELEMENT_ID' ) 
			) 
		) 
		AND po.inpatorderdata_id IS NOT NULL THEN
			'Y' ELSE 'N' 
		END AS 'applicationType',
		prd.REPORT_URL AS 'reportUrl',
		c.PDF_URL AS 'pdf',
		concat( ( SELECT FLAG FROM SYSMN.SYSTEM_CONFIG WHERE CONFIG_CODE = 'IMAGE_URL' ), c.IMAGE_URL ) AS 'imageUrl',
	CASE
			
		WHEN b.EXAM_ITEM_ID IN (
		SELECT
			b.item_id 
		FROM
			sysmn.SYS_DICT_FILTER a
			INNER JOIN SYSMN.SYS_SERVICE_ITEM_DICT b ON a.RIGHT_VALUE_ID = b.ITEM_ID 
		WHERE
			a.LEFT_DICT_ID = ( SELECT c.var_value FROM sysmn.sys_var c WHERE c.var_code = 'LDI_BL' ) 
			AND a.LEFT_VALUE_ID = ( SELECT c.var_value FROM sysmn.sys_var c WHERE c.var_code = 'LVI_BL' ) 
			AND b.STOP_FLAG = '0' 
			AND b.DEL_FLAG = '0' 
		) THEN
			'1' ELSE '0' 
		END pathologyType

FROM EMR.PATIENT_EXAM a LEFT JOIN EMR.PATIENT_EXAM_ITEM b ON a.REQ_NUM = b.REQ_NUM LEFT JOIN EMR.PATIENT_EXAM_REPORT c ON b.REQ_NUM = c.REQ_NUM AND b.SERIAL_NUMBER = c.SERIAL_NUMBER LEFT JOIN SYSMN.SYS_SERVICE_ITEM_DICT d ON b.EXAM_ITEM_ID = d.ITEM_ID LEFT JOIN SYSMN.EHR_INSTITUTION_INDEX e ON a.HOSPITAL_ID = e.HOSPITAL_ID LEFT JOIN emr.patient_orders po ON po.req_num = a.req_num LEFT JOIN SYSMN.MAV_EMR_CORE_DICT bb ON po.ORDERS_CLASS_ID = bb.BASICDATAVALUE_ID LEFT JOIN EMR.PATHOLOGY_DATA pd ON pd.inpat_num = a.inpat_num AND pd.order_no = po.order_no LEFT JOIN EMR.PATHOLOGY_REPORT_DATA prd ON prd.apply_id = pd.apply_id AND prd.PATHOLOGY_REPORT_ID = c.PATEXAMREPORTDATA_ID WHERE a.DEL_FLAG = '0' AND b.DEL_FLAG = '0' AND a.INPAT_NUM = '' AND ( a.EXAM_CLASS || nvl2( a.EXAM_SUB_CLASS, '(' || a.EXAM_SUB_CLASS || ')', '' ) NOT LIKE '%营养科%' OR a.EXAM_CLASS || nvl2( a.EXAM_SUB_CLASS, '(' || a.EXAM_SUB_CLASS || ')', '' ) LIKE '%营养科检查%' ) AND a.EXAM_CLASS || nvl2( a.EXAM_SUB_CLASS, '(' || a.EXAM_SUB_CLASS || ')', '' ) NOT LIKE '%放疗%' UNION ALL SELECT ( CASE WHEN po.EXEC_STATUS_HIS = '2' OR po.EXEC_STATUS_HIS = '3' THEN '1' ELSE '0' END ) AS 'nurseCheck', nvl2( b.APPOINTMENT_TIME_END, '1', '0' ) AS status, a.HEALTH_NUM AS 'healthNum', a.INPAT_NUM AS 'inpatNum', to_char( c.EXAM_REPORT_DATE, 'yyyy-mm-dd HH24:mi' ) AS 'examReportDate', to_char( c.EXAM_REPORT_DATE, 'yyyy-mm-dd' ) AS 'day', to_char( c.EXAM_REPORT_DATE, 'HH24:mi' ) AS 'time', to_char( nvl( a.REQ_DATE_TIME, a.gmt_create ), 'yyyy-mm-dd HH24:mi' ) AS 'reqDateTime', a.EXAM_CLASS || nvl2( a.EXAM_SUB_CLASS, '(' || a.EXAM_SUB_CLASS || ')', '' ) AS 'examClass', c.EXAM_REPORT_NUM AS 'examReportNum', d.ITEM_CODE AS 'examItemCode', nvl( b.EXAM_ITEM_NAME, d.ITEM_NAME ) AS 'examItemName', ( SELECT DEPT_CODE FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.DEPT_ID ) AS 'deptCode', ( SELECT DEPT_NAME FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.DEPT_ID ) AS 'deptName', ( SELECT DEPT_CODE FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.WARD_DEPT_ID ) AS 'wardDeptCode', ( SELECT DEPT_NAME FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.WARD_DEPT_ID ) AS 'wardDeptName', ( SELECT DEPT_CODE FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.EXEC_DEPT_ID ) AS 'execDeptCode', ( SELECT DEPT_NAME FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = a.EXEC_DEPT_ID ) AS 'execDeptName', ( SELECT DEPT_CODE FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = c.REPORT_DEPT_ID ) AS 'reportDeptCode', ( SELECT DEPT_NAME FROM SYSMN.SYS_DEPT_DICT WHERE DEPT_ID = c.REPORT_DEPT_ID ) AS 'reportDeptName', a.REQ_NUM AS 'reqNum', b.SERIAL_NUMBER AS 'serialNumber', c.REPORT_OBJECTIVE AS 'reportObjective', c.REPORT_SUBJECTIVE AS 'reportSubjective', c.EXAM_PARA AS 'examPara', to_char( c.AUDITOR_DATE, 'yyyy-mm-dd HH24:mi' ) AS 'auditorDate', CASE

	WHEN f.HOSPITAL_ID = 'DE3AC0D2E69E2F7FE0530100007FF571' THEN
	'本院' ELSE f.HOSPITAL_NAME 
	END AS 'hospitalName',
	b.APPOINTMENT_TIME AS 'appointmentTime',
	b.APPOINTMENT_TIME_END AS 'appointmentTimeEnd',
	(
	CASE
			
		WHEN b.APPOINTMENT_TIME IS NOT NULL THEN
		'1' 
		WHEN b.APPOINTMENT_TIME IS NULL 
		AND b.APPOINTMENT_STATUS IS NULL THEN
			'4' ELSE b.APPOINTMENT_STATUS 
		END 
		) AS 'appointmentStatus',
		po.EXEC_STATUS AS 'execStatus',
	CASE
			
		WHEN bb.DATA_VALUE_REMARK = '长期医嘱' 
		AND nvl( po.NEXT_END_DATETIME, po.PLAN_END_DATETIME ) < SYSDATE THEN
			'1' ELSE '0' 
			END AS 'stopStatus',
		po.inpatorderdata_id AS 'inpatorderdataId',
	CASE
			
		WHEN d.ITEM_CODE IN (
		SELECT
			b.item_code 
		FROM
			idr.TASK_NODE_CONDITION_DETAIL a
			LEFT JOIN sysmn.sys_service_item_dict b ON a.data_code = b.item_id 
		WHERE
			CONDITION_ID = (
			SELECT
				CONDITION_ID 
			FROM
				IDR.TASK_NODE_CONDITION 
			WHERE
				TASK_NODE_ID = (
				SELECT
					a.TASK_NODE_ID 
				FROM
					IDR.TASK_NODE a 
				WHERE
					a.TASK_ID = ( SELECT task_id FROM idr.task WHERE task_name = ( SELECT config_code FROM sysmn.system_config WHERE remark = 'YZ_TASK_ID' ) AND status = '1' ) 
					AND a.NODE_ID = ( SELECT config_code FROM sysmn.system_config WHERE remark = 'BL_NODE_ID' ) 
				) 
				AND element_id = ( SELECT config_code FROM sysmn.system_config WHERE remark = 'ELEMENT_ID' ) 
			) 
		) 
		AND po.inpatorderdata_id IS NOT NULL THEN
			'Y' ELSE 'N' 
		END AS 'applicationType',
		prd.REPORT_URL AS 'reportUrl',
		c.PDF_URL AS 'pdf',
		c.c.IMAGE_URL AS 'imageUrl' 
FROM
	EMR.PATIENT_EXAM a
	LEFT JOIN EMR.PATIENT_EXAM_ITEM b ON a.REQ_NUM = b.REQ_NUM
	LEFT JOIN EMR.PATIENT_EXAM_REPORT c ON b.REQ_NUM = c.REQ_NUM 
	AND b.SERIAL_NUMBER = c.SERIAL_NUMBER
	LEFT JOIN SYSMN.SYS_SERVICE_ITEM_DICT d ON b.EXAM_ITEM_ID = d.ITEM_ID
	LEFT JOIN SYSMN.SYS_PRIMARY_DATA_VALUE e ON a.PAT_TYPE_ID = e.DATAVALUE_ID
	LEFT JOIN SYSMN.EHR_INSTITUTION_INDEX f ON a.HOSPITAL_ID = f.HOSPITAL_ID
	LEFT JOIN emr.patient_orders po ON po.req_num = a.req_num
	LEFT JOIN SYSMN.MAV_EMR_CORE_DICT bb ON po.ORDERS_CLASS_ID = bb.BASICDATAVALUE_ID
	LEFT JOIN EMR.PATHOLOGY_DATA pd ON pd.inpat_num = a.inpat_num 
	AND pd.order_no = po.order_no
	LEFT JOIN EMR.PATHOLOGY_REPORT_DATA prd ON prd.apply_id = pd.apply_id 
	AND prd.PATHOLOGY_REPORT_ID = c.PATEXAMREPORTDATA_ID 
WHERE
	a.DEL_FLAG = '0' 
	AND b.DEL_FLAG = '0' 
	AND (
		a.EXAM_CLASS || nvl2( a.EXAM_SUB_CLASS, '(' || a.EXAM_SUB_CLASS || ')', '' ) NOT LIKE '%营养科%' 
		OR a.EXAM_CLASS || nvl2( a.EXAM_SUB_CLASS, '(' || a.EXAM_SUB_CLASS || ')', '' ) LIKE '%营养科检查%' 
	) 
	AND a.EXAM_CLASS || nvl2( a.EXAM_SUB_CLASS, '(' || a.EXAM_SUB_CLASS || ')', '' ) NOT LIKE '%放疗%' 
	AND a.HEALTH_NUM IN ( '1000001223', '1000001223' ) 
AND e.DATA_VALUE != '3' 
)   查询具有不正确的数据列   哪里可能会发生   中文回答内容：根据给出的SQL查询语句，存在以下可能导致不正确的数据列的情况：

  1. 列名错误：在查询中，可能存在列名拼写错误或与表中的列名不匹配的情况。这可能导致查询结果不正确。

  2. 表连接错误：在查询中，可能存在表连接条件错误或遗漏表连接条件的情况。这可能导致查询结果不正确。

  3. 数据类型不匹配：在查询中，可能存在数据类型不匹配的情况，例如将字符型数据与数字型数据进行运算或比较。这可能导致查询结果不正确。

  4. 条件逻辑错误：在查询中，可能存在条件逻辑错误的情况，例如使用错误的条件运算符或逻辑运算符。这可能导致查询结果不正确。

  5. 数据过滤错误：在查询中，可能存在数据过滤条件错误或遗漏数据过滤条件的情况。这可能导致查询结果不正确。

为了确定具体的错误原因，可以进一步检查查询语句中的表结构、列名、表连接条件、数据类型和条件逻辑等方面是否正确，并根据具体情况进行调整。

查询所有患者的检验信息

原文地址: https://www.cveoy.top/t/topic/qAk1 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录