CODEWITHSUNDEEP
sqlif-statementcalculated-columns
khuetran
khuetran

Reputation: 21

Creating multiple columns in SQL

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!

Upvotes: 0

Views: 54

Answers (2)

qdoot
qdoot

Reputation: 131

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

Upvotes: 0

lx21
lx21

Reputation: 64

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])

Upvotes: 1

Related Questions