Referencing an alias in a where clause

I have the following data set - there is more to it than whats below, I just made it easier to read and highlight my problem!

SELECT LEFT(actv.ProjID, 4) AS proj, actv.Activity, actv.TotalExpensesLB, actv.PADM
FROM dbo.xtbl_MERActv actv
WHERE LEFT(actv.projID,4) = @.project OR actv.PADM = @.PADM

What I want to do is have the user enter a 4 digit number (@.project) which will correspond to LEFT(actv.ProjID, 4). The way it is now, if the user enters a 4 digit number, no records are returned. If the user enters a 6 digit number ( the real length of the projID), then it runs correctly and I get the records I want.

I have tried to use the alias 'proj' in the where statement, but I get an error message that it is an invalid column name.

Where am I going wrong?

Thanks in advance!

You cannot use a column alias in the where clause, you just have to duplicate the calculation.

I cannot see anything wrong with what you are trying to do. My only guess is that perhaps there is a datatype problem. Are both @.project and actv.projID the same datatype?

Also, what version of SQL server is this?

|||

Its SQL 2000, and I haven't actually set the @.project as any data type - could that be the problem? The actv.projID is char.

Actually, I may not have been clear in the problem. When running the data set, it will work fine, but when I try to preview the report, and enter a 4 digit @.project, I get a blank report with no error.

Thanks for the info on not using column alias!

|||

Ah, that's a different problem.

I'm not really sure what could be causing a blank report when your dataset returns data.

What is the datatype of the parameter that you are using to hold the 4-digit number?

Also, when you said that entering a six digit number worked, was that without any modification to the dataset?

|||

Sorry for that confusion about the problem.

The parameter is string, as is the projId.

It seems that using either 4 or 6 digit for @.project will give me results in the viewer, but still blank preview

Using the following statement

WHERE (LEFT(actv.ProjID, 4) = @.project)

|||

I cannot think of a case where the left 4 characters of a string* would be equal to a six character string...

Actually, I take that back. If you have ANSI_NULLS set to OFF (not recommended) and both of the sides were NULL, then it could, but then you should be getting the same results for both entries, which is to say all rows. I guess I'm stumped.

*string meaning varchar, char, nvarchar, or nchar

|||

It doesn't make sense to me at all - I figured there was something really obvious that I had forgotten...I'll take a new look at it on Monday, with fresh eyes, and maybe see my error.

Thanks for your help!

Referencing a conditional column in the WHERE clause -- Possible?

I have two tables which I'm joining in a query. In the join, I'm creating a column whose value is conditional (see columns Actual_Latitude and Actual_Longitude below). Is it possible to reference the created column in the WHERE clause?

Consider the following query:

---

SELECT
S.StationID
, P.PoleID
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID

---

I'd like to be able to add the following:

---

WHERE Actual_Latitude > 50

---

...But, I'm getting an "Invalid column name" error. Is this possible in some way?

The benefit, of course, would be that I wouldn't have to repeat the conditions in the WHERE clause.

Unfortunately, you can't.

You are creating an ALIAS for an expression, and the expression is not 'known' by that ALIAS in the 'acquisition' part of the query. Once the data is acquired, you can refer to the ALIAS in the ORDER BY because a 'derived table' has been determined.

You could, however, wrap this query in another, and use the ALIAS in the outer query. That does't provide much help with filtering though...

The 'best' option is to repeat the CASE structure for Actual_Latitude in the WHERE clause.

|||

I agree with Arnie for the most part. This will work:

SELECT S.StationID , P.PoleID

--NOTE: change to this means a change to the where clause for Actual_latitude!!
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
where CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END > 50 --Actual_Latitude > 50

In 2005, I would probably try this and see how it works out. It probably will have the same plan and is a bit clearer. If this is a highly used, performance intensive operation I would consider rewriting the query to eliminate the CASE in the where clause and express it as just expressions (it could be done, I think):

