CODEWITHSUNDEEP
sqlpostgresql
Ralph Robert Caritativo
Ralph Robert Caritativo

Reputation: 21

Error using column alias to make operations in PostgreSQL

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'

Upvotes: 2

Views: 987

Answers (3)

roman
roman

Reputation: 117485

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

Upvotes: 4

dnoeth
dnoeth

Reputation: 60482

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

Upvotes: 1

Tomas Greif
Tomas Greif

Reputation: 22663

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;

Upvotes: 1

Related Questions