Xtykutl

A simple Select x, y , z from Table query is containing a "Case when" in the select.

This field is ending as "End as 'result field'".

I would like to use that 'result field' in another command as part of the same SELECT command (a Dateadd command).

SELECT

    PositionDate,

    ISIN,

    Issuer,

    FirstCouponDate,

    TermLength,

    TermUnit,

    CASE

       WHEN TermUnit = 'Years' THEN 'year'

       WHEN TermUnit = 'Months' THEN 'month'

    END AS 'TermUnitRenamed', 

    DATEADD(TermUnitRenamed, TermLength, FirstCouponDate),

FROM

    GIBDataWarehouse.dbo.vw_Positions

Two suggestions based on the information you provided: add an ELSE default value to your CASE statement; then, nest this query inside a subsequent one so you can reference your new created field.

select

    *

    , case 

        when TermUnitRenamed = 'year' then DateAdd(year, TermLength, FirstCouponDate) 

        when TermUnitRenamed = 'month' then DateAdd(month, TermLength, FirstCouponDate) 

        when TermUnitRenamed = 'day' then DateAdd(day, TermLength, FirstCouponDate) 

        end as newDate

    from (

        select

            PositionDate

            , ISIN

            , Issuer

            , FirstCouponDate

            , TermLength

            , TermUnit

            , case

                when TermUnit = 'Years' then 'year'

                when TermUnit = 'Months' then 'month'

                else 'day' -- assumes default 'day' unit

                end AS TermUnitRenamed

        from GIBDataWarehouse.dbo.vw_Positions

) x

Of course, you could just apply the CASE and DATEADD logic in original query itself and avoid nesting altogether if you don’t need TermUnitRenamed for anything else.

select

    PositionDate

    , ISIN

    , Issuer

    , FirstCouponDate

    , TermLength

    , TermUnit

    , case

        when TermUnit = 'Years' then DateAdd(year, TermLength, FirstCouponDate) 

        when TermUnit = 'Months' then DateAdd(month, TermLength, FirstCouponDate) 

        else then DateAdd(day, TermLength, FirstCouponDate) -- assumes default 'day' unit

        end AS newDate

from GIBDataWarehouse.dbo.vw_Positions