63 lines
2.8 KiB
MySQL
63 lines
2.8 KiB
MySQL
|
ALTER VIEW [dbo].[HJGL_View_CheckResultSummary]
|
|||
|
|
/*<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ܱ<EFBFBD>*/
|
|||
|
|
AS
|
|||
|
|
|
|||
|
|
SELECT c.*,
|
|||
|
|
(CASE WHEN CHARINDEX('20d2cbca-8b3d-434b-b1c1-181796986fa5',c.NDT)>0 THEN
|
|||
|
|
|
|||
|
|
(SELECT TOP (1) report.CH_TrustCode FROM dbo.HJGL_CH_Trust report
|
|||
|
|
WHERE report.CH_NDTMethod='20d2cbca-8b3d-434b-b1c1-181796986fa5' AND report.BatchId=c.BatchId
|
|||
|
|
ORDER BY report.CH_TrustCode)
|
|||
|
|
ELSE NULL END) AS RTReport,
|
|||
|
|
|
|||
|
|
(CASE WHEN CHARINDEX('20d2cbca-8b3d-434b-b1c1-181796986fa5',c.NDT)>0 THEN
|
|||
|
|
(SELECT TOP (1) item.States FROM dbo.HJGL_CH_TrustItem item
|
|||
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = item.CH_TrustID
|
|||
|
|
WHERE item.BatchDetailId=c.BatchDetailId AND trust.CH_NDTMethod='20d2cbca-8b3d-434b-b1c1-181796986fa5' AND item.States IN ('2','4'))
|
|||
|
|
ELSE NULL end) AS RT ,
|
|||
|
|
|
|||
|
|
|
|||
|
|
(CASE WHEN CHARINDEX('ee316c8c-9899-4ab8-8152-7f00b655243d',c.NDT)>0 THEN
|
|||
|
|
(SELECT TOP (1) report.CH_TrustCode FROM dbo.HJGL_CH_Trust report
|
|||
|
|
WHERE report.CH_NDTMethod='ee316c8c-9899-4ab8-8152-7f00b655243d' AND report.BatchId=c.BatchId
|
|||
|
|
ORDER BY report.CH_TrustCode)
|
|||
|
|
ELSE NULL END) AS UTReport,
|
|||
|
|
|
|||
|
|
(CASE WHEN CHARINDEX('ee316c8c-9899-4ab8-8152-7f00b655243d',c.NDT)>0 THEN
|
|||
|
|
(SELECT TOP (1) item.States FROM dbo.HJGL_CH_TrustItem item
|
|||
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = item.CH_TrustID
|
|||
|
|
WHERE item.BatchDetailId=c.BatchDetailId AND trust.CH_NDTMethod='ee316c8c-9899-4ab8-8152-7f00b655243d' AND item.States IN ('2','4'))
|
|||
|
|
ELSE NULL end) AS UT ,
|
|||
|
|
|
|||
|
|
(CASE WHEN CHARINDEX('6bac78b9-0e82-4885-bb8f-36486b15e545',c.NDT)>0 THEN
|
|||
|
|
(SELECT TOP (1) report.CH_TrustCode FROM dbo.HJGL_CH_Trust report
|
|||
|
|
WHERE report.CH_NDTMethod='6bac78b9-0e82-4885-bb8f-36486b15e545' AND report.BatchId=c.BatchId
|
|||
|
|
ORDER BY report.CH_TrustCode)
|
|||
|
|
ELSE NULL END) AS MTReport,
|
|||
|
|
|
|||
|
|
(CASE WHEN CHARINDEX('6bac78b9-0e82-4885-bb8f-36486b15e545',c.NDT)>0 THEN
|
|||
|
|
(SELECT TOP (1) item.States FROM dbo.HJGL_CH_TrustItem item
|
|||
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = item.CH_TrustID
|
|||
|
|
WHERE item.BatchDetailId=c.BatchDetailId AND trust.CH_NDTMethod='6bac78b9-0e82-4885-bb8f-36486b15e545' AND item.States IN ('2','4'))
|
|||
|
|
ELSE NULL end) AS MT,
|
|||
|
|
|
|||
|
|
(CASE WHEN CHARINDEX('3799c68a-895e-4ed1-b516-dbb487660832',c.NDT)>0 THEN
|
|||
|
|
(SELECT TOP (1) report.CH_TrustCode FROM dbo.HJGL_CH_Trust report
|
|||
|
|
WHERE report.CH_NDTMethod='3799c68a-895e-4ed1-b516-dbb487660832' AND report.BatchId=c.BatchId
|
|||
|
|
ORDER BY report.CH_TrustCode)
|
|||
|
|
ELSE NULL END) AS PTReport,
|
|||
|
|
|
|||
|
|
(CASE WHEN CHARINDEX('3799c68a-895e-4ed1-b516-dbb487660832',c.NDT)>0 THEN
|
|||
|
|
(SELECT TOP (1) item.States FROM dbo.HJGL_CH_TrustItem item
|
|||
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = item.CH_TrustID
|
|||
|
|
WHERE item.BatchDetailId=c.BatchDetailId AND trust.CH_NDTMethod='3799c68a-895e-4ed1-b516-dbb487660832' AND item.States IN ('2','4'))
|
|||
|
|
ELSE NULL end) AS PT
|
|||
|
|
|
|||
|
|
FROM dbo.HJGL_View_CheckResultSummary1 c
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|