Creating multiple columns in SQL-CodePudding

SELECT 
    SUM([FinalECL])   SUM([EarlyCorrections]) AS [CollectiveProvisions],
    CASE
        WHEN [Currency] = 'EUR' THEN [CollectiveProvisions]
        WHEN [Currency] = 'DKK' THEN [CollectiveProvisions]*0.13
        WHEN [Currency] = 'NOK' THEN [CollectiveProvisions]*0.1
        ELSE [CollectiveProvisions]*0.094
    END AS [CollProv_New]
FROM
    CollectiveProvision_Q22022
WHERE 
    [MONTH] IN ('202204')

But when I run the query, it shows this error message:

Invalid column name

How could I fix this bug? [FinalECL] and [EarlyCorrections] are existing columns in the CollectiveProvision_Q22022 table.

Thank you in advance!

CodePudding user response：

The invalid column is CollectiveProvisions. To re-use this calculated column you should create it in a before-statement like this:

SELECT SUM([CollectiveProvisions]) AS [CollectiveProvisions],
SUM([CollectiveProvisions] * [CurrencyFactor]) AS [CollProv_New]
FROM (
    -- here the before-statement
    SELECT SUM([FinalECL]   [EarlyCorrections]) AS [CollectiveProvisions],
    [Currency],
    CASE
        WHEN [Currency] = 'EUR' THEN 1
        WHEN [Currency] = 'DKK' THEN 0.13
        WHEN [Currency] = 'NOK' THEN 0.1
        ELSE 0.094 END AS [CurrencyFactor]
    FROM CollectiveProvision_Q22022
    WHERE [MONTH] in ('202204')
    GROUP BY [Currency])

CodePudding user response：

This might not be the best solution but it appears to be a working one:

SELECT SUM(CollProv_New) FROM (

SELECT
CASE
    WHEN Currency = 'EUR' THEN CollectiveProvisions*1.0
    WHEN Currency = 'DKK' THEN CollectiveProvisions*0.13
    WHEN Currency = 'NOK' THEN CollectiveProvisions*0.1
    ELSE CollectiveProvisions*0.094
END AS CollProv_New FROM (


SELECT SUM(FinalECL)   SUM(EarlyCorrections) AS CollectiveProvisions, Currency
FROM CollectiveProvision_Q22022
WHERE MONTH in ('202204')
GROUP BY CURRENCY


) as T ) as T2

CodePudding user response：

WITH vw_temp AS
(

SELECT 
    SUM([FinalECL])   SUM([EarlyCorrections]) AS [CollectiveProvisions],
    Currency
FROM
    CollectiveProvision_Q22022
WHERE 
    [MONTH] IN ('202204') 

GROUP BY Currency
)
 Select
    CollectiveProvisions,
    CASE
        WHEN [Currency] = 'EUR' THEN [CollectiveProvisions]
        WHEN [Currency] = 'DKK' THEN [CollectiveProvisions]*0.13
        WHEN [Currency] = 'NOK' THEN [CollectiveProvisions]*0.1
        ELSE [CollectiveProvisions]*0.094
    END AS [CollProv_New]
FROM
    vw_temp