2

This is a simple question that I'm hung up on. I want to know if I can create a column alias and then use it in my WHERE clause, i.e.:

SELECT TRACTOR, CONVERT(VARCHAR, ORDER) AS NUMBER
FROM TABLE
WHERE NUMBER = '4'

Keep in mind this is just an example of what I'm trying to do. The query I'm running is a bit more complex, but just the basic idea of how to create a variable and then use it in a clause.

My question is about the aliased columns in the where clause. I have a query that looks for an invoice number in one database and matches it the first 7 digits of a field in another database. The query worked fine when we only had 6 digits but now that we have 7, I'm getting an error and I'm trying to rewrite the query in a different manner.

Kritner
  • 13,557
  • 10
  • 46
  • 72
AccidentalDBA
  • 33
  • 4
  • 4
    For starters, please stop using lazy declarations like [`VARCHAR` with no length](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx). Next, you can't reference an alias in the `WHERE` clause. Finally, if `ORDER` is numeric, why do you want to use a string? – Aaron Bertrand Oct 14 '14 at 19:40
  • I was just making a quick generic query, sorry. Also, order is actually a string, ex: 1234567A or 1234567B – AccidentalDBA Oct 14 '14 at 19:43
  • 4
    are you actually asking about variables? Or are you asking more about aliased columns in the where clause? – Kritner Oct 14 '14 at 19:44
  • 4
    [How to ask](http://stackoverflow.com/help/how-to-ask) – billinkc Oct 14 '14 at 19:47
  • more so aliased columns in the where clause. I have a query that looks for an invoice number in one database and matches it the first 7 digits of a field in another database. The query worked fine when we only had 6 digits but now that we have 7, I'm getting an error and I'm trying to rewrite the query in a different manner. – AccidentalDBA Oct 14 '14 at 19:49

2 Answers2

6

Re-using aliased columns is a great use case for CROSS APPLY:

SELECT t.TRACTOR, CxA.Num
FROM TABLE t
CROSS APPLY
  (SELECT CONVERT(VARCHAR(10), ORDER)) CxA(Num)
WHERE CxA.Num = '4'

Anything in a CROSS APPLY can be referenced in the SELECT, WHERE, ORDER BY, etc with some limitations (normally if you have aggregation in the CROSS APPLY expression).

JNK
  • 63,321
  • 15
  • 122
  • 138
3

Take a look at: http://sqlmag.com/t-sql/working-variables-t-sql-part-1

The basic syntax of variables would be like so:

declare @id int
select @id = 1

select *
from myTable
where id = @id

EDIT: in case you're actually asking about column aliases in your where clause, and not sql variables, take a look at this SO question: Referring to a Column Alias in a WHERE Clause

EDITx2:

for your specific case (assuming asking about column aliases) you could do the following:

SELECT *
FROM (
    SELECT TRACTOR, CONVERT(VARCHAR(100), ORDER) AS NUMBER
    FROM TABLE
) someTableAlias
WHERE NUMBER = '4'

Though you may want to rethink your column names... i think number and order are both reserved words, and should be avoided where possible. you can get around these for the most part by using [order] and [number] I believe, but still best to avoid reserved words as columns/tables/whatevers.

Community
  • 1
  • 1
Kritner
  • 13,557
  • 10
  • 46
  • 72