Invalid Column Name in SELECT

This select works as I expect:
SELECT exp_AcctNum,
exp_Amount,
CAST(Left(dbo.strat(exp_Amount),2) AS INT) AS [Strat Level],
SUBSTRING(dbo.strat(exp_Amount),3, LEN(dbo.strat(exp_Amount))- 2)
AS [Strata Desc]
FROM Expenses
Go
However, I am concerned about it having to call the function 3 times for
each row. Will SQL Server 2000 be smart enough to know that it is the same
call each time?
I tried using column aliases, but got errors.
For example, in Access I am use to doing something like:
SELECT Amount AS [Amt],
AMT as Amt2
FROM [tbl Expenses];
When I try to do the same thing in SQL Server,
SELECT exp_Amount AS [Amt],
AMT as Amt2
FROM Expenses
Go
it gives me a Invalid column name 'AMT'.
I had wanted to call the function once and create a column alias and then
use that alias for the CAST and SUBSTRING, but cannot get by the column
issue.
Something like
SELECT exp_AcctNum,
exp_Amount,
dbo.strat(exp_Amount) AS 'stvalue',
CAST(Left(stvalue,2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(dbo.strat(exp_Amount))- 2)
AS [Strata Desc]
FROM Expenses
Go
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'stvalue'.
I would really appreciate some guidance on this one!
Thanks.On Thu, 1 Jun 2006 17:40:15 -0500, MikeV06 wrote:

>This select works as I expect:
>SELECT exp_AcctNum,
> exp_Amount,
> CAST(Left(dbo.strat(exp_Amount),2) AS INT) AS [Strat Level],
> SUBSTRING(dbo.strat(exp_Amount),3, LEN(dbo.strat(exp_Amount))- 2)
> AS [Strata Desc]
>FROM Expenses
>Go
>However, I am concerned about it having to call the function 3 times for
>each row. Will SQL Server 2000 be smart enough to know that it is the same
>call each time?
Hi Mike,
Unfortunately, no.
(snip)
>I had wanted to call the function once and create a column alias and then
>use that alias for the CAST and SUBSTRING, but cannot get by the column
>issue.
>Something like
>SELECT exp_AcctNum,
> exp_Amount,
> dbo.strat(exp_Amount) AS 'stvalue',
> CAST(Left(stvalue,2) AS INT) AS [Strat Level],
> SUBSTRING(stvalue, 3, LEN(dbo.strat(exp_Amount))- 2)
> AS [Strata Desc]
>FROM Expenses
>Go
You can't do it this way. A column alias can only be used in the ORDER
BY clause, nowhere else in the query.
You can use a derived table, though:
SELECT exp_AcctNum,
exp_Amount,
stvalue,
CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc]
FROM (SELECT exp_AcctNum,
exp_Amount,
dbo.strat(exp_Amount) AS stvalue
FROM Expenses) AS d
Hugo Kornelis, SQL Server MVP|||On Fri, 02 Jun 2006 01:00:44 +0200, Hugo Kornelis wrote:
[snip]

> You can't do it this way. A column alias can only be used in the ORDER
> BY clause, nowhere else in the query.
> You can use a derived table, though:
> SELECT exp_AcctNum,
> exp_Amount,
> stvalue,
> CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
> SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc]
> FROM (SELECT exp_AcctNum,
> exp_Amount,
> dbo.strat(exp_Amount) AS stvalue
> FROM Expenses) AS d
Thank you very much. I was miles away from this solution and had already
spent 1 day trying several things that did not work. It needs a little
touch up; however, what I finally came up from your template works. What I
really do like is that the call to the function is only made once.
I guess I could put the derived table in a view and have this view call
that view (which ends up being called by another view to get the final
stratification result table). I am not sure I see any benefit in taking
that approach, but may try it just to see what happens. Uhm, maybe another
UDF instead of a View ... too tired to really think at this point.
You have made my day -- I think I will give it up for the day. Thanks.
Mike.
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'prestrat')
DROP VIEW prestrat
GO
CREATE VIEW prestrat
AS
SELECT exp_AcctNum,
exp_Amount,
stvalue,
CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc],
posrecs,
posdols,
negrecs,
negdols,
[posrecs]+[negrecs] AS totrecs,
[posdols]+[negdols] AS totdols,
exp_VouchNum,
exp_InvoiceNum,
exp_StoreNum,
exp_Date,
exp_SupplierNum,
exp_SupplierName,
exp_RecordType
FROM (SELECT exp_AcctNum,
exp_Amount,
exp_VouchNum,
exp_InvoiceNum,
exp_StoreNum,
exp_Date,
exp_SupplierNum,
exp_SupplierName,
exp_RecordType,
CASE WHEN exp_Amount>=0 THEN 1 ELSE 0 END AS posrecs,
CASE WHEN exp_Amount>=0 THEN exp_Amount ELSE 0 END AS
posdols,
CASE WHEN exp_Amount<0 THEN 0 ELSE 1 END AS negrecs,
CASE WHEN exp_Amount<0 THEN 0 ELSE exp_Amount END AS negdols,
dbo.strat(exp_Amount) AS stvalue
FROM Expenses) AS d
WHERE Exp_RecordType = '2'
Go