Solved

SQL SELECT reference a field in a CASE statement

Posted on 2014-09-23
7
200 Views
Last Modified: 2014-09-29
I am looking to reference a field I am creating with the CASE statement in lines 10 - 21.  I want to calculate the duration of package_assignment_timestamp (lines 18,19) between form_download_timestamp (lines 10, 11).  I tried using their aliases but I am getting the column does not exist error message.

SELECT
    public.assets.first_name || ' ' || public.assets.last_name AS team_member,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'Corporate'
        ELSE public.project_markets.market
                              END,
    closeout.packages.name AS package_name,
    events.event.name AS event_name,
    case
        when event.id = 1 then min(events.event_log.timestamp) end as form_download_timestamp,
     case
        when event.id = 1 then DATE_PART('day', now()::timestamp - min(events.event_log.timestamp)::timestamp) end as form_download_duration,
    case
        when event.id = 2 then min(events.event_log.timestamp) end as form_upload_timestamp,
    case
        when event.id = 2 then DATE_PART('day', now()::timestamp - min(events.event_log.timestamp)::timestamp) end as form_download_duration,
    case
        when event.id = 3 then min(events.event_log.timestamp) end as package_assignment_timestamp,
    case
        when event.id = 3 then DATE_PART('day', now()::timestamp - min(events.event_log.timestamp)::timestamp) end as form_download_duration,
    NOW() as today
FROM
    events.event
INNER JOIN
    events.event_log
ON
    (
        events.event.id = events.event_log.event_id)
INNER JOIN
    public.assets_user_xref
ON
    (
        events.event_log.user_id = public.assets_user_xref.user_id)
INNER JOIN
    public.assets
