Saya memiliki kueri SQL di bawah ini di SSMS tempat saya ingin mengecualikan baris NULL yang muncul di kolom 'anggaran antarperusahaan'.

Saya telah mencoba mencari jawaban dan meletakkan klausa WHERE untuk 'Intercompany Budget' IS NOT NULL, seperti pada kueri di bawah, tetapi baris NULL masih muncul.

Ada saran? Terima kasih!

SELECT 
    ONE.PROJECT_CODE AS 'Charter Number',
    TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE) AS 'Gross Margin',
    SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) END) AS 'Intercompany Budget',
    (TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE)) - SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) ELSE 0 END) AS 'Difference',
    ((TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE)) - SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) ELSE 0 END)) / 2 AS 'Budget Adjustment Required'
FROM 
    dbo.AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW AS ONE 
LEFT JOIN 
    dbo.CFMS_Charters_FlightsAndGrossMargin_IncICFLIGHT AS TWO ON ONE.PROJECT_CODE = TWO.Charter_Number
WHERE 
    ONE.CT_DEADLINE BETWEEN '2/1/2021' AND '5/15/2021'
    AND ONE.CT_TRANTYPE= 'MSC'
    AND 'Intercompany Budget' IS NOT NULL
GROUP BY 
    ONE.PROJECT_CODE, TWO.Charter_sales_amount, TWO.Charter_Gross_Margin
HAVING 
    SUM(ONE.HOME_VALUE) <> 0
ORDER BY 
    one.PROJECT_CODE
0
PeterK 12 Mei 2021, 11:29

1 menjawab

Jawaban Terbaik

'Intercompany Budget' IS NOT NULL tidak akan pernah benar, 'Intercompany Budget' adalah string literal dan karenanya tidak memiliki nilai NULL. Ini sebenarnya salah satu alasan mengapa menggunakan string literal untuk alias adalah kebiasaan buruk, karena menyebabkan kesalahpahaman seperti ini. 'Intercompany Budget' tidak merujuk ke kolom dengan alias yang ditentukan menggunakan AS 'Intercompany Budget'; itu secara harfiah adalah string literal. Hanya dalam aliasing sintaksnya (sayangnya) diterima.

Jika Anda harus menggunakan alias yang perlu diidentifikasi batasnya, gunakan pengenal batas dialek, tanda kurung ([]) di T-SQL, atau pengenal batas ANSI, tanda kutip ganda ("). Namun, idealnya, jangan gunakan alias/nama yang memerlukan identifikasi delimit sama sekali. Saya cenderung menggunakan PascalCase (seperti yang ditunjukkan di bawah).

Adapun untuk memfilter baris, salah satu metode adalah menggunakan CTE:

WITH CTE AS(
    SELECT ONE.PROJECT_CODE AS CharterNumber,
           TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE) AS GrossMargin,
           SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) END) AS IntercompanyBudget,
           (TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE))
           - SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE)
                     ELSE 0
                 END) AS Difference,
           ((TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE))
            - SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE)
                      ELSE 0
                  END)) / 2 AS BudgetAdjustmentRequired
    FROM.dbo.AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW ONE
        LEFT JOIN.dbo.CFMS_Charters_FlightsAndGrossMargin_IncICFLIGHT TWO ON ONE.PROJECT_CODE = TWO.Charter_Number
    WHERE ONE.CT_DEADLINE BETWEEN '2/1/2021' AND '5/15/2021'
      AND ONE.CT_TRANTYPE = 'MSC'
    GROUP BY ONE.PROJECT_CODE,
             TWO.Charter_sales_amount,
             TWO.Charter_Gross_Margin
    HAVING SUM(ONE.HOME_VALUE) <> 0
    ORDER BY ONE.PROJECT_CODE)
SELECT CharterNumber,
       GrossMargin,
       IntercompanyBudget,
       Difference,
       BudgetAdjustmentRequired
FROM CTE
WHERE IntercompanyBudget IS NOT NULL;
3
Larnu 12 Mei 2021, 08:45