I am using MS-SQL.
In Line 60 <IsNotNull Property=""ZERO_FLAG"">AND T.TOTAL > 0 </IsNotNull>,
I want to replace part <IsNotNull Property ~~~~</IsNotNull>.
I want to replace part <IsNotNull Property=""ZERO_FLAG"">AND T.TOTAL > 0 </IsNotNull> with part "IS NOT NULL" MS-SQL grammar.
In C language, it seems to be expressed as follows.
if ( ZERO_FLAG != 0 )
{
T.TOTAL > 0
}
If the above expression in C language means the same as <IsNotNull Property=""ZERO_FLAG"">AND T.TOTAL > 0 </IsNotNull>
I want to switch to MS-SQL syntax it
if ( ZERO_FLAG != 0 )
{
T.TOTAL > 0
}
.
In Line 60 <IsNotNull Property=""ZERO_FLAG"">AND T.TOTAL > 0 </IsNotNull>,
I want to replace part <IsNotNull Property ~~~~</IsNotNull>.
I want to replace part <IsNotNull Property=""ZERO_FLAG"">AND T.TOTAL > 0 </IsNotNull> with part "IS NOT NULL" MS-SQL grammar.
In C language, it seems to be expressed as follows.
if ( ZERO_FLAG != 0 )
{
T.TOTAL > 0
}
If the above expression in C language means the same as <IsNotNull Property=""ZERO_FLAG"">AND T.TOTAL > 0 </IsNotNull>
I want to switch to MS-SQL syntax it
if ( ZERO_FLAG != 0 )
{
T.TOTAL > 0
}
.
C#:
ALTER PROCEDURE [dbo].[GetOEMRequestPlanList]
@PLANT_CODE varchar(20),
@LINE_CODE varchar(50),
@PLAN_DATE varchar(8),
@PRODUCT_CODE varchar(50),
@ZERO_FLAG varchar(1)
AS
BEGIN
WITH T AS (
SELECT *
FROM (
SELECT
A.PLANT_CODE,
A.PLAN_DATE,
A.PRODUCT_CODE,
B.PRODUCT_NAME,
A.ALC_CODE,
CONVERT(float, D0_T1)+CONVERT(float, D0_T2)+CONVERT(float, D0_T3)+CONVERT(float, D0_T4)+CONVERT(float, D0_T5)+
CONVERT(float, D0_T6)+CONVERT(float, D0_T7)+CONVERT(float, D0_T8)+CONVERT(float, D0_T9)+CONVERT(float, D0_T10)+
CONVERT(float, D1_T1)+CONVERT(float, D1_T2)+CONVERT(float, D1_T3)+CONVERT(float, D1_T4)+CONVERT(float, D1_T5)+
CONVERT(float, D1_T6)+CONVERT(float, D1_T7)+CONVERT(float, D1_T8)+CONVERT(float, D1_T9)+CONVERT(float, D1_T10)+
CONVERT(float, D2_T1)+CONVERT(float, D2_T2)+CONVERT(float, D2_T3)+CONVERT(float, D2_T4)+CONVERT(float, D2_T5)+
CONVERT(float, D2_T6)+CONVERT(float, D2_T7)+CONVERT(float, D2_T8)+CONVERT(float, D2_T9)+CONVERT(float, D2_T10)+
CONVERT(float, D3_T1)+CONVERT(float, D3_T2)+CONVERT(float, D3_T3)+CONVERT(float, D3_T4)+CONVERT(float, D3_T5)+
CONVERT(float, D3_T6)+CONVERT(float, D3_T7)+CONVERT(float, D3_T8)+CONVERT(float, D3_T9)+CONVERT(float, D3_T10)+
CONVERT(float, D4_T1)+CONVERT(float, D4_T2)+CONVERT(float, D4_T3)+CONVERT(float, D4_T4)+CONVERT(float, D4_T5)+
CONVERT(float, D4_T6)+CONVERT(float, D4_T7)+CONVERT(float, D4_T8)+CONVERT(float, D4_T9)+CONVERT(float, D4_T10)+
CONVERT(float, D5_TT)+CONVERT(float, D6_TT)+CONVERT(float, D7_TT)+CONVERT(float, D8_TT)+CONVERT(float, D9_TT)+
CONVERT(float, D10_TT)+CONVERT(float, D11_TT)+CONVERT(float, D12_TT)+CONVERT(float, D13_TT) AS TOTAL,
CONVERT(FLOAT, D0_TT) AS D0_TT,
CONVERT(FLOAT, D0_T1) AS D0_T1, CONVERT(FLOAT, D0_T2) AS D0_T2, CONVERT(FLOAT, D0_T3) AS D0_T3, CONVERT(FLOAT, D0_T4) AS D0_T4, CONVERT(FLOAT, D0_T5) AS D0_T5,
CONVERT(FLOAT, D0_T6) AS D0_T6, CONVERT(FLOAT, D0_T7) AS D0_T7, CONVERT(FLOAT, D0_T8) AS D0_T8, CONVERT(FLOAT, D0_T9) AS D0_T9, CONVERT(FLOAT, D0_T10) AS D0_T10,
CONVERT(FLOAT, D1_TT) AS D1_TT,
CONVERT(FLOAT, D1_T1) AS D1_T1, CONVERT(FLOAT, D1_T2) AS D1_T2, CONVERT(FLOAT, D1_T3) AS D1_T3, CONVERT(FLOAT, D1_T4) AS D1_T4, CONVERT(FLOAT, D1_T5) AS D1_T5,
CONVERT(FLOAT, D1_T6) AS D1_T6, CONVERT(FLOAT, D1_T7) AS D1_T7, CONVERT(FLOAT, D1_T8) AS D1_T8, CONVERT(FLOAT, D1_T9) AS D1_T9, CONVERT(FLOAT, D1_T10) AS D1_T10,
CONVERT(FLOAT, D2_TT) AS D2_TT,
CONVERT(FLOAT, D2_T1) AS D2_T1, CONVERT(FLOAT, D2_T2) AS D2_T2, CONVERT(FLOAT, D2_T3) AS D2_T3, CONVERT(FLOAT, D2_T4) AS D2_T4, CONVERT(FLOAT, D2_T5) AS D2_T5,
CONVERT(FLOAT, D2_T6) AS D2_T6, CONVERT(FLOAT, D2_T7) AS D2_T7, CONVERT(FLOAT, D2_T8) AS D2_T8, CONVERT(FLOAT, D2_T9) AS D2_T9, CONVERT(FLOAT, D2_T10) AS D2_T10,
CONVERT(FLOAT, D3_TT) AS D3_TT,
CONVERT(FLOAT, D3_T1) AS D3_T1, CONVERT(FLOAT, D3_T2) AS D3_T2, CONVERT(FLOAT, D3_T3) AS D3_T3, CONVERT(FLOAT, D3_T4) AS D3_T4, CONVERT(FLOAT, D3_T5) AS D3_T5,
CONVERT(FLOAT, D3_T6) AS D3_T6, CONVERT(FLOAT, D3_T7) AS D3_T7, CONVERT(FLOAT, D3_T8) AS D3_T8, CONVERT(FLOAT, D3_T9) AS D3_T9, CONVERT(FLOAT, D3_T10) AS D3_T10,
CONVERT(FLOAT, D4_TT) AS D4_TT,
CONVERT(FLOAT, D4_T1) AS D4_T1, CONVERT(FLOAT, D4_T2) AS D4_T2, CONVERT(FLOAT, D4_T3) AS D4_T3, CONVERT(FLOAT, D4_T4) AS D4_T4, CONVERT(FLOAT, D4_T5) AS D4_T5,
CONVERT(FLOAT, D4_T6) AS D4_T6, CONVERT(FLOAT, D4_T7) AS D4_T7, CONVERT(FLOAT, D4_T8) AS D4_T8, CONVERT(FLOAT, D4_T9) AS D4_T9, CONVERT(FLOAT, D4_T10) AS D4_T10,
CONVERT(FLOAT, D5_TT) AS D5_TT, CONVERT(FLOAT, D6_TT) AS D6_TT, CONVERT(FLOAT, D7_TT) AS D7_TT, CONVERT(FLOAT, D8_TT) AS D8_TT, CONVERT(FLOAT, D9_TT) AS D9_TT, CONVERT(FLOAT, D10_TT) AS D10_TT,
CONVERT(FLOAT, D11_TT) AS D11_TT, CONVERT(FLOAT, D12_TT) AS D12_TT, CONVERT(FLOAT, D13_TT) AS D13_TT, CONVERT(FLOAT, D14_TT) AS D14_TT, CONVERT(FLOAT, D15_TT) AS D15_TT,
CONVERT(FLOAT, D16_TT) AS D16_TT, CONVERT(FLOAT, D17_TT) AS D17_TT, CONVERT(FLOAT, D18_TT) AS D18_TT, CONVERT(FLOAT, D19_TT) AS D19_TT, CONVERT(FLOAT, D20_TT) AS D20_TT,
CONVERT(FLOAT, D21_TT) AS D21_TT, CONVERT(FLOAT, D22_TT) AS D22_TT, CONVERT(FLOAT, D23_TT) AS D23_TT, CONVERT(FLOAT, D24_TT) AS D24_TT, CONVERT(FLOAT, D25_TT) AS D25_TT,
CONVERT(FLOAT, D26_TT) AS D26_TT, CONVERT(FLOAT, D27_TT) AS D27_TT, CONVERT(FLOAT, D28_TT) AS D28_TT, CONVERT(FLOAT, D29_TT) AS D29_TT, CONVERT(FLOAT, D30_TT) AS D30_TT, CONVERT(FLOAT, D31_TT) AS D31_TT
FROM HI_ERP_PLAN A WITH (NOLOCK)
LEFT OUTER JOIN MA_PRODUCT B WITH (NOLOCK)
ON A.PLANT_CODE = B.PLANT_CODE AND A.PRODUCT_CODE = B.PRODUCT_CODE
WHERE A.PLANT_CODE = @PLANT_CODE
AND A.PLAN_DATE = @PLAN_DATE
AND B.PRODUCT_UDF_2 = @LINE_CODE AND A.PRODUCT_CODE = @PRODUCT_CODE
) T WHERE 1=1
<IsNotNull Property=""ZERO_FLAG"">AND T.TOTAL > 0 </IsNotNull>
)
SELECT PLANT_CODE,
PLAN_DATE,
'합계' AS PRODUCT_CODE,
'' AS PRODUCT_NAME,
'' AS ALC_CODE,
SUM(TOTAL) AS TOTAL,
SUM(D0_TT) AS D0_TT,
SUM(D0_T1) AS D0_T1, SUM(D0_T2) AS D0_T2, SUM(D0_T3) AS D0_T3, SUM(D0_T4) AS D0_T4, SUM(D0_T5) AS D0_T5,
SUM(D0_T6) AS D0_T6, SUM(D0_T7) AS D0_T7, SUM(D0_T8) AS D0_T8, SUM(D0_T9) AS D0_T9, SUM(D0_T10) AS D0_T10,
SUM(D1_TT) AS D1_TT,
SUM(D1_T1) AS D1_T1, SUM(D1_T2) AS D1_T2, SUM(D1_T3) AS D1_T3, SUM(D1_T4) AS D1_T4, SUM(D1_T5) AS D1_T5,
SUM(D1_T6) AS D1_T6, SUM(D1_T7) AS D1_T7, SUM(D1_T8) AS D1_T8, SUM(D1_T9) AS D1_T9, SUM(D1_T10) AS D1_T10,
SUM(D2_TT) AS D2_TT,
SUM(D2_T1) AS D2_T1, SUM(D2_T2) AS D2_T2, SUM(D2_T3) AS D2_T3, SUM(D2_T4) AS D2_T4, SUM(D2_T5) AS D2_T5,
SUM(D2_T6) AS D2_T6,SUM( D2_T7) AS D2_T7, SUM(D2_T8) AS D2_T8, SUM(D2_T9) AS D2_T9, SUM(D2_T10) AS D2_T10,
SUM(D3_TT) AS D3_TT,
SUM(D3_T1) AS D3_T1, SUM(D3_T2) AS D3_T2, SUM(D3_T3) AS D3_T3, SUM(D3_T4) AS D3_T4, SUM(D3_T5) AS D3_T5,
SUM(D3_T6) AS D3_T6, SUM(D3_T7) AS D3_T7, SUM(D3_T8) AS D3_T8, SUM(D3_T9) AS D3_T9, SUM(D3_T10) AS D3_T10,
SUM(D4_TT) AS D4_TT,
SUM(D4_T1) AS D4_T1, SUM(D4_T2) AS D4_T2, SUM(D4_T3) AS D4_T3, SUM(D4_T4) AS D4_T4, SUM(D4_T5) AS D4_T5,
SUM(D4_T6) AS D4_T6, SUM(D4_T7) AS D4_T7, SUM(D4_T8) AS D4_T8, SUM(D4_T9) AS D4_T9, SUM(D4_T10) AS D4_T10,
SUM(D5_TT) AS D5_TT, SUM(D6_TT) AS D6_TT, SUM(D7_TT) AS D7_TT, SUM(D8_TT) AS D8_TT, SUM(D9_TT) AS D9_TT, SUM(D10_TT) AS D10_TT,
SUM(D11_TT) AS D11_TT, SUM(D12_TT) AS D12_TT, SUM(D13_TT) AS D13_TT, SUM(D14_TT) AS D14_TT, SUM(D15_TT) AS D15_TT,
SUM(D16_TT) AS D16_TT, SUM(D17_TT) AS D17_TT, SUM(D18_TT) AS D18_TT, SUM(D19_TT) AS D19_TT, SUM(D20_TT) AS D20_TT,
SUM(D21_TT) AS D21_TT, SUM(D22_TT) AS D22_TT, SUM(D23_TT) AS D23_TT, SUM(D24_TT) AS D24_TT, SUM(D25_TT) AS D25_TT,
SUM(D26_TT) AS D26_TT, SUM(D27_TT) AS D27_TT, SUM(D28_TT) AS D28_TT, SUM(D29_TT) AS D29_TT, SUM(D30_TT) AS D30_TT, SUM(D31_TT) AS D31_TT
FROM T
GROUP BY PLANT_CODE, PLAN_DATE
UNION ALL
SELECT * FROM T
ORDER BY ALC_CODE ASC
END
Last edited by a moderator: