SQL Server Using SQL Alias In Expression Without SELECTING It [duplicate]

ecbunoof 于 2023-04-04 发布在其他

关注(0)|答案(2)|浏览(130)

This question already has answers here:

Possible to store value of one select column and use it for the next one? (4 answers)
How to use a calculated column to calculate another column in the same view (5 answers)

Closed 5 days ago.

I have a simple issue in SQL (using SQL Server) and it drives me nuts... I'm sure there must be an easy solution but have yet to identify it. I must add that I have been programming in SQL for some time now and am not a beginner.

Basically I run a query on 2 Tables (1 and 2) using an INNER JOIN and in the SELECT part, I ask for several columns to be returned... so far everything plain and basic.

But I also want to refer to a column in Table A without selecting it as such. The column itself contains the last entry date of an item in our inventory system and is called [Last Entry Dt] .

Now, I do not want this column to be in the query output but rather the number of days since that Last Entry Dt, which is derived from [Last Entry Dt] . Next, using this information I qualify in the output dataset each item with strings such as "old item", "relatively new item", "new item" etc.

In short, from the query I don't need the [Last Entry Dt] , but rather this qualifier.

I tried the following:

SELECT 
    Acol, Bcol, Ccol, 
    [Last Entry Dt] AS LED, 
    DATEDIFF('d', LED, TodayDate) AS ItemAge 
FROM 
    Table1 
INNER JOIN 
    Table1.somefield = Table2.somefied etc.

And of course it works, but I get the last entry date from the query as well, which I don't need in the output dataset. So the question is, how do I define the alias LED in the query without actually selecting it but rather merely using it in another expression?

I tried using a double SELECT statement, where the second one is appended after FROM , i.e.

SELECT 
    Acol, Bcol, Ccol, 
    DATEDIFF('d', LED, TodayDate) AS ItemAge
FROM 
    Table1 
INNER JOIN 
    Table1.somefield = Table2.somefied, 
(SELECT [Last Entry Dt] AS LED)

but I got a

Syntax error in FROM clause

Thank you so much,

Stefan

sql-server

来源：https://stackoverflow.com/questions/75878209/using-sql-alias-in-expression-without-selecting-it

2条答案

按热度按时间

tct7dpnv1#

If you don't really want the column [Last Entry Dt] , but a "classification" based on it - try something like this (also - you cannot refer to an alias that you've introduced in the SELECT , inside the same SELECT - you need to use the actual column name):

SELECT 
    Acol, Bcol, Ccol, 
    [Last Entry Dt] AS LED, 
    CASE 
        WHEN DATEDIFF(day, [Last Entry Dt], TodayDate) <= 30 THEN 'Fairly recent'
        WHEN DATEDIFF(day, [Last Entry Dt], TodayDate) <= 90 THEN 'A bit older'
        WHEN DATEDIFF(day, [Last Entry Dt], TodayDate) > 90 THEN 'Quite old really'
        ELSE 'undetermined'
    END AS Classification
FROM 
    Table1 
INNER JOIN 
    Table2 ON Table1.somefield = Table2.somefield

Furthermore:

I'd recommend using the proper day , month etc. in DATEDIFF - instead of their abbreviations - just makes the code more readable and understandable
I correct the INNER JOIN - you need to INNER JOIN with a second table and an ON join condition

赞(0）回复(0）举报 2023-04-04

bn31dyow2#

Another option is a CROSS APPLY with the VALUES construct. This allows you to stack and reference calculations

Depending on your tables, the CROSS APPLY can come after the JOIN

Example

SELECT Acol
     , Bcol
     , Ccol
     ,DATEDIFF('d', LED, TodayDate) AS ItemAge 
FROM Table1 
Cross Apply ( values ( [Last Entry Dt] )) C(LED)
INNER JOIN  Table1.somefield = Table2.somefied

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server Using SQL Alias In Expression Without SELECTING It [duplicate]

2条答案

相关问题

热门标签

最新问答