ON
    (
        public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
    public.unitek_pcs
ON
    (
        public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
    public.project_pc_market_xref
ON
    (
        public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
    public.project_markets
ON
    (
        public.project_pc_market_xref.market_id = public.project_markets.id)
INNER JOIN
    public.project_instance_people
ON
    (
        public.assets.id = public.project_instance_people.asset_id)
INNER JOIN
    closeout.package_instances
ON
    (
        public.project_instance_people.project_instance_id = closeout.package_instances.project_id)
INNER JOIN
    closeout.packages
ON
    (
        closeout.package_instances.package_id = closeout.packages.id)
WHERE
    events.event_log.event_id IN (3,
                                  2,
                                  1) 
group by events.event_log.timestamp, public.assets.first_name, public.assets.last_name, public.project_markets.market, closeout.packages.name, events.event.name, events.event.id  ;

Open in new window

0
Comment
Question by:szadroga
7 Comments
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40339110
hello,

You can not use alias in the case in the same SQL query

Either you should use the real column name or try another query above the main then you can use alias
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40339126
You can't use an alias in one SELECT column in another, as the query engine (I think) processes the entire SELECT clause together.

Perhaps a workaround would be to throw the line 10-11 part into some kind of subquery, and then the main query will have all columns, joined on the subquery, and can use the subquery alias columns.
0
 

Author Comment

by:szadroga
ID: 40339179
can an example for using a subquery be provided.  I am looking for the time difference between the 2 alias fields package_assignment_timestamp and form_download_timestamp.

I am not too familiar with joining subqueries...
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40339410
one column (col 2) has no alias, some columns have the same alias (I added 1 2 3), these need to be sorted out; but for this question:

You are going to have to "push down" you current query one level to enable access to column aliases for this calculation, something like this:
select
        team_member
      , whatever
      , package_name
      , event_name
      , form_download_timestamp
      , form_download_duration1
      , form_upload_timestamp
      , form_download_duration2
      , package_assignment_timestamp
      , form_download_duration3
      , today

, do your column difference calculation here using aliases

from (
      SELECT
               PUBLIC.assets.first_name || ' ' || PUBLIC.assets.last_name AS team_member
             , CASE WHEN PUBLIC.project_markets.market IS NULL THEN 'Corporate' ELSE PUBLIC.project_markets.market END AS whatever
             , closeout.packages.NAME AS package_name
             , events.event.NAME AS event_name
             , CASE WHEN event.id = 1 THEN min(events.event_log.TIMESTAMP) END AS form_download_timestamp
             , CASE WHEN event.id = 1 THEN DATE_PART('day', now()::TIMESTAMP - min(events.event_log.TIMESTAMP)::TIMESTAMP) END AS form_download_duration1
             , CASE WHEN event.id = 2 THEN min(events.event_log.TIMESTAMP) END AS form_upload_timestamp
             , CASE WHEN event.id = 2 THEN DATE_PART('day', now()::TIMESTAMP - min(events.event_log.TIMESTAMP)::TIMESTAMP) END AS form_download_duration2
             , CASE WHEN event.id = 3 THEN min(events.event_log.TIMESTAMP) END AS package_assignment_timestamp
             , CASE WHEN event.id = 3 THEN DATE_PART('day', now()::TIMESTAMP - min(events.event_log.TIMESTAMP)::TIMESTAMP) END AS form_download_duration3
             , NOW() AS today
      FROM events.event
      INNER JOIN events.event_log ON (events.event.id = events.event_log.event_id)
      INNER JOIN PUBLIC.assets_user_xref ON (events.event_log.user_id = PUBLIC.assets_user_xref.user_id)
      INNER JOIN PUBLIC.assets ON (PUBLIC.assets_user_xref.assets_id = PUBLIC.assets.id)
      FULL JOIN PUBLIC.unitek_pcs ON (PUBLIC.assets.pc6 = PUBLIC.unitek_pcs.pc6)
      FULL JOIN PUBLIC.project_pc_market_xref ON (PUBLIC.unitek_pcs.id = PUBLIC.project_pc_market_xref.pc_id)
      FULL JOIN PUBLIC.project_markets ON (PUBLIC.project_pc_market_xref.market_id = PUBLIC.project_markets.id)
      INNER JOIN PUBLIC.project_instance_people ON (PUBLIC.assets.id = PUBLIC.project_instance_people.asset_id)
      INNER JOIN closeout.package_instances ON (PUBLIC.project_instance_people.project_instance_id = closeout.package_instances.project_id)
      INNER JOIN closeout.packages ON (closeout.package_instances.package_id = closeout.packages.id)
      WHERE events.event_log.event_id IN (3, 2, 1)
      GROUP BY
               events.event_log.TIMESTAMP
             , PUBLIC.assets.first_name
             , PUBLIC.assets.last_name
             , PUBLIC.project_markets.market
             , closeout.packages.NAME
             , events.event.NAME
             , events.event.id
      ) IQ
;

Open in new window

This query is subject to several questions at once, I suggest you get the basic query stable before adding more to it.
0
 

Author Comment

by:szadroga
ID: 40346493
i used your suggestion and I am getting NULL values for the duration.  My thought is because the 2 timestamps are not in the same row within the dataset so the calculation is null - timestamp or timestamp - null and NOT timestamp - timestamp. here is my query.

i have also attached the result data set from the subquery.

SELECT
fname,
lname,
market,
package_name,
form_upload - form_download as duration

FROM
(SELECT
    public.assets.first_name as fname,
    public.assets.last_name as lname,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'Corporate'
        ELSE public.project_markets.market
    END as market,
    closeout.packages.name as package_name,
    MIN(CASE
        WHEN events.event_log.event_id = 3
        THEN events.event_log.timestamp
    END) AS package_assignment,
    MIN(CASE
        WHEN events.event_log.event_id = 1
        THEN events.event_log.timestamp
    END) AS form_download,
    MIN(CASE
        WHEN events.event_log.event_id = 2
        THEN events.event_log.timestamp
    END) AS form_upload
FROM
    events.event
INNER JOIN
    events.event_log
ON
    (
        events.event.id = events.event_log.event_id)
INNER JOIN
    public.assets_user_xref
ON
    (
        events.event_log.user_id = public.assets_user_xref.user_id)
INNER JOIN
    public.assets
ON
    (
        public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
    public.unitek_pcs
ON
    (
        public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
    public.project_pc_market_xref
ON
    (
        public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
    public.project_markets
ON
    (
        public.project_pc_market_xref.market_id = public.project_markets.id)
INNER JOIN
    public.project_instance_people
ON
    (
        public.assets.id = public.project_instance_people.asset_id)
INNER JOIN
    closeout.package_instances
ON
    (
        public.project_instance_people.project_instance_id = closeout.package_instances.project_id)
INNER JOIN
    closeout.packages
ON
    (
        closeout.package_instances.package_id = closeout.packages.id)
WHERE
    events.event_log.event_id IN (3,
                                  2,
                                  1)
GROUP BY public.assets.first_name, public.assets.last_name, public.project_markets.market, events.event.name, events.event_log.event_id, packages.name

ORDER BY assets.last_name, package_name ASC) as duration_table;

Open in new window

closeout-adoption-report.xlsx
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40347114
This question is about how to use an alias in a calculation.

The subsequent issue of aligning the fields into one row is on a different question
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
MySQL left join performance 4 30
join 2 views with 5 conditions 3 46
Unable to save view in SSMS 21 60
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now