Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
select 
convert(varchar(10),  TotalSeconds / 3600) +':'+
convert(varchar(10),   TotalSeconds % 3600 / 60) +':'+ 
 convert(varchar(10),  TotalSeconds % 60) as Seconds
from
(select
  DateDiff
(second, date,outtime )
 as TotalSeconds 
    from attendance.attn_card_register) x -- this one
share|improve this question
6  
The SQL Standards call it a "correlation name", colloquially it is known as an "alias". –  onedaywhen Oct 12 '11 at 11:39
    
Related: dba.stackexchange.com/q/5989/2660 –  Nick Chammas Nov 16 '11 at 17:23

2 Answers 2

up vote 11 down vote accepted

It is the alias used to reference the derived table. In the outer SELECT if you fully qualified the column references they would read x.TotalSeconds

select convert(varchar(10)
     , x.TotalSeconds / 3600) +':'+ convert(varchar(10)
     , x.TotalSeconds % 3600 / 60) +':'+ convert(varchar(10)
     , x.TotalSeconds % 60) as Seconds 
from (select DateDiff (second, date,outtime ) as TotalSeconds 
      from attendance.attn_card_register) x
share|improve this answer
    
thanks RobPaller –  Isha Oct 13 '11 at 6:36

All tables used in a FROM clause must have a "correlation name" in standard SQL terms a.k.a "alias" in common vernacular SQL

When you use a table directly, the name is obvious. When you use an inline derived table it must be aliased whether it is actually referenced or not

You can use a CTE too. Here "MyCTE" is the name and doesn't required an alias

;WITH MyCTE AS
(
    select
      DateDiff (second, date,outtime)  as TotalSeconds 
    from
        attendance.attn_card_register)
)
Select 
   convert(varchar(10),  TotalSeconds / 3600) +':'+
   convert(varchar(10),   TotalSeconds % 3600 / 60) +':'+ 
   convert(varchar(10),  TotalSeconds % 60) as Seconds
from
   MyCTE;

Edit: not all RDBMS require it. SQL Server, MySQL, and Teradata do, Oracle doesn't.

share|improve this answer
    
"When you use an inline derived table it must be aliased so it can be referenced" -- slight quibble: in Standard SQL, it must be 'aliased' regardless of whether it needs to be 'referenced' e.g. SELECT COUNT(*) FROM (SELECT * FROM P) P1; -- here the correlation name P1 is entirely pointless yet mandatory. –  onedaywhen Oct 12 '11 at 11:44
    
@onedaywhen: clarified, thnaks –  gbn Oct 12 '11 at 11:58
    
thank you for improving my knowledge –  Isha Oct 13 '11 at 6:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.