Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL SELECT reference a field in a CASE statement

Posted on 2014-09-23
7
Medium Priority
?
232 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 66

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

963 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