83 lines
2.2 KiB
Transact-SQL
83 lines
2.2 KiB
Transact-SQL
/****** Object: View [dbo].[View_CH_CheckItem] Script Date: 2022/2/24 17:24:28 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
--쇱꿎데츠玖柬暠
|
|
ALTER VIEW [dbo].[View_CH_CheckItem]
|
|
/*쇱꿎데츠玖柬暠*/
|
|
AS
|
|
SELECT
|
|
checks.ProjectId,
|
|
checks.CHT_CheckID,
|
|
isoInfo.ISO_ID,
|
|
isoInfo.ISO_IsoNo,
|
|
isoInfo.ISO_IsoNumber,
|
|
jointInfo.JOT_ID,
|
|
jointInfo.JOT_JointNo,
|
|
jointInfo.JOT_Dia,
|
|
jointInfo.JOT_Component1,
|
|
detectionType.DetectionTypeId,
|
|
detectionType.DetectionTypeCode,
|
|
detectionType.DetectionTypeName,
|
|
unit.UnitId,
|
|
unit.UnitName,
|
|
trust.CH_TrustID,
|
|
trust.CH_TrustCode,
|
|
trust.CH_TrustUnit,
|
|
trust.CH_NDTMethod,
|
|
trustItem.CH_TrustItemID,
|
|
checkItem.CHT_CheckItemID,
|
|
checkItem.CHT_RepairLocation,
|
|
checkItem.CHT_RequestDate,
|
|
checkItem.CHT_PassFilm,
|
|
checkItem.CHT_TotalFilm,
|
|
checkItem.CHT_CheckResult,
|
|
checkItem.CHT_FilmDate,
|
|
checkItem.CHT_ReportDate,
|
|
checkItem.CHT_Remark,
|
|
checkItem.Defects_Definition,
|
|
jointInfo.JOT_CheckFlag,
|
|
jointInfo.JOT_JointDesc,
|
|
jointInfo.Extend_Length,
|
|
(CASE WHEN checkItem.CHT_CheckNo is not null THEN checkItem.CHT_CheckNo ELSE isoInfo.CHT_CheckNo END) AS CHT_CheckNo,
|
|
jointInfo.JOT_CellWelder AS WelderId,
|
|
welder.WED_Code as WelderCode,
|
|
welder.WED_Name as WelderName,
|
|
checkItem.FilmSpecifications,
|
|
checkItem.DefectLength,
|
|
--CH_CheckItem.ExtendingRice,
|
|
case when isoInfo.Is_Standard=0 then
|
|
Convert(decimal(18,2),(CASE WHEN JOT_Dia IS NOT NULL THEN (JOT_Dia * 3.14) else 0 END))
|
|
else jointInfo.Extend_Length end
|
|
AS ExtendingRice,--儺낀쵠（棍쓺*┪）
|
|
'1' AS JointCount --보왯鑒
|
|
FROM dbo.PW_JointInfo as jointInfo
|
|
left join dbo.CH_TrustItem as trustItem on trustItem.JOT_ID=jointInfo.JOT_ID
|
|
left join dbo.CH_Trust as trust on trust.CH_TrustID = trustItem.CH_TrustID
|
|
LEFT JOIN dbo.CH_CheckItem as checkItem ON trustItem.CH_TrustItemID = checkItem.CH_TrustItemID
|
|
LEFT JOIN dbo.CH_Check as checks ON checks.CHT_CheckID=checkItem.CHT_CheckID
|
|
LEFT JOIN dbo.PW_IsoInfo as isoInfo ON jointInfo.ISO_ID = isoInfo.ISO_ID
|
|
left join dbo.Base_DetectionType as detectionType on trust.CH_NDTMethod=detectionType.DetectionTypeId
|
|
left join dbo.Base_Unit as unit on unit.UnitId=trust.CH_TrustUnit
|
|
left join BS_Welder as welder on welder.WED_ID=jointInfo.JOT_CellWelder
|
|
where jointInfo.JOT_TrustFlag!='00'
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
|
|
|