icustay_hourly.sql run error #1736

Activity

1. HuilingHU commented on Apr 30, 2024

   

   HuilingHU

   on Apr 30, 2024

   More actions

   you can try:
   the last line of the icustay_hourly.sql----> CROSS JOIN UNNEST(array[all_hours.hrs]) AS _t(hr_unnested)
2. theompm commented on Jul 23, 2024

   

   theompm

   on Jul 23, 2024

   More actions

   Hi i got the whole pipeline run by editing:
   mimic-iv/concepts_postgres/demographics/icustay_hourly.sql

   -- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
   DROP TABLE IF EXISTS mimiciv_derived.icustay_hourly; CREATE TABLE mimiciv_derived.icustay_hourly AS
   /* This query generates a row for every hour the patient is in the ICU. */ /* The hours are based on clock-hours (i.e. 02:00, 03:00). */ /* The hour clock starts 24 hours before the first heart rate measurement. */ /* Note that the time of the first heart rate measurement is ceilinged to */ /* the hour. */ /* this query extracts the cohort and every possible hour they were in the ICU */ /* this table can be to other tables on stay_id and (ENDTIME - 1 hour,ENDTIME] */ /* get first/last measurement time */
   WITH all_hours AS (
     SELECT
       it.stay_id, /* round the intime up to the nearest hour */
       CASE
         WHEN DATE_TRUNC('HOUR', it.intime_hr) = it.intime_hr
         THEN it.intime_hr
         ELSE DATE_TRUNC('HOUR', it.intime_hr) + INTERVAL '1 HOUR'
       END AS endtime, /* create integers for each charttime in hours from admission */ /* so 0 is admission time, 1 is one hour after admission, etc, */ /* up to ICU disch */ /*  we allow 24 hours before ICU admission (to grab labs before admit) */
       GENERATE_SERIES(-24, CAST(CEIL(EXTRACT(EPOCH FROM it.outtime_hr - it.intime_hr) / 3600.0) AS INT)) AS hrs /* noqa: L016 */
     FROM mimiciv_derived.icustay_times AS it
   )
   SELECT
     stay_id,
     CAST(all_hours.hrs AS BIGINT) AS hr,
     endtime + CAST(all_hours.hrs AS BIGINT) * INTERVAL '1 HOUR' AS endtime
   FROM all_hours
   -- CROSS JOIN UNNEST(all_hours.hrs) AS _t(hr_unnested)
3. chessing234 commented on Jul 16, 2026

   

   chessing234

   on Jul 16, 2026

   Contributor

   More actions

   On current main, postgres icustay_hourly wraps GENERATE_SERIES in ARRAY(...) before UNNEST, which should avoid unnest(integer). If you still hit it, re-pull concepts_postgres/demographics/icustay_hourly.sql.
4. chessing234 commented on Jul 16, 2026

   

   chessing234

   on Jul 16, 2026

   Contributor

   More actions

   Also covered for DuckDB in #2060 — GENERATE_ARRAY now transpiles to a list(...) so UNNEST works.
5. alistairewj commented on Jul 18, 2026

   

   alistairewj

   on Jul 18, 2026

   Member

   More actions

   fixed in recent transpilation work

   👍React with 👍1Reacted by Taksh Kothari
6. alistairewj

   closed this as completedon Jul 18, 2026
7. chessing234

   added a commit that references this issue on Jul 23, 2026

   fix: keep DuckDB GENERATE_ARRAY as a list for UNNEST

   ...

   2d4f3ae