Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SELECT 
(SUBSTRING(Column1,1,3)) AS ComputedColumn
WHERE ComputedColumn !="dec"
INTO Table2
FROM Table1;

This is an example where I have a computed column (which is essentially created by using an expression). If I run this, I get a "column name invalid" error, because I make a reference to the computed column in the same query where I created it.

In an application called SAS, this is solved by prefixing the computed column name in the WHERE clause with the "(CALCULATED)" keyword. Is there a similar solution in Sql Server. Other than doing this:

SELECT 
(SUBSTRING(Column1,1,3)) AS ComputedColumn
WHERE (SUBSTRING(Column1,1,3)) !="dec"
INTO Table2
FROM Table1;

Which I know it is possible but very confusing when the expression is very huge. There must be another way to REFERENCE A NEWLY CREATED COMPUTED COLUMN in the same query where we create it.

Thanks a billion! Anna

share|improve this question
    
When we're talking about a LEFT, what is the value of trying to reference the alias? Why not just WHERE Column1 NOT LIKE 'dec%'? –  Aaron Bertrand Feb 28 '12 at 4:43

3 Answers 3

You can use CROSS APPLY for this:

SELECT ComputedColumn 
INTO Table2 
FROM Table1
CROSS APPLY (SELECT SUBSTRING(Column1,1,3)) CxA(ComputedColumn)
WHERE ComputedColumn !="dec" 
share|improve this answer

Just wrap it up in a derived table:

SELECT *
  INTO Table2
FROM (
  SELECT (SUBSTRING(Column1,1,3)) AS ComputedColumn
  FROM Table1
) t
WHERE ComputedColumn != 'dec'

I also change the double quotes to single quoteset because string literals need to be put between single quotes. Double quotes denote column (or table) names.

share|improve this answer

Create a function for your calculation, and call that function everywhere you need that column, For example in your case;

Create FUNCTION [dbo].[GetSubString]
( @Column1 AS nvarchar(MAX) ,
  @StartIndex AS int ,
  @EndIndex as int,
)
RETURNS nvarchar(MAX)
AS
BEGIN
  Declare @value nvarchar(MAX);

  SET @value = SUBSTRING(@Column1,@StartIndex,@EndIndex);

  RETURN @Value;
END

and in the query all your function when its need, this has less memory an cpu consumption that copy a large table to new table and use it.

Note that for your case substring itself is already a function and not need to create another function to call available function, But in case of more calculations need this way help you have a cleaner query.

say you need to calculate products' Applied Or Current Price when your product has special price and/or discount that applied in a range of time or date or etc, so your current price calculation needs more lines and its not suitable to write it every where you need in query.

In this case it will somthing like this:

Create FUNCTION [dbo].[GetCurrentPrice]
( @Price AS decimal(18,4) ,
  @SpecialPrice AS decimal(18,4) ,
  @SpecialPriceStartDate AS datetime2 ,
  @SpecialPriceEndDate AS datetime2 
)
RETURNS decimal(18,4)
AS
BEGIN
  Declare @CurrentPrice DECIMAL(18,4);

    SET @CurrentPrice = CASE WHEN (@SpecialPrice > 0 AND @SpecialPriceStartDate <= GETUTCDATE() 
                        AND @SpecialPriceEndDate >= GETUTCDATE())
                THEN @SpecialPrice ELSE @Price END;

  RETURN @CurrentPrice;
END
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.