Solved

SQL SELECT reference a field in a CASE statement

Posted on 2014-09-23
7
198 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 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

19 Experts available now in Live!

Get 1:1 Help Now