Results 1 to 3 of 3
  1. #1
    Join Date
    May 2011
    Posts
    12

    Unanswered: What's Group by syntax in db2

    Query:

    SELECT DISTINCT p.pid, p.name_last, p.name_first, p.date_birth, p.title, p.sex, p.photo_filename, e.insurance_class_nr,
    e.encounter_type, e.is_discharged, e.in_ward, e.discharge_date, e.discharge_time, e.admission_dt, e.encounter_nr, e.current_ward_nr AS ward_nr,
    e.current_room_nr AS room_nr, b.date_from AS ward_date, b.date_to,w.roomprefix, w.name AS ward_name, b.location_nr AS bed_nr,
    b.nr AS bed_loc_nr, i.name AS insurance_name, i.LD_var AS "insurance_LDvar", n.nr AS ward_notes FROM hisdb_ojt2011.care_person AS p
    CROSS JOIN LATERAL (SELECT encounter_nr FROM hisdb_ojt2011.care_encounter AS enc WHERE p.pid=enc.pid AND enc.is_discharged=0
    AND enc.encounter_status <> 'cancelled' AND enc.status NOT IN ('deleted','hidden','inactive','void')
    ORDER BY enc.encounter_date DESC FETCH FIRST 1 ROWS ONLY) enc
    INNER JOIN care_encounter as e ON e.encounter_nr = enc.encounter_nr
    LEFT JOIN care_encounter_location AS r ON e.encounter_nr=r.encounter_nr
    LEFT JOIN care_encounter_location AS b ON (r.encounter_nr=b.encounter_nr AND r.group_nr=b.group_nr AND b.type_nr=5
    AND b.status NOT IN ('discharged','closed','deleted','hidden','inactiv e','void') AND b.date_from<='2011-05-12'
    AND ('2011-05-12'<=b.date_to OR b.date_to ='0001-01-01') )
    LEFT JOIN care_encounter_location AS c
    ON (r.encounter_nr=c.encounter_nr AND r.group_nr=c.group_nr AND c.type_nr=2 AND c.status NOT IN ('discharged','closed','deleted','hidden','inactiv e','void')
    AND c.date_from<='2011-05-12' AND ('2011-05-12'<=c.date_to OR c.date_to ='0001-01-01') )
    LEFT JOIN care_ward AS w ON w.nr=c.location_nr
    LEFT JOIN care_class_insurance AS i ON e.insurance_class_nr=i.class_nr
    LEFT JOIN care_encounter_notes AS n ON b.encounter_nr=n.encounter_nr AND n.type_nr=6 WHERE r.type_nr=4
    AND r.status NOT IN ('discharged','closed','deleted','hidden','inactiv e','void')
    AND ('2011-05-12'<=r.date_to OR r.date_to ='0001-01-01') AND ( name_last LIKE '%')
    AND encounter_type IN (3,4)
    GROUP BY encounter_nr

    It has error saying: A reference to column "ENCOUNTER_NR" is ambiguous..

    i try : GROUP BY e.encounter_nr
    another error: An expression starting with "NR" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified..

    helpppppzzzz....!!!!!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The error message explained enough straitly.

    All column names in a SELECT clause should be specified in GROUP BY clause,
    or use aggregate functions(i.e. MAX, MIN, SUM etc.) for columns(or expressions) not in GROUP BY clause.

    For example:
    "n.nr AS ward_notes"(last element of outer most SELECT clause) should be "MAX(n.nr) AS ward_notes"(or use MIN),
    or include n.nr to GROUP BY clause.
    Last edited by tonkuma; 05-12-11 at 01:59. Reason: Changed to use capital letters for KEY WORDS.

  3. #3
    Join Date
    May 2011
    Posts
    12
    I see. Thanx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •