WITH
v_para_info AS (
select
UNNEST (
ARRAY['001']
) AS para_value
)
, v_item_tag_info AS (
SELECT
SPLIT_PART(TS3.para_value, ',', 1) AS item_tag_code
, SPLIT_PART(TS3.para_value, ',', 2) AS employee_number
FROM
v_para_info TS3
),
v_tag_info AS (
SELECT
T01.original_store_code
, T01.item_tag_code
, MAX(T01.item_tag_input_name) AS item_tag_input_name
, MAX(T01.employee_number) AS item_tag_employee_number
, MAX(T01.item_tag_type) AS item_tag_type
, MAX(T01.item_tag_name) AS item_tag_name
, MAX(T01.item_tag_usage) AS item_tag_usage
, MAX(T01.alert_send_date) AS alert_send_date
, MAX(T01.order_display_start_date) AS order_display_start_date
, MAX(T01.order_display_end_date) AS order_display_end_date
, MAX(T01.item_tag_input_datetime) AS item_tag_input_datetime
, MAX(T01.item_tag_input_person) AS item_tag_input_person
, MAX(T01.item_tag_input_person_type) AS item_tag_input_person_type
, MAX(T01.item_tag_update_datetime) AS item_tag_update_datetime
, MAX(T01.update_count) AS update_count
, MAX(T01.item_tag_priority_display_flag) AS item_tag_priority_display_flag
, COUNT(T02.item_code) AS item_quantity
, MAX(
CASE
WHEN T02.alert_cleared_flag = '0'
AND T01.order_display_end_date >= '20001919'
THEN T02.recommendation_cancel_alert_flag
ELSE '0'
END
) AS recommendation_cancel_alert_flag
, COALESCE(MAX(T03.new_arrival_flag), '0') AS new_arrival_flag
, MAX(T01.long_term_preservation_flag) AS long_term_preservation_flag
, MAX(T01.apply_flag) AS apply_flag
, MAX(T01.out_of_date_alert_flag) AS out_of_date_alert_flag
, MAX(T01.update_person_id) AS item_tag_update_person
FROM
m_item_tag T01
INNER JOIN v_item_tag_info T06
ON T01.item_tag_code = T06.item_tag_code
AND T01.employee_number = T06.employee_number
LEFT OUTER JOIN m_item_tagged T02
ON T02.original_store_code = '123456'
AND T02.original_store_code = T01.original_store_code
AND T02.item_tag_code = T01.item_tag_code
AND T02.employee_number = T01.employee_number
LEFT OUTER JOIN m_item_tag_details_per_person T03
ON T03.original_store_code = '123456'
AND T01.original_store_code = T03.original_store_code
AND T01.item_tag_code = T03.item_tag_code
AND (
(
T01.item_tag_type = '04'
AND T03.employee_number = T01.employee_number
)
OR (
T01.item_tag_type <> '04'
AND T03.employee_number = #{employeeNumber}
)
)
WHERE
T01.original_store_code = '123456'
AND (
(
T01.item_tag_type = '04'
AND T01.employee_number = '123456'
)
OR (
T01.employee_number = lpad('123456', 13, '0')
AND T01.item_tag_type IN ('01', '02', '03')
)
)
AND T01.order_display_start_date <= '20001919'
AND T01.order_display_end_date >= '20001919
AND
T01.item_tag_name LIKE CONCAT('%', '001', '%')
AND (
EXISTS (
SELECT
1
FROM
m_item_tagged T11
WHERE
T11.original_store_code = '123456'
AND T11.original_store_code = T01.original_store_code
AND T11.item_tag_code = T01.item_tag_code
AND T11.employee_number = T01.employee_number
AND T11.item_code IN ('001')
)
OR
EXISTS (
SELECT
1
FROM
m_item_tagged T11
INNER JOIN m_ia_item_by_store_all T31
ON T31.version = #{versionItemByStoreAll}
AND T31.original_store_code = #{originalStoreCode}
AND T31.original_store_code = T11.original_store_code
AND T31.system_item_code = T11.system_item_code
AND
(
(
T31.label_code_0 IN ('001')
AND T31.label_code_0 <> '0000000000000'
)
OR (
T31.label_code_1 IN ('001')
AND T31.label_code_1 <> '0000000000000'
)
OR (
T31.label_code_2 IN ('001')
AND T31.label_code_2 <> '0000000000000'
)
OR (
T31.label_code_3 IN ('001')
AND T31.label_code_3 <> '0000000000000'
)
)
WHERE
T11.original_store_code = '123456'
AND T11.original_store_code = T01.original_store_code
AND T11.item_tag_code = T01.item_tag_code
AND T11.employee_number = T01.employee_number
)
)
AND EXISTS (
SELECT
1
FROM
m_item_tagged T21
WHERE
T21.original_store_code = '123456'
AND T21.original_store_code = T01.original_store_code
AND T21.item_tag_code = T01.item_tag_code
AND T21.employee_number = T01.employee_number
AND T21.item_code IN ('001')
)
AND T01.item_tag_type IN ('001')
AND ((T01.item_tag_type = '04' AND T01.item_tag_input_person IN ('001'))
OR (T01.item_tag_type <![CDATA[ <> ]]> '04')
)
AND ((T01.item_tag_type IN ('01', '02', '03', '04') AND COALESCE(TRIM(T01.automatic_generation_type), '') = '')
OR (T01.item_tag_type = '02' AND T01.automatic_generation_type IN ('001')
)
)
AND ((T01.long_term_preservation_flag = '0' AND T01.order_display_end_date >= '20001919')
OR
T01.long_term_preservation_flag = '1'
)
AND T01.long_term_preservation_flag IN ('0', '1')
AND T01.long_term_preservation_flag = '1'
AND T01.long_term_preservation_flag = '0'
AND T01.apply_flag IN ('0', '1')
AND T01.apply_flag = '1'
AND T01.apply_flag = '0'
AND T01.order_display_end_date >= '20001919'
AND T01.order_display_start_date <= '20001919'
AND T01.item_tag_input_datetime >= '200001919'
AND T01.item_tag_input_datetime < '200001919' + '1 day' :: interval
AND EXISTS(
SELECT 1
FROM
m_ia_item_by_store_all T22
WHERE
T22.version = '0001_0101'
AND T22.original_store_code = '123456'
AND T22.original_store_code = T02.original_store_code
AND T22.cover_timezone_aggregate_flag = '1'
AND T22.system_item_code = T02.system_item_code
)
AND EXISTS(
SELECT 1
FROM
m_ia_item_by_store_all T22
WHERE
T22.version = #{versionItemByStoreAll}
AND T22.original_store_code = '123456'
AND T22.original_store_code = T02.original_store_code
AND T22.cover_timezone_aggregate_flag = '0'
AND T22.system_item_code = T02.system_item_code
)
GROUP BY
T01.original_store_code
, T01.item_tag_code
)
SELECT
T05.original_store_code
, T05.item_tag_code
, T05.item_tag_employee_number AS item_tag_employee_number
, T05.item_tag_type AS item_tag_type
, T05.item_tag_name AS item_tag_name
, T05.item_tag_usage AS item_tag_usage
, T05.alert_send_date AS alert_send_date
, T05.order_display_start_date AS order_display_start_date
, T05.order_display_end_date AS order_display_end_date
, T05.item_tag_input_datetime AS item_tag_input_datetime
, T05.item_tag_input_person AS item_tag_input_person
, CASE
WHEN T05.item_tag_input_person_type = '04'
THEN T05.item_tag_input_name
ELSE T04.display_name_1
END AS item_tag_input_person_name
, T05.item_tag_input_person_type AS item_tag_input_person_type
, T04.display_name_1 AS item_tag_input_person_type_name
, T05.item_tag_update_datetime AS item_tag_update_datetime
, T05.update_count AS update_count
, T05.item_tag_priority_display_flag AS item_tag_priority_display_flag
, T05.item_quantity AS item_quantity
, T05.recommendation_cancel_alert_flag AS recommendation_cancel_alert_flag
, COALESCE(T05.new_arrival_flag, '0') AS new_arrival_flag
, T05.long_term_preservation_flag AS long_term_preservation_flag
, T05.apply_flag AS apply_flag
, T05.out_of_date_alert_flag AS out_of_date_alert_flag
, T05.item_tag_update_person AS item_tag_update_person
, CASE
WHEN T05.item_tag_type = '04'
AND T05.item_tag_update_person = '9999999999999'
THEN T04.display_name_1
WHEN (
T05.item_tag_type IN ('02', '03')
AND T05.item_tag_update_person LIKE 'SAND%'
)
THEN T04.display_name_1
WHEN (
T05.item_tag_type IN ('02', '03')
AND T05.item_tag_update_person = '9999999999999'
)
THEN T04.display_name_3
ELSE T04.display_name_2
END AS item_tag_update_name
, CASE
WHEN '20001919' < T05.order_display_start_date
THEN '2'
WHEN '20001919' BETWEEN T05.order_display_start_date AND T05.order_display_end_date
THEN '1'
ELSE '3'
END AS sort_key
FROM
v_tag_info T05
LEFT OUTER JOIN m_common_type_detail T04
ON T04.type_id = 'TG01000002'
AND T04.setup_value_1 = T05.item_tag_input_person_type
ORDER BY
sort_key ASC
, T05.new_arrival_flag DESC
, T05.item_tag_priority_display_flag DESC
, T05.order_display_start_date DESC
, T05.item_tag_input_datetime DESC
, T05.item_tag_type ASC
, T05.item_tag_code ASC 看一下哪错了
最新发布
字面量是程序中直接使用的值，如数字或字符串。变量则用于保存这些字面量，其含义随保存的值变化。在实际开发中，通常通过变量引用字面量，以提高代码的灵活性和可读性。
扫一扫
1098
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