WITH stationQuery AS (

SELECT S.StationID , P.PoleID
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
select *

from stationQuery

where actual_latitude > 50

The thing is, you are not going to get good performance no matter how you do it. Since the both of your tables in the join are tied up in the CASE expression, very unlikely to get any index utilization. You could also do it as a derived table in 2000.

Reference an aggregate in where clause

I have two tables that are joined on 1 column.

I want to update one table using an aggregate from the other table.

I could just update all rows even if the aggregate value has not changed, but I wondered if there was a way to only update the records where the aggregate has changed.

I tried this code but assigning the name m to aggreate gives an error

UPDATE t2 SET t2.ColumnB = SELECT MAX(ColumnB) AS m From Table t1

JOIN t2 ON t1.ColumnA = t2.ColumnA

WHERE t2.ColumnB <> m

GROUP BY t1.ColumnA

Niall:

Does this meet your needs?

set nocount on

declare @.t1 table (ColumnA char(1), ColumnB int)
declare @.t2 table (ColumnA char(1), ColumnB int)

insert into @.t1 values ('A', 5)
insert into @.t1 values ('B', null)
insert into @.t1 values ('C', null)
--select * from @.t1

insert into @.t2 values ('A', 3)
insert into @.t2 values ('A', 5)
insert into @.t2 values ('A', 7)
insert into @.t2 values ('B', 2)
--select * from @.t2

set nocount off

update @.t1
set ColumnB = xt.columnb
from @.t1 as yt
inner join
( select t1.ColumnA,
max (t2.columnB) columnB
from @.t1 as t1
inner join @.t2 as t2
on t1.columnA = t2.columnA
group by t1.columnA
) xt
on xt.columnA = yt.columnA

set nocount on

select * from @.t1

-- --
-- Output
-- --

-- (2 row(s) affected)

-- ColumnA ColumnB
-- - --
-- A 7
-- B 2
-- C NULL

|||

Use a subquery - replace the 0 in the isnull calls with blank strings if your ColumnB is character data.

UPDATE Table2 SET ColumnB = t1.MaxB
FROM Table2 t2
INNER JOIN
(SELECT ColumnA, MAX(ColumnB) AS MaxB
FROM Table1
GROUP BY ColumnA) t1 ON t2.ColumnA = t1.ColumnA
WHERE isnull(t2.ColumnB, 0) <> isnull(t1.MaxB, 0)

Reference a column in UDF in a FROM or WHERE clause

Hello there,

I got a problem with using a table returning UDF in
FROM/WHERE clause.

For example...

SELECT *
FROM tb_Employees AS E INNER JOIN
dbo.fn_EmployeesInDept('Houskeeping') AS EID ON
E.EmployeeID = EID.EmployeeID

Works without a problem. But when i reference a column in
the udf...

SELECT *
FROM tb_Employees AS E INNER JOIN
dbo.fn_EmployeesInDept(E.Department) AS EID ON
E.EmployeeID = EID.EmployeeID

Gives me the following error message

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '.'.

Joining in the WHERE Clause gives the same Message

NOTE. This is a simplified example of what i'm actally
trying to achieve.
The point is referencing a column in a UDF in a FROM or
WHERE clause

Why is this? Thanks

CREATE TABLE [tb_employees]
(
[EmployeeID] [int] NOT NULL ,
[EmployeeName] [nvarchar] (50) ,
[Department] [nvarchar] (50) ,
CONSTRAINT [PK_tb_employees] PRIMARY KEY
CLUSTERED
(
[EmployeeID]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE FUNCTION fn_EmployeesInDept (@.Department nvarchar
(50))
RETURNS @.Employees TABLE
(
EmployeeID int
)
AS
BEGIN
INSERT INTO @.Employees(EmployeeID) SELECT
EmployeeID FROM tb_employees WHERE Department = @.Department

RETURN
END

INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (1, 'Kees', 'Shipping')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (2, 'Piet', 'Shipping')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (3, 'Jan', 'Accounting')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (4, 'Klaas', 'Accounting')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (5, 'Dirk', 'Houskeeping')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (6, 'Arie', 'Houskeeping')
INSERT INTO tb_Employees(EmployeeID, EmployeeName,
Department) VALUES (7, 'Bob', 'Houskeeping')RE:
Q1 [Is it possible to pass a UDF a non-scalar as in the example in the From clause; and why]? Example in the From clause:

SELECT *
FROM tb_Employees AS E INNER JOIN
dbo.fn_EmployeesInDept(E.Department) AS EID ON
E.EmployeeID = EID.EmployeeID


A1 Not directly. Sql Server 2k UDFs parameters currently may accept constants (or certain kinds of expressions evaluateing to scalar constants within the current execution context). Sql Server 2k UDFs parameters do not currently accept table, or other non scalar referenced database objects. One may instead use an iterative approach, (passing each sucessive value in as a scalar) as for example:

Declare
@.vDepartment As Nvarchar (128)
Select @.vDepartment = (Select Top 1 Department From dbo.tb_Employees)
Select @.vDepartment As '@.vDepartment', EmployeeID From dbo.fn_EmployeesInDept(@.vDepartment)
.
.
.

RE:
Q2 [Is it possible to reference a column in a UDF in a Where Clause]?

A2 Yes. Referencing Sql Server 2k UDFs in a Where clause is supported. The following, for example, should work:

SELECT *
FROM tb_Employees AS E INNER JOIN
dbo.fn_EmployeesInDept('Houskeeping') AS EID ON
E.EmployeeID = EID.EmployeeID
Where EID.EmployeeID > 5|||/*
USE SCALAR FUNCTION
*/
create FUNCTION fn_IsEmployeeInDept (@.Department nvarchar (50), @.EmployeeID int)
RETURNS bit
with schemabinding
AS
BEGIN
declare @.IsEmployeeID bit
if exists(select Department from dbo.tb_employees WHERE Department = @.Department and EmployeeID=@.EmployeeID)
set @.IsEmployeeID=1
else
set @.IsEmployeeID=0
RETURN @.IsEmployeeID
END
GO

/*
HAS DIFFERENT FUNCTIONALITY
*/
SELECT E.EmployeeID,E2.EmployeeID
FROM tb_Employees AS E
JOIN tb_Employees AS E2 on fn_IsEmployeeInDept(E.Department,E2.EmployeeID)=1

/*
IT IS NOSENCE, CAN BE REPLACED BY JOIN
*/
SELECT E.EmployeeID,E2.EmployeeID
FROM tb_Employees AS E
JOIN tb_Employees AS E2 on E.Department=E2.Department

/*
BUT SCALAR FUNCTION JOIN CAN USED TO JOIN HIERARCHIES IN MSSQL2K
*/