Error using column alias to make operations in PostgreSQL

Question

I'm having a problem with this query. I want to make an operation for my payroll system. I Here is my query which doesn't work.

Query:

select
    hrrawd_timein,
    hrrawd_shiftin,
    (
      (extract(hour from (hrrawd_timein::time))
      - extract(hour from (hrrawd_shiftin::time))
      )::numeric
    ) AS shiftinhours,
    (
      (extract(minute from (hrrawd_timein::time))
      - extract(minute from (hrrawd_shiftin::time))
      )::numeric
    ) AS shiftinminutes,
    (
      ((extract(hour from (hrrawd_timein::time))
      - extract(hour from (hrrawd_shiftin::time)
      ))*60)::numeric
    )
    + 
    (
      (extract(minute from (hrrawd_timein::time))
      - extract(minute from (hrrawd_shiftin::time))
      )::numeric
    ) AS Total,
    case
      when (Total >0) then 'Late' 
      else 'EARLY'
    end as remarks
  FROM hr.hrrawd;

Error:

> ERROR:  column "total" does not exist LINE 7: case when (Total >0)
> then 'Late'

roman · Accepted Answer · 2013-08-27 10:28:05Z

4

I suggest to use common table expressions when you need precalculated columns:

with cte1 as (
    select
        hrrawd_timein, hrrawd_shiftin,
        (
           extract(hour from (hrrawd_timein::time)) -
           extract(hour from (hrrawd_shiftin::time))
        )::numeric as shiftinhours,
        (
           extract(minute from (hrrawd_timein::time)) -
           extract(minute from (hrrawd_shiftin::time))
        )::numeric as shiftinminutes
    from hr.hrrawd
), cte2 as (
    select
        *,
        shiftinhours * 60 + shiftinminutes as [total]
   from cte1
)
select
    *,
    case when [total] > 0 then 'Late' else 'Early' end as remarks
from cte2

I think it's much cleaner than subqueries. You can also chain as many CTE as you want, and it helps you to keep DRY principle - note how shiftinminutes and shiftinhours calculated just one time instead of two times. Readability counts, don't do the calculation twice, it will be hard to maintain in the future.

BTW, take a look at interval data type in PostgreSQL, you can extract hours and minutes from it

edited Aug 27, 2013 at 10:28

answered Aug 27, 2013 at 10:15

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ralph Robert Caritativo Over a year ago

Thankyou sir but your code has an error "ERROR: syntax error at or near "as" LINE 7: )::numeric) as shiftinhours,"

Tomas Greif · Accepted Answer · 2013-08-27 09:49:15Z

1

Answer:

select
    *,
    case when (Total >0) then 'Late' 
     else 'EARLY' end as remarks
from (
    select  hrrawd_timein, hrrawd_shiftin,
    ((extract(hour from (hrrawd_timein::time)) - extract(hour from (hrrawd_shiftin::time)))::numeric) AS shiftinhours,
    ((extract(minute from (hrrawd_timein::time)) - extract(minute from (hrrawd_shiftin::time)))::numeric) AS shiftinminutes,
    (((extract(hour from (hrrawd_timein::time)) - extract(hour from (hrrawd_shiftin::time)))*60)::numeric)
     + 
     ((extract(minute from (hrrawd_timein::time)) - extract(minute from (hrrawd_shiftin::time)))::numeric) AS Total
    FROM hr.hrrawd) a;

answered Aug 27, 2013 at 9:49

Tomas Greif

22.9k25 gold badges114 silver badges162 bronze badges

1 Comment

Ralph Robert Caritativo Over a year ago

Hello sir, It is possible instead of this (((EXTRACT(HOUR FROM (hrrawd_timein::TIME)) - EXTRACT(HOUR FROM (hrrawd_shiftin::TIME)))*60)::NUMERIC) + ((EXTRACT(MINUTE FROM (hrrawd_timein::TIME)) - EXTRACT(MINUTE FROM (hrrawd_shiftin::TIME)))::NUMERIC) AS Total I'm using the ALIAS for the formula of total? for example "(shiftinhours + shiftinminutes) as Total" is that possible?

dnoeth · Accepted Answer · 2013-08-27 09:49:45Z

1

Standard SQL doesn't allow a column alias to be used in any other place but ORDER BY.

You need to cut&paste the calculation or better use a Derived Table:

SELECT
   hrrawd_timein, hrrawd_shiftin, 
   shiftinhours,
   shiftinminutes,
   Total,
   CASE WHEN (Total >0) THEN 'Late' ELSE 'EARLY' END AS remarks
FROM
 (
    SELECT  hrrawd_timein, hrrawd_shiftin, 
       ((EXTRACT(HOUR FROM (hrrawd_timein::TIME)) - EXTRACT(HOUR FROM (hrrawd_shiftin::TIME)))::NUMERIC) AS shiftinhours,
       ((EXTRACT(MINUTE FROM (hrrawd_timein::TIME)) - EXTRACT(MINUTE FROM (hrrawd_shiftin::TIME)))::NUMERIC) AS shiftinminutes,
       (((EXTRACT(HOUR FROM (hrrawd_timein::TIME)) - EXTRACT(HOUR FROM (hrrawd_shiftin::TIME)))*60)::NUMERIC)
        + ((EXTRACT(MINUTE FROM (hrrawd_timein::TIME)) - EXTRACT(MINUTE FROM (hrrawd_shiftin::TIME)))::NUMERIC) AS Total
    FROM hr.hrrawd
 ) AS dt

answered Aug 27, 2013 at 9:49

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

4 Comments

Tomas Greif Over a year ago

You can use alias also in GROUP BY, e.g.: select t as d from generate_series(1,10,1) t group by d

dnoeth Over a year ago

@Tomas Greif: That's why i said "Standard SQL" :-) Some DBMSes allow it to be used in other places, too. But AFAIK only Access and Teradata allow in SELECT/WHERE.

Ralph Robert Caritativo Over a year ago

Hello sir, It is possible instead of this (((EXTRACT(HOUR FROM (hrrawd_timein::TIME)) - EXTRACT(HOUR FROM (hrrawd_shiftin::TIME)))*60)::NUMERIC) + ((EXTRACT(MINUTE FROM (hrrawd_timein::TIME)) - EXTRACT(MINUTE FROM (hrrawd_shiftin::TIME)))::NUMERIC) AS Total I'm using the ALIAS for the formula of total? for example "(shiftinhours + shiftinminutes) as Total" is that possible?

dnoeth Over a year ago

Yes, but then you can't use the "Total" alias in the CASE unless you nest it in another Derived Table.

Collectives™ on Stack Overflow

Error using column alias to make operations in PostgreSQL

3 Answers 3

1 Comment

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related