SELECT *,       stock_quantity - isnull([0-30_QUANTITY],0) - isnull([30-60_QUANTITY],0) - isnull([60-90_QUANTITY],0) - isnull([90-120_QUANTITY],0) - isnull([120-180_QUANTITY],0) - isnull([180-270_QUANTITY],0) - isnull([270-365_QUANTITY],0) - isnull([1-2y_QUANTITY],0) - isnull([2-3y_QUANTITY],0) - isnull([3-4y_QUANTITY],0) - isnull([4-5y_QUANTITY],0) - isnull(over5y_QUANTITY,0)                                                                        as '9999_quantity',

       stock_value - isnull([0-30_value],0) - isnull([30-60_value],0) - isnull([60-90_value],0) - isnull([90-120_value],0) - isnull([120-180_value],0) - isnull([180-270_value],0) - isnull([270-365_value],0) - isnull([1-2y_value],0) - isnull([2-3y_value],0) - isnull([3-4y_value],0) - isnull([4-5y_value],0) - isnull(over5y_value,0)                                                                                              as '9999_value' FROM (SELECT 

       FMONTH,

       replace(ltrim(replace(PRODUCT_NUM, '0', ' ')), ' ', '0')           as PRODUCT_NUM,

       PRODUCT_NAME,

       specification,

       STOCK_UNITS,

       MATERIAL_GROUP,

       MATERIAL_GROUP_description,

       Evaluation_class,

       business_division_code,

       business_division_name,

       stock_class,

       actual_cost,

       stock_quantity,

       stock_value,

       turnover_quantity,

       [0-30_QUANTITY],

       [0-30_VALUE],

       [30-60_QUANTITY],

       [30-60_VALUE],

       [60-90_QUANTITY],

       [60-90_VALUE],

       [90-120_QUANTITY],

       [90-120_VALUE],

       [120-180_QUANTITY],

       [120-180_VALUE],

       [180-270_QUANTITY],

       [180-270_VALUE],

       [270-365_QUANTITY],

       [270-365_VALUE],

       [1-2y_QUANTITY],

       [1-2y_VALUE],

       [2-3y_QUANTITY],

       [2-3y_VALUE],

       [3-4y_QUANTITY],

       [3-4y_VALUE],

       [4-5y_QUANTITY],

       [4-5y_VALUE],

       over5y_QUANTITY,

       over5y_VALUE,

       ETL_DATE,

       E9999_QUANTITY,

       E9999_VALUE,

       CONSUME_QUANTITY,

       TURNOVER_RATE,

       TURNOVER_DAY

FROM DM_T01_STOCK_TOTAL) TAAAA

         where 1=1

${if(len(pPRODUCT_NUM) == 0,"","and PRODUCT_NUM = '" + pPRODUCT_NUM + "'")}

${if(len(DATE) == 0,"","and FMONTH = '" + DATE + "'")}

${if(len(pSTOCK_CLASS) == 0,"","and STOCK_CLASS = '" + pSTOCK_CLASS + "'")}

${if(len(pbusiness_division_name) == 0,"","and business_division_name = '" + pbusiness_division_name + "'")} 

${if(len(pEvaluation_class) == 0,"","and Evaluation_class = '" + pEvaluation_class + "'")} 

${if(JOINARRAY(GETUSERDEPARTMENTS(),"") = "IDC事业部","and Evaluation_class in('半成品','成品')","")}

${if(JOINARRAY(GETUSERDEPARTMENTS(),"") = "电力事业部","and Evaluation_class in('半成品','成品')","")}

${if(JOINARRAY(GETUSERDEPARTMENTS(),"") = "站点能源事业部","and Evaluation_class in('半成品','成品')","")}

ORDER BY PRODUCT_NUM