locked
Expressions followed with AS [fieldname] RRS feed

  • Question

  • I have create view SELECT statement that uses a number of expressions with functions, which I then use AS to set a new unique field name. I'd like to perform another expression on the "new" field name, but in Visual Studio Express I'm getting an error message that says the new name.  Here is the code that I can't figure out:

    ISNULL(TRY_PARSE([viewUnionLegFinData].[102015actual] AS float)*[drcr],0) AS [102015],
    ISNULL(TRY_PARSE([viewUnionLegFinData].[112015actual] AS float)*[drcr],0) AS [112015],
    ([102015]+[112015]) AS [Nov2015T12]

    The last statement isn't working for me.

    Saturday, January 30, 2016 12:58 AM

Answers

  • ;with cte as (
    select ISNULL(TRY_PARSE([viewUnionLegFinData].[102015actual] AS float)*[drcr],0) AS [102015],
    ISNULL(TRY_PARSE([viewUnionLegFinData].[112015actual] AS float)*[drcr],0) AS [112015]
    
    from yourtable)
    
    select  [102015], [112015], ([102015]+[112015]) AS [Nov2015T12]
    from cte
    
    --or
    select  [102015], [112015], ([102015]+[112015]) AS [Nov2015T12]
    from  (
    select ISNULL(TRY_PARSE([viewUnionLegFinData].[102015actual] AS float)*[drcr],0) AS [102015],
    ISNULL(TRY_PARSE([viewUnionLegFinData].[112015actual] AS float)*[drcr],0) AS [112015]
    
    from yourtable) t

    • Proposed as answer by pituachMVP Saturday, January 30, 2016 9:16 AM
    • Marked as answer by John Spaid Saturday, January 30, 2016 10:58 PM
    Saturday, January 30, 2016 4:40 AM

All replies

  • ;with cte as (
    select ISNULL(TRY_PARSE([viewUnionLegFinData].[102015actual] AS float)*[drcr],0) AS [102015],
    ISNULL(TRY_PARSE([viewUnionLegFinData].[112015actual] AS float)*[drcr],0) AS [112015]
    
    from yourtable)
    
    select  [102015], [112015], ([102015]+[112015]) AS [Nov2015T12]
    from cte
    
    --or
    select  [102015], [112015], ([102015]+[112015]) AS [Nov2015T12]
    from  (
    select ISNULL(TRY_PARSE([viewUnionLegFinData].[102015actual] AS float)*[drcr],0) AS [102015],
    ISNULL(TRY_PARSE([viewUnionLegFinData].[112015actual] AS float)*[drcr],0) AS [112015]
    
    from yourtable) t

    • Proposed as answer by pituachMVP Saturday, January 30, 2016 9:16 AM
    • Marked as answer by John Spaid Saturday, January 30, 2016 10:58 PM
    Saturday, January 30, 2016 4:40 AM
  • Jingyang,

    Thank you.  I used your first suggested option and it worked.  It seems like a verbose way of going about the solution and my query runs slower than I think it should, but it worked.  Thank you again.

    Saturday, January 30, 2016 3:56 PM