Using an Alias Column in the Where Clause in Ms-SQL 2000

Using an Alias column in the where clause in ms-sql 2000

One workaround would be to use a derived table.

For example:

select *
from 
   (
   select a + b as aliased_column
   from table
   ) dt
where dt.aliased_column = something.

I hope this helps.

Can you use an alias in the WHERE clause in mysql?

You could use a HAVING clause, which can see the aliases, e.g.

 HAVING avg_rating>5

but in a where clause you'll need to repeat your expression, e.g.

 WHERE (sum(reviews.rev_rating)/count(reviews.rev_id))>5

BUT! Not all expressions will be allowed - using an aggregating function like SUM will not work, in which case you'll need to use a HAVING clause.

From the MySQL Manual:

It is not allowable to refer to a
column alias in a WHERE clause,
because the column value might not yet
be determined when the WHERE clause
is executed. See Section B.1.5.4,
“Problems with Column Aliases”.

Selecting an alias in case t-sql

use this datediff function whenever you need.

DATEDIFF(d, vso.pod_ata, ISNULL(cncr.cntr_date3, GETUTCDATE())) - 14  AS dem_days, -- Date difference to work out Demurrage days
DATEDIFF(d, cncr.dc_ata, ISNULL(cncr.empty_return_dt, GETUTCDATE())) - 21  AS det_days,

CASE WHEN DATEDIFF(d, vso.pod_ata, ISNULL(cncr.cntr_date3, GETUTCDATE())) - 14 <= 0 THEN 0 END AS 'test1',
CASE WHEN DATEDIFF(d, cncr.dc_ata, ISNULL(cncr.empty_return_dt, GETUTCDATE())) - 21 <= 0 THEN 0 END AS 'test2',

alias WHERE clause to simplify your query

You can try this way.

SELECT *
FROM
(
    SELECT x, y, z, (x*y) xy, (x*z) xz
    FROM T
) R
WHERE (xy < 15 AND xz > 20) OR (xy > 20 AND xz < 100)

Use datetime as column alias in SQL Server 2000

You have to use dynamic sql. So maybe something like this:

DECLARE @DayToCheck datetime
SET @DayToCheck=GETDATE()

DECLARE @query NVARCHAR(4000)
SET @query=
N'SELECT
    sum(case when startdate=@DayToCheck then 1 else 0 end) as '+
    CONVERT(varchar, @DayToCheck, 103)+',
    sum(case when startdate=DATEADD(day,-7,@DayToCheck) then 1 else 0 end) as '+
    CONVERT(varchar, @DayToCheck, 103)+'
FROM
    table1'

EXECUTE(@query)

How to use an Alias in a Calculation for Another Field

That method doesn't work in SQL Server. You can accomplish the same thing in a couple different ways:

1.) Use the code for each aliased column instead of the alias:

(SELECT COUNT(*)
 FROM UserEvent UE
 WHERE UE.EventTypeID = 1 
 AND UE.PracticeID = au.PracticeID 
 AND (UE.EventDate BETWEEN @Date1 and @Date2) 
- COUNT(CASE WHEN udi.DevicePlatform = 'iOS' THEN 1 ELSE NULL END)
+ COUNT(CASE WHEN udi.DevicePlatform = 'Android' THEN 1 ELSE NULL END) Desktop Logics

2.) Use a derived table to make the columns, then you can reference them by alias:

SELECT PracticeName, iOSLogins, AndroidLogins, TotalNumberLogins,
       (TotalNumberofLogins - (iOSLogins + AndroidLogins)) DesktopLogins
FROM (
       SELECT prtc.PracticeName,
              COUNT(CASE WHEN udi.DevicePlatform = 'iOS' THEN 1 ELSE NULL END) iOSLogins,  
              COUNT(CASE WHEN udi.DevicePlatform = 'Android' THEN 1 ELSE NULL END) AndroidLogins,
             ( SELECT COUNT(*)
               FROM UserEvent UE
               WHERE UE.EventTypeID = 1 
               AND UE.PracticeID = au.PracticeID 
               AND (UE.EventDate BETWEEN @Date1 and @Date2)
             ) TotalNumberLogins, 
       FROM UserDeviceInfo UDI 
       JOIN AppUser AU ON udi.UserID = au.UserID 
       JOIN Practice PRTC ON au.PracticeID = prtc.PracticeID 
       WHERE au.PracticeID = @PracticeID 
       AND (udi.Created BETWEEN @Date1 AND @Date2)
       GROUP BY prtc.PracticeName, au.PracticeID
    ) a --table alias

Edit: Table alias explained

In a simple query:

SELECT col1 FROM Table

You know the table reference for col1 is Table. (Table.Col1) You don't have to write it if it is the only col1, but you still know the table it is referencing.

In a simple derived table:

SELECT col1 FROM (SELECT col1 FROM Table)

The table reference for the inner column is still Table, but what about the outer? In this case, everything within the parentheses is your table, but in the above example that table is unnamed. SQL Server requires that you name/alias the table that you have created so you can reference it:

SELECT col1 FROM (SELECT col1 FROM Table) MyDerivedTable

...and now you have a table reference for your outer column:

SELECT MyDerivedTable.col1 FROM (SELECT col1 FROM Table) MyDerivedTable

You can also see a greater need for this once more tables are involved:

SELECT MyDerivedTable.col1
FROM (SELECT col1 FROM Table) MyDerivedTable
JOIN Table T on T.col1 = MyDerivedTable.col1

Edit 2: CTE option:

Another option is a common table expression or CTE:

with cteName as (
SELECT prtc.PracticeName,
       COUNT(CASE WHEN udi.DevicePlatform = 'iOS' THEN 1 ELSE NULL END) iOSLogins,  
       COUNT(CASE WHEN udi.DevicePlatform = 'Android' THEN 1 ELSE NULL END) AndroidLogins,
         ( SELECT COUNT(*)
           FROM UserEvent UE
           WHERE UE.EventTypeID = 1 
           AND UE.PracticeID = au.PracticeID 
           AND (UE.EventDate BETWEEN @Date1 and @Date2)
         ) TotalNumberLogins, 
   FROM UserDeviceInfo UDI 
   JOIN AppUser AU ON udi.UserID = au.UserID 
   JOIN Practice PRTC ON au.PracticeID = prtc.PracticeID 
   WHERE au.PracticeID = @PracticeID 
   AND (udi.Created BETWEEN @Date1 AND @Date2)
   GROUP BY prtc.PracticeName, au.PracticeID
)

SELECT PracticeName, iOSLogins, AndroidLogins, TotalNumberLogins,
       (TotalNumberofLogins - (iOSLogins + AndroidLogins)) DesktopLogins
FROM cteName

These can be pretty convenient because they create a clear separation between the outer and inner queries. Ultimately it does the same thing as inline derived tables, so choose whichever version is more readable to you. (props to xorcus for suggesting the CTE addition)

Using an Alias column in the where clause in Postgresql

MySQL's support is, as you experienced, non-standard. The correct way is to reprint the same expression used in the SELECT clause:

SELECT
    jobs.*, 
    CASE 
         WHEN lead_informations.state IS NOT NULL THEN lead_informations.state 
         ELSE 'NEW' 
    END AS lead_state
FROM
    jobs
    LEFT JOIN lead_informations ON
        lead_informations.job_id = jobs.id
        AND
        lead_informations.mechanic_id = 3
WHERE
    lead_informations.state IS NULL

---

Related Topics