See DDL Below

Put simply, I need to get some numeric values in a SELECT statement
and then SUM them (I realise in the example below that I don't need to
SUM them, I can just + them).  The numeric values are stored in a
related table so I've created a scalar function to get the value.  The
first SELECT statement works fine; the second barfs reporting "Invalid
column name 'Expr1'" (as well as 'Expr2' and 'Expr3')

-- First SELECT statement
SELECT
    TestID,
    dbo.fnTEST(TestDDL1) AS Expr1,
    dbo.fnTEST(TestDDL2) AS Expr2,
    dbo.fnTEST(TestDDL3) AS Expr3,
    SUM(dbo.fnTEST(TestDDL1) + dbo.fnTEST(TestDDL2) + dbo.fnTEST
(TestDDL3)) AS Expr4
FROM
    dbo.EC_TEST
GROUP BY
    TestID,
    TestDDL1,
    TestDDL2,
    TestDDL3


-- Second SELECT statement
SELECT
    TestID,
    dbo.fnTEST(TestDDL1) AS Expr1,
    dbo.fnTEST(TestDDL2) AS Expr2,
    dbo.fnTEST(TestDDL3) AS Expr3,
    SUM(Expr1 + Expr2 + Expr3) AS Expr4
FROM
    dbo.EC_TEST
GROUP BY
    TestID,
    Expr1,
    Expr2,
    Expr3

========= DDL================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EC_DDL](
    [DDLID] [int] IDENTITY(1,1) NOT NULL,
    [DDLValue] [nchar](10) NOT NULL,
    [DDLScore] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_EC_DDL] PRIMARY KEY CLUSTERED
(
    [DDLID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EC_TEST](
    [TestID] [int] IDENTITY(1,1) NOT NULL,
    [TestDDL1] [int] NULL,
    [TestDDL2] [int] NULL,
    [TestDDL3] [int] NULL,
CONSTRAINT [PK_EC_TEST] PRIMARY KEY CLUSTERED
(
    [TestID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE FUNCTION [dbo].[fnTEST]
    (
        -- Add the parameters for the function here
        @p1 int
    )
    RETURNS decimal(18,2)
    AS
    BEGIN
        -- Declare the return variable here
        DECLARE @Result decimal(18,2)

            SELECT
                @Result = IsNull(dbo.EC_DDL.DDLScore, 0)
            FROM
                dbo.EC_DDL
            WHERE
                dbo.EC_DDL.DDLID = @p1
        -- Return the result of the function
        RETURN @Result

END

Hello Edward,

Just wrap another selct around it:

select
TestID,
Expr1,
Expr2,
Expr3,
SUM(Expr1 + Expr2 + Expr3) AS Expr4
from
(
-- Second SELECT statement
SELECT
TestID,
dbo.fnTEST(TestDDL1) AS Expr1,
dbo.fnTEST(TestDDL2) AS Expr2,
dbo.fnTEST(TestDDL3) AS Expr3,
SUM(Expr1 + Expr2 + Expr3) AS Expr4
FROM
dbo.EC_TEST
) source
GROUP BY
TestID,
Expr1,
Expr2,
Expr3


Show quoteHide quotejesse.houwing at sogeti.nl

Another way to solve this is to use CROSS APPLY (SQL Server 2005/2008):

SELECT TestID,
        Expr1,
        Expr2,
        Expr3,
        SUM(Expr1 + Expr2 + Expr3) AS Expr4
FROM dbo.EC_TEST
CROSS APPLY (
SELECT dbo.fnTEST(TestDDL1) AS Expr1,
        dbo.fnTEST(TestDDL2) AS Expr2,
        dbo.fnTEST(TestDDL3) AS Expr3) AS C
GROUP BY TestID,
          Expr1,
          Expr2,
          Expr3;

ID large queries while they execute and kill
Autostatistic ON/OFF on separate tables?
Install SQL Server 2008 Questions ....
Saving images
problem with new login
Column Limits
Problem with triggers an ntext
SQLhas encountered 2 occurrence(s) of IO requests taking longer th
sql replication
SQL Server Programming Books