Solved

SQL SELECT reference a field in a CASE statement

Posted on 2014-09-23
7
203 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 15

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

861 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