<IsNotNull Property=""ZERO_FLAG"">AND T.TOTAL &gt; 0 </IsNotNull>

Status
Not open for further replies.

patrick

Well-known member
Joined
Dec 5, 2021
Messages
238
Programming Experience
1-3
I am using MS-SQL.

In Line 60 <IsNotNull Property=""ZERO_FLAG"">AND T.TOTAL &gt; 0 </IsNotNull>,

I want to replace part <IsNotNull Property ~~~~</IsNotNull>.

I want to replace part <IsNotNull Property=""ZERO_FLAG"">AND T.TOTAL &gt; 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 &gt; 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 &gt; 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:
How is this a C# question?
 
You can if it's related to C#. All you are doing here is asking us to translate some kind of XML notation for a query into SQL. Should I just go to my dentist to help me understand the diagnosis written by my podiatrist? They are both in the medical field.
 
You can if it's related to C#. All you are doing here is asking us to translate some kind of XML notation for a query into SQL. Should I just go to my dentist to help me understand the diagnosis written by my podiatrist? They are both in the medical field.

How do I delete a post? I'll delete it.
 
You can if it's related to C#. All you are doing here is asking us to translate some kind of XML notation for a query into SQL. Should I just go to my dentist to help me understand the diagnosis written by my podiatrist? They are both in the medical field.


Do you know of any SQL query forum sites that you know of?
I need an internet site to ask for SQL.
 
You actually need to read iBatis and MyBatis documentation to see how that annotated XML is translated over into SQL by iBatis/MyBatis. Here's a page that seems to explain part of what happens with those annotations:

 
Status
Not open for further replies.

Similar threads

Back
Top Bottom