修正了对所选参数显示零(如果没有找到)的可能输出的列表

Question

如果以前曾问过类似的问题，请表示歉意。对于下面的代码，我希望我的报告显示“bulk_details”的所有可能选项，即使所选的“week”参数没有可用的数据。在我们的业务中，每周都会定期比较数据，因此每次运行报表时，都需要显示bulk_details中所有可能的结果。我附上了第2235周和第2236周的报告图片，其中2235的数据少于2236。

第2235周：

​

​

第2236周：

​

​

当前代码：

WITH a AS (
   SELECT   product_description,
        material_type_code,
        bulk_non_bulk,
        raw_weight_tons,
        financial_net_weight_tons,
        standard_litres,
        sterling_value,
        sterling_cost,
        purchaser_contract_ref,
        supplier_contract_ref,
        purchaser_contract_number,
        supplier_contract_number,
        purchaser_org_code,
        purchaser_org_name,
        purchaser_grouping_code,
        supplier_org_code,
        supplier_org_name,
        supplier_grouping_code,
        sector_code
  ,CASE WHEN journal_bulk_type_code = 'PURCHASE' AND journal_section_code = 'PURCHASE' THEN 'Purchase'
  WHEN journal_bulk_type_code = 'BROKER' AND journal_section_code = 'PURCHASE' THEN 'Purchase'
  WHEN journal_bulk_type_code = 'WASTEBRO' AND journal_section_code = 'PURCHASE' THEN 'Purchase'
  WHEN journal_bulk_type_code = 'SALEDES' AND journal_section_code = 'SALE' THEN 'Sale'
  WHEN journal_bulk_type_code = 'BROKER' AND journal_section_code = 'SALE' THEN 'Sale'
  WHEN journal_bulk_type_code = 'WASTEBRO' AND journal_section_code = 'SALE' THEN 'Sale'
  WHEN journal_bulk_type_code = 'SALEREC' AND journal_section_code = 'SALE' THEN 'Sale'
  WHEN journal_bulk_type_code = 'WASTEDIS' AND journal_section_code = 'PURCHASE' THEN 'Waste Disposal'
  WHEN journal_bulk_type_code = 'WASTESAL' AND journal_section_code = 'SALE' THEN 'Waste Receipt'
  WHEN journal_bulk_type_code = 'CONSUMABLE' AND journal_section_code = 'PURCHASE' THEN 'Consumable'
  ELSE 'Transfer'
  END AS bulk_type_new
  ,CASE WHEN is_internal = 'I' THEN 'STOCK'
  WHEN journal_bulk_type_code = 'BROKER' THEN 'BROKER'
  ELSE 'STOCK'
  END AS sb
  ,CASE WHEN purchaser_grouping_code = supplier_grouping_code AND sector_code = '' THEN 'INTER'
  WHEN purchaser_grouping_code = 'OLLECO' AND supplier_org_name LIKE ('OLLECO%') AND sector_code = '' THEN 'INTER'
  WHEN purchaser_grouping_code <> supplier_grouping_code AND sector_code = '' THEN '3RDPARTY'
  ELSE sector_code
  END AS    sector_new
        ,journal_business_unit_code
  FROM bi.journal_lines
  WHERE week_number = @Week
  AND material_type_code IN ('UCOP', 'UCOU', 'EFFLUENT')
  ), [data] AS (
  SELECT    product_description
        ,material_type_code
        ,bulk_non_bulk
        ,raw_weight_tons
        ,financial_net_weight_tons
        ,standard_litres
        ,sterling_value
        ,sterling_cost
        ,purchaser_contract_ref
        ,supplier_contract_ref
        ,purchaser_contract_number
        ,supplier_contract_number
        ,purchaser_org_code
        ,purchaser_org_name
        ,purchaser_grouping_code
        ,supplier_org_code
        ,supplier_org_name
        ,supplier_grouping_code
        ,sector_code
        ,bulk_type_new
        ,sb
        ,sector_new
  ,CASE WHEN bulk_type_new IN ('Sale', 'Waste Receipt') THEN purchaser_org_name
  ELSE supplier_org_name
  END AS purchaser_supplier
  ,CASE WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'UCOU' THEN 'Stock - Purchase - NB - InterCo - UCOU'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'UCOP' THEN 'Stock - Purchase - NB - InterCo - UCOP'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'EFFLUENT' THEN 'Stock - Purchase - NB - InterCo - EFFLUENT'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'UCOU' THEN 'Stock - Purchase - B - InterCo - UCOU'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'UCOP' THEN 'Stock - Purchase - B - InterCo - UCOP'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'EFFLUENT' THEN 'Stock - Purchase - B - InterCo - EFFLUENT'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'UCOU' THEN 'Stock - Purchase - NB - 3rd Party - UCOU'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'UCOP' THEN 'Stock - Purchase - NB - 3rd Party - UCOP'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'EFFLUENT' THEN 'Stock - Purchase - NB - 3rd Party - EFFLUENT'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'UCOU' THEN 'Stock - Purchase - B - 3rd Party - UCOU'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'UCOP' THEN 'Stock - Purchase - B - 3rd Party - UCOP'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'EFFLUENT' THEN 'Stock - Purchase - B - 3rd Party - EFFLUENT'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'UCOU' THEN 'Broker - Purchase - NB - InterCo - UCOU'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'UCOP' THEN 'Broker - Purchase - NB - InterCo - UCOP'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'EFFLUENT' THEN 'Broker - Purchase - NB - InterCo - EFFLUENT'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'UCOU' THEN 'Broker - Purchase - B - InterCo - UCOU'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'UCOP' THEN 'Broker - Purchase - B - InterCo - UCOP'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'EFFLUENT' THEN 'Broker - Purchase - B - InterCo - EFFLUENT'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'UCOU' THEN 'Broker - Purchase - NB - 3rd Party - UCOU'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'UCOP' THEN 'Broker - Purchase - NB - 3rd Party - UCOP'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'EFFLUENT' THEN 'Broker - Purchase - NB - 3rd Party - EFFLUENT'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'UCOU' THEN 'Broker - Purchase - B - 3rd Party - UCOU'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'UCOP' THEN 'Broker - Purchase - B - 3rd Party - UCOP'
  WHEN bulk_type_new = 'PURCHASE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'EFFLUENT' THEN 'Broker - Purchase - B - 3rd Party - EFFLUENT'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'UCOU' THEN 'Stock - Sale - NB - InterCo - UCOU'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'UCOP' THEN 'Stock - Sale - NB - InterCo - UCOP'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'EFFLUENT' THEN 'Stock - Sale - NB - InterCo - EFFLUENT'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'UCOU' THEN 'Stock - Sale - B - InterCo - UCOU'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'UCOP' THEN 'Stock - Sale - B - InterCo - UCOP'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'EFFLUENT' THEN 'Stock - Sale - B - InterCo - EFFLUENT'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'UCOU' THEN 'Stock - Sale - NB - 3rd Party - UCOU'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'UCOP' THEN 'Stock - Sale - NB - 3rd Party - UCOP'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'STOCK' AND material_type_code = 'EFFLUENT' THEN 'Stock - Sale - NB - 3rd Party - EFFLUENT'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'UCOU' THEN 'Stock - Sale - B - 3rd Party - UCOU'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'UCOP' THEN 'Stock - Sale - B - 3rd Party - UCOP'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'STOCK' AND material_type_code = 'EFFLUENT' THEN 'Stock - Sale - B - 3rd Party - EFFLUENT'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'UCOU' THEN 'Broker - Sale - NB - InterCo - UCOU'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'UCOP' THEN 'Broker - Sale - NB - InterCo - UCOP'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'EFFLUENT' THEN 'Broker - Sale - NB - InterCo - EFFLUENT'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'UCOU' THEN 'Broker - Sale - B - InterCo - UCOU'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'UCOP' THEN 'Broker - Sale - B - InterCo - UCOP'
  WHEN bulk_type_new = 'SALE' AND sector_new = 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'EFFLUENT' THEN 'Broker - Sale - B - InterCo - EFFLUENT'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'UCOU' THEN 'Broker - Sale - NB - 3rd Party - UCOU'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'UCOP' THEN 'Broker - Sale - NB - 3rd Party - UCOP'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'NB' AND sb = 'BROKER' AND material_type_code = 'EFFLUENT' THEN 'Broker - Sale - NB - 3rd Party - EFFLUENT'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'UCOU' THEN 'Broker - Sale - B - 3rd Party - UCOU'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'UCOP' THEN 'Broker - Sale - B - 3rd Party - UCOP'
  WHEN bulk_type_new = 'SALE' AND sector_new <> 'INTER' AND bulk_non_bulk = 'B' AND sb = 'BROKER' AND material_type_code = 'EFFLUENT' THEN 'Broker - Sale - B - 3rd Party - EFFLUENT'
  ELSE NULL
  END AS    bulk_details
        ,journal_business_unit_code
  FROM a
  WHERE bulk_type_new IN ('Sale', 'Purchase' ,'Transfer')
  ), [default] AS (
  SELECT DISTINCT   product_description
                ,material_type_code
                ,bulk_non_bulk
                ,0 AS raw_weight_tons
                ,0 AS financial_net_weight_tons
                ,0 AS standard_litres
                ,0 AS sterling_value
                ,0 AS sterling_cost
                ,purchaser_contract_ref
                ,supplier_contract_ref
                ,purchaser_contract_number
                ,supplier_contract_number
                ,purchaser_org_code
                ,purchaser_org_name
                ,purchaser_grouping_code
                ,supplier_org_code
                ,supplier_org_name
                ,supplier_grouping_code
                ,sector_code    
                ,bulk_type_new
                ,sb
                ,sector_new
                ,purchaser_supplier
                ,bulk_details
                ,journal_business_unit_code
  FROM [data]
  WHERE bulk_details IS NOT NULL
  )
  SELECT *
  FROM [data]
  WHERE journal_business_unit_code = @Business
  AND bulk_details IS NOT NULL
  UNION ALL
  SELECT *
  FROM [default]
  ORDER BY bulk_details

Answer 1

简而言之，您需要一个包含所有可能值的明确列表。您可能可以从现有的数据中获得这一点，但从查询的角度来看，我想您不能。

所以，创建一个新的表，从case语句的‘So’表达式中添加所有选项，这就是我假设您需要的。‘股票-购买- NB - InterCo - UCOP'，’股票-购买- NB - InterCo -废水‘等.)

然后，在您的主查询中，只需从这个表开始，然后将现有的查询加入到这个表中，所以语句的结尾类似于如下所示.

SELECT *
    FROM myNewTable x 
        LEFT JOIN (  SELECT *
                     FROM [data]
                     WHERE journal_business_unit_code = @Business
                     AND bulk_details IS NOT NULL
                     UNION ALL
                     SELECT *
                     FROM [default]) y 
        ON x.myNewListOf_bulk_type = y.bulk_details
     ORDER BY bulk_details