szadroga
asked on
SQL SELECT reference a field in a CASE statement
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_timesta mp (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 ;
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.
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.
ASKER
can an example for using a subquery be provided. I am looking for the time difference between the 2 alias fields package_assignment_timesta mp and form_download_timestamp.
I am not too familiar with joining subqueries...
I am not too familiar with joining subqueries...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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;
closeout-adoption-report.xlsx
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
The subsequent issue of aligning the fields into one row is on a different question
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