I am using MS-SQL.
I'm trying to replace dbo.UDF_DATETIME_TO_VARCHAR8() with a substitutable function.
I do not know what kind of result this function(dbo.UDF_DATETIME_TO_VARCHAR8()) Outputs.
dbo.UDF_DATETIME_TO_VARCHAR8() does not work in MSSQL Server Mannagement 18.
dbo.UDF_DATETIME_TO_VARCHAR8() is not recognized in the DB of MSSQL Server Mannagement 18 and an error occurs.
So, I didn't know exactly, so I tried substituting DBO.Get_DateTime().
As a result, dbo.UDF_DATETIME_TO_VARCHAR8() cannot be replaced with DBO.Get_DateTime().
In the code below,
the code in problem is a on line 143 ( dbo.UDF_DATETIME_TO_VARCHAR8(DT) )
the code in problem is a on line 12 ( dbo.UDF_VARCHAR14_TO_DATETIME(@WORK_DATE) )
An error is occurring in line 12 and line 143.
Is there a function in DBO that can replace dbo.UDF_DATETIME_TO_VARCHAR8 and dbo.UDF_VARCHAR14_TO_DATETIME ?
I'm trying to replace dbo.UDF_DATETIME_TO_VARCHAR8() with a substitutable function.
I do not know what kind of result this function(dbo.UDF_DATETIME_TO_VARCHAR8()) Outputs.
dbo.UDF_DATETIME_TO_VARCHAR8() does not work in MSSQL Server Mannagement 18.
dbo.UDF_DATETIME_TO_VARCHAR8() is not recognized in the DB of MSSQL Server Mannagement 18 and an error occurs.
So, I didn't know exactly, so I tried substituting DBO.Get_DateTime().
As a result, dbo.UDF_DATETIME_TO_VARCHAR8() cannot be replaced with DBO.Get_DateTime().
In the code below,
the code in problem is a on line 143 ( dbo.UDF_DATETIME_TO_VARCHAR8(DT) )
the code in problem is a on line 12 ( dbo.UDF_VARCHAR14_TO_DATETIME(@WORK_DATE) )
An error is occurring in line 12 and line 143.
Is there a function in DBO that can replace dbo.UDF_DATETIME_TO_VARCHAR8 and dbo.UDF_VARCHAR14_TO_DATETIME ?
C#:
ALTER PROCEDURE [dbo].[SetOEMRequest]
@PLANT_CODE varchar(20),
@SHOP_CD varchar(3),
@USERID varchar(8),
@PLAN_DATE varchar(14),
@WORK_DATE varchar(14),
@LINE_CODE varchar(14)
AS
BEGIN
WITH PT AS (
SELECT 1 AS LV, dbo.UDF_VARCHAR14_TO_DATETIME(@WORK_DATE) AS DT
UNION ALL
SELECT LV+1, DT+1 AS DT
FROM PT
WHERE LV < 11
)
SELECT
A.PLANT_CODE,
A.LINE_CODE,
@WORK_DATE AS PLAN_DATE,
CONVERT(INT, ROUND(((LEN(T1)-LEN(REPLACE(T1,'1',''))) /60.0) * B.UPH, 0)) AS T1_CAPA,
CONVERT(INT, ROUND(((LEN(T2)-LEN(REPLACE(T2,'1','')))/60.0) * B.UPH, 0)) AS T2_CAPA,
CONVERT(INT, ROUND(((LEN(T3)-LEN(REPLACE(T3,'1','')))/60.0) * B.UPH, 0)) AS T3_CAPA,
CONVERT(INT, ROUND(((LEN(T4)-LEN(REPLACE(T4,'1','')))/60.0) * B.UPH, 0)) AS T4_CAPA,
CONVERT(INT, ROUND(((LEN(T5)-LEN(REPLACE(T5,'1','')))/60.0) * B.UPH, 0)) AS T5_CAPA,
CONVERT(INT, ROUND(((LEN(T6)-LEN(REPLACE(T6,'1','')))/60.0) * B.UPH, 0)) AS T6_CAPA,
CONVERT(INT, ROUND(((LEN(T7)-LEN(REPLACE(T7,'1','')))/60.0) * B.UPH, 0)) AS T7_CAPA,
CONVERT(INT, ROUND(((LEN(T8)-LEN(REPLACE(T8,'1','')))/60.0) * B.UPH, 0)) AS T8_CAPA,
CONVERT(INT, ROUND(((LEN(T9)-LEN(REPLACE(T9,'1','')))/60.0) * B.UPH, 0)) AS T9_CAPA,
CONVERT(INT, ROUND(((LEN(T10)-LEN(REPLACE(T10,'1',''))) /60.0) * B.UPH, 0)) AS T10_CAPA,
CONVERT(INT, ROUND(((LEN(T11)-LEN(REPLACE(T11,'1',''))) /60.0) * B.UPH, 0)) AS T11_CAPA, /* D1 T1 */
CONVERT(INT, ROUND(((LEN(T12)-LEN(REPLACE(T12,'1',''))) /60.0) * B.UPH, 0)) AS T12_CAPA, /* D1 T2 */
CONVERT(INT, ROUND(((LEN(T13)-LEN(REPLACE(T13,'1',''))) /60.0) * B.UPH, 0)) AS T13_CAPA, /* D1 T3 */
CONVERT(INT, ROUND(((LEN(T14)-LEN(REPLACE(T14,'1',''))) /60.0) * B.UPH, 0)) AS T14_CAPA, /* D1 T4 */
CONVERT(INT, ROUND(((LEN(T15)-LEN(REPLACE(T15,'1',''))) /60.0) * B.UPH, 0)) AS T15_CAPA, /* D1 T5 */
CONVERT(INT, ROUND(((LEN(T16)-LEN(REPLACE(T16,'1',''))) /60.0) * B.UPH, 0)) AS T16_CAPA, /* D1 T6 */
CONVERT(INT, ROUND(((LEN(T17)-LEN(REPLACE(T17,'1',''))) /60.0) * B.UPH, 0)) AS T17_CAPA, /* D1 T7 */
CONVERT(INT, ROUND(((LEN(T18)-LEN(REPLACE(T18,'1',''))) /60.0) * B.UPH, 0)) AS T18_CAPA, /* D1 T8 */
CONVERT(INT, ROUND(((LEN(T19)-LEN(REPLACE(T19,'1',''))) /60.0) * B.UPH, 0)) AS T19_CAPA, /* D1 T9 */
CONVERT(INT, ROUND(((LEN(T20)-LEN(REPLACE(T20,'1',''))) /60.0) * B.UPH, 0)) AS T20_CAPA, /* D1 T10 */
CONVERT(INT, ROUND(((LEN(T21)-LEN(REPLACE(T21,'1',''))) /60.0) * B.UPH, 0)) AS T21_CAPA, /* D2 T1 */
CONVERT(INT, ROUND(((LEN(T22)-LEN(REPLACE(T22,'1',''))) /60.0) * B.UPH, 0)) AS T22_CAPA, /* D2 T2 */
CONVERT(INT, ROUND(((LEN(T23)-LEN(REPLACE(T23,'1',''))) /60.0) * B.UPH, 0)) AS T23_CAPA, /* D2 T3 */
CONVERT(INT, ROUND(((LEN(T24)-LEN(REPLACE(T24,'1',''))) /60.0) * B.UPH, 0)) AS T24_CAPA, /* D2 T4 */
CONVERT(INT, ROUND(((LEN(T25)-LEN(REPLACE(T25,'1',''))) /60.0) * B.UPH, 0)) AS T25_CAPA, /* D2 T5 */
CONVERT(INT, ROUND(((LEN(T26)-LEN(REPLACE(T26,'1',''))) /60.0) * B.UPH, 0)) AS T26_CAPA, /* D2 T6 */
CONVERT(INT, ROUND(((LEN(T27)-LEN(REPLACE(T27,'1',''))) /60.0) * B.UPH, 0)) AS T27_CAPA, /* D2 T7 */
CONVERT(INT, ROUND(((LEN(T28)-LEN(REPLACE(T28,'1',''))) /60.0) * B.UPH, 0)) AS T28_CAPA, /* D2 T8 */
CONVERT(INT, ROUND(((LEN(T29)-LEN(REPLACE(T29,'1',''))) /60.0) * B.UPH, 0)) AS T29_CAPA, /* D2 T9 */
CONVERT(INT, ROUND(((LEN(T30)-LEN(REPLACE(T30,'1',''))) /60.0) * B.UPH, 0)) AS T30_CAPA, /* D2 T10 */
CONVERT(INT, ROUND(((LEN(T31)-LEN(REPLACE(T31,'1',''))) /60.0) * B.UPH, 0)) AS T31_CAPA, /* D3 T1 */
CONVERT(INT, ROUND(((LEN(T32)-LEN(REPLACE(T32,'1',''))) /60.0) * B.UPH, 0)) AS T32_CAPA, /* D3 T2 */
CONVERT(INT, ROUND(((LEN(T33)-LEN(REPLACE(T33,'1',''))) /60.0) * B.UPH, 0)) AS T33_CAPA, /* D3 T3 */
CONVERT(INT, ROUND(((LEN(T34)-LEN(REPLACE(T34,'1',''))) /60.0) * B.UPH, 0)) AS T34_CAPA, /* D3 T4 */
CONVERT(INT, ROUND(((LEN(T35)-LEN(REPLACE(T35,'1',''))) /60.0) * B.UPH, 0)) AS T35_CAPA, /* D3 T5 */
CONVERT(INT, ROUND(((LEN(T36)-LEN(REPLACE(T36,'1',''))) /60.0) * B.UPH, 0)) AS T36_CAPA, /* D3 T6 */
CONVERT(INT, ROUND(((LEN(T37)-LEN(REPLACE(T37,'1',''))) /60.0) * B.UPH, 0)) AS T37_CAPA, /* D3 T7 */
CONVERT(INT, ROUND(((LEN(T38)-LEN(REPLACE(T38,'1',''))) /60.0) * B.UPH, 0)) AS T38_CAPA, /* D3 T8 */
CONVERT(INT, ROUND(((LEN(T39)-LEN(REPLACE(T39,'1',''))) /60.0) * B.UPH, 0)) AS T39_CAPA, /* D3 T9 */
CONVERT(INT, ROUND(((LEN(T40)-LEN(REPLACE(T40,'1',''))) /60.0) * B.UPH, 0)) AS T40_CAPA, /* D3 T10 */
CONVERT(INT, ROUND(((LEN(T41)-LEN(REPLACE(T41,'1',''))) /60.0) * B.UPH, 0)) AS T41_CAPA, /* D4 T1 */
CONVERT(INT, ROUND(((LEN(T42)-LEN(REPLACE(T42,'1',''))) /60.0) * B.UPH, 0)) AS T42_CAPA, /* D4 T2 */
CONVERT(INT, ROUND(((LEN(T43)-LEN(REPLACE(T43,'1',''))) /60.0) * B.UPH, 0)) AS T43_CAPA, /* D4 T3 */
CONVERT(INT, ROUND(((LEN(T44)-LEN(REPLACE(T44,'1',''))) /60.0) * B.UPH, 0)) AS T44_CAPA, /* D4 T4 */
CONVERT(INT, ROUND(((LEN(T45)-LEN(REPLACE(T45,'1',''))) /60.0) * B.UPH, 0)) AS T45_CAPA, /* D4 T5 */
CONVERT(INT, ROUND(((LEN(T46)-LEN(REPLACE(T46,'1',''))) /60.0) * B.UPH, 0)) AS T46_CAPA, /* D4 T6 */
CONVERT(INT, ROUND(((LEN(T47)-LEN(REPLACE(T47,'1',''))) /60.0) * B.UPH, 0)) AS T47_CAPA, /* D4 T7 */
CONVERT(INT, ROUND(((LEN(T48)-LEN(REPLACE(T48,'1',''))) /60.0) * B.UPH, 0)) AS T48_CAPA, /* D4 T8 */
CONVERT(INT, ROUND(((LEN(T49)-LEN(REPLACE(T49,'1',''))) /60.0) * B.UPH, 0)) AS T49_CAPA, /* D4 T9 */
CONVERT(INT, ROUND(((LEN(T50)-LEN(REPLACE(T50,'1',''))) /60.0) * B.UPH, 0)) AS T50_CAPA, /* D4 T10 */
CONVERT(INT, ROUND(((LEN(D5_TT)-LEN(REPLACE(D5_TT,'1',''))) /60.0) * B.UPH, 0)) AS T51_CAPA, /* D5 TT */
CONVERT(INT, ROUND(((LEN(D6_TT)-LEN(REPLACE(D6_TT,'1',''))) /60.0) * B.UPH, 0)) AS T52_CAPA, /* D6 TT */
CONVERT(INT, ROUND(((LEN(D7_TT)-LEN(REPLACE(D7_TT,'1',''))) /60.0) * B.UPH, 0)) AS T53_CAPA, /* D7 TT */
CONVERT(INT, ROUND(((LEN(D8_TT)-LEN(REPLACE(D8_TT,'1',''))) /60.0) * B.UPH, 0)) AS T54_CAPA, /* D8 TT */
CONVERT(INT, ROUND(((LEN(D9_TT)-LEN(REPLACE(D9_TT,'1',''))) /60.0) * B.UPH, 0)) AS T55_CAPA /* D9 TT */
FROM (
SELECT
PLANT_CODE,
LINE_CODE,
SUBSTRING([1],1, 120) AS T1, /* 08~10 */
SUBSTRING([1],121, 120) AS T2, /*10~12 */
SUBSTRING([1],301, 120) AS T3, /* 13~15 */
SUBSTRING([1],421, 120) AS T4, /* 15~17 */
SUBSTRING([1],541, 120) AS T5, /* 17~19 */
SUBSTRING([1],721, 120) AS T6, /* 20~ 22 */
SUBSTRING([1],841, 120) AS T7, /* 22~24 */
SUBSTRING([1],1021, 120) AS T8, /* 01~03 */
SUBSTRING([1],1141, 120) AS T9, /* 03~05 */
SUBSTRING([1],1261, 120) AS T10, /* 05~07 */
/* D+1 */
SUBSTRING([2],1, 120) AS T11, /* 08~10 */
SUBSTRING([2],121, 120) AS T12, /*10~12 */
SUBSTRING([2],301, 120) AS T13, /* 13~15 */
SUBSTRING([2],421, 120) AS T14, /* 15~17 */
SUBSTRING([2],541, 120) AS T15, /* 17~19 */
SUBSTRING([2],721, 120) AS T16, /* 20~ 22 */
SUBSTRING([2],841, 120) AS T17, /* 22~24 */
SUBSTRING([2],1021, 120) AS T18, /* 01~03 */
SUBSTRING([2],1141, 120) AS T19, /* 03~05 */
SUBSTRING([2],1261, 120) AS T20, /* 05~07 */
/* D+2 */
SUBSTRING([3],1, 120) AS T21, /* 08~10 */
SUBSTRING([3],121, 120) AS T22, /*10~12 */
SUBSTRING([3],301, 120) AS T23, /* 13~15 */
SUBSTRING([3],421, 120) AS T24, /* 15~17 */
SUBSTRING([3],541, 120) AS T25, /* 17~19 */
SUBSTRING([3],721, 120) AS T26, /* 20~ 22 */
SUBSTRING([3],841, 120) AS T27, /* 22~24 */
SUBSTRING([3],1021, 120) AS T28, /* 01~03 */
SUBSTRING([3],1141, 120) AS T29, /* 03~05 */
SUBSTRING([3],1261, 120) AS T30, /* 05~07 */
/* D+3 */
SUBSTRING([4],1, 120) AS T31, /* 08~10 */
SUBSTRING([4],121, 120) AS T32, /*10~12 */
SUBSTRING([4],301, 120) AS T33, /* 13~15 */
SUBSTRING([4],421, 120) AS T34, /* 15~17 */
SUBSTRING([4],541, 120) AS T35, /* 17~19 */
SUBSTRING([4],721, 120) AS T36, /* 20~ 22 */
SUBSTRING([4],841, 120) AS T37, /* 22~24 */
SUBSTRING([4],1021, 120) AS T38, /* 01~03 */
SUBSTRING([4],1141, 120) AS T39, /* 03~05 */
SUBSTRING([4],1261, 120) AS T40, /* 05~07 */
/* D+4 */
SUBSTRING([5],1, 120) AS T41, /* 08~10 */
SUBSTRING([5],121, 120) AS T42, /*10~12 */
SUBSTRING([5],301, 120) AS T43, /* 13~15 */
SUBSTRING([5],421, 120) AS T44, /* 15~17 */
SUBSTRING([5],541, 120) AS T45, /* 17~19 */
SUBSTRING([5],721, 120) AS T46, /* 20~ 22 */
SUBSTRING([5],841, 120) AS T47, /* 22~24 */
SUBSTRING([5],1021, 120) AS T48, /* 01~03 */
SUBSTRING([5],1141, 120) AS T49, /* 03~05 */
SUBSTRING([5],1261, 120) AS T50, /* 05~07 */
[6] AS D5_TT, /* D+5 DAY */
[7] AS D6_TT, /* D+6 DAY */
[8] AS D7_TT, /* D+7 DAY */
[9] AS D8_TT, /* D+8 DAY */
[10] AS D9_TT /* D+9 DAY */
FROM (
SELECT A.LV,
ISNULL(B.PLANT_CODE, @PLANT_CODE) AS PLANT_CODE,
ISNULL(B.LINE_CODE, @LINE_CODE) AS LINE_CODE,
ISNULL(B.PATTERN, '0000000000') AS PATTERN
FROM (
SELECT LV, dbo.UDF_DATETIME_TO_VARCHAR8(DT) AS WORK_DATE
FROM PT
) A LEFT OUTER JOIN (
SELECT * FROM ST_PATTERN WITH (NOLOCK)
WHERE PLANT_CODE = @PLANT_CODE AND LINE_CODE = @LINE_CODE
) B ON A.WORK_DATE = B.WORK_DATE
) A PIVOT (
MAX(PATTERN) FOR LV IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) AS PVT
) A
INNER JOIN (
SELECT PLANT_CODE, KEY_1 AS LINE_CODE, DATA_1 AS UPH
FROM MA_CODE_DATA WITH (NOLOCK)
WHERE CODE_TABLE_NAME = 'CM_UPH'
) B ON A.PLANT_CODE = B.PLANT_CODE AND A.LINE_CODE = B.LINE_CODE
Last edited by a moderator:
