Trouble with union and grouping

Question

I know this has been a topic here before but I'm still having trouble grouping within unions. I've looked here: Grouping and Joining a Unioned Table. Having Problems among others and am still confused as to what to do with the parenthesis. Here is my code:

SELECT
      W.WORK_ORDER_NO,
      W.WORK_CLASS,
      SD.WORK_ORDER_TASK_NO,
      sd.ATTRIBUTE_VALUE"Outage Type",
      sd2.attribute_value"Total Time Water Off",
      WT.TASK_STATUS,
      wt.area,
      wt.creation_date,
      sd3.attribute_value"Homes Affected",
      sd4.attribute_value"Businesses Affected",
      sum(sm.CT_REGULAR_HOURS)"Reg Hours",
      sum(sm.CT_PREMIUM_HOURS)"OT Hours"
    FROM
    (
    SELECT
      W.WORK_ORDER_NO,
      W.WORK_CLASS,
      SD.WORK_ORDER_TASK_NO,
      sd.ATTRIBUTE_VALUE"Outage Type",
      sd2.attribute_value"Total Time Water Off",
      WT.TASK_STATUS,
      wt.area,
      wt.creation_date,
      sd3.attribute_value"Homes Affected",
      sd4.attribute_value"Businesses Affected",
      sm.CT_REGULAR_HOURS"Reg Hours",
      sm.CT_PREMIUM_HOURS"OT Hours"
    FROM
      SA_WORK_ORDER W,
      SA_WORK_ORDER_TASK WT,
      SA_SERVICE_HISTORY_DETAILS SD,
      SA_SERVICE_HISTORY_DETAILS SD2,
      SA_SERVICE_HISTORY_DETAILS SD3,
      SA_SERVICE_HISTORY_DETAILS SD4,
      smu_transactions sm
    WHERE
      SD.SPECIFICATION_CATEGORY = 'WATER OUTAGE'
    AND W.WORK_ORDER_NO         = WT.WORK_ORDER_NO
    AND
      (
        WT.WORK_ORDER_NO        = SD.WORK_ORDER_NO
      AND WT.WORK_ORDER_TASK_NO = SD.WORK_ORDER_TASK_NO
      )
    AND
      (
        sd.work_order_no        = sd2.work_order_no
      AND sd.work_order_task_no = sd2.work_order_task_no
      )
    AND
      (
        sd.work_order_no        = sd3.work_order_no
      AND sd.work_order_task_no = sd3.work_order_task_no
      )
    AND
      (
        sd.work_order_no        = sd4.work_order_no
      AND sd.work_order_task_no = sd4.work_order_task_no
      )
    AND SD.SPECIFICATION_CATEGORY  = 'WATER OUTAGE'
    AND sd.attribute_desc          = 'Type of Outage'
    AND SD2.SPECIFICATION_CATEGORY = 'WATER OUTAGE'
    AND sd2.attribute_desc         = 'Total Time Water Off'
    AND SD3.SPECIFICATION_CATEGORY = 'WATER OUTAGE'
    AND sd3.attribute_desc         = 'Number of Homes affected'
    AND SD4.SPECIFICATION_CATEGORY = 'WATER OUTAGE'
    AND sd4.attribute_desc         = 'Number of Businesses affected'
    AND WT.TASK_STATUS            != 'CANCELED'
    AND sm.transaction_type        = 'CT'

    UNION ALL

    SELECT
      W.WORK_ORDER_NO,
      W.WORK_CLASS,
      SD.WORK_ORDER_TASK_NO,
      sd.ATTRIBUTE_VALUE"Outage Type",
      sd2.attribute_value"Total Time Water Off",
      WT.TASK_STATUS,
      wt.area,
      wt.creation_date,
      sd3.attribute_value"Homes Affected",
      sd4.attribute_value"Businesses Affected",
      SUM(sm.CT_REGULAR_HOURS)"Reg Hours",
      SUM(sm.CT_PREMIUM_HOURS)"OT Hours"
    FROM
    (  SELECT
        W.WORK_ORDER_NO,
        W.WORK_CLASS,
        SD.WORK_ORDER_TASK_NO,
        sd.ATTRIBUTE_VALUE"Outage Type",
        sd2.attribute_value"Total Time Water Off",
        WT.TASK_STATUS,
        wt.area,
        wt.creation_date,
        sd3.attribute_value"Homes Affected",
        sd4.attribute_value"Businesses Affected",
        sm.CT_REGULAR_HOURS"Reg Hours",
        sm.CT_PREMIUM_HOURS"OT Hours"
    FROM
      SA_WORK_HISTORY W,
      SA_WORK_HISTORY_TASK WT,
      SA_WORK_HISTORY_SERVICE_DETAIL SD,
      SA_WORK_HISTORY_SERVICE_DETAIL SD2,
      SA_WORK_HISTORY_SERVICE_DETAIL SD3,
      SA_WORK_HISTORY_SERVICE_DETAIL SD4,
      smu_transactions sm
    WHERE
      SD.SPECIFICATION_CATEGORY = 'WATER OUTAGE'
    AND W.WORK_ORDER_NO         = WT.WORK_ORDER_NO
    AND
      (
        WT.WORK_ORDER_NO        = SD.WORK_ORDER_NO
      AND WT.WORK_ORDER_TASK_NO = SD.WORK_ORDER_TASK_NO
      )
    AND
      (
        sd.work_order_no        = sd2.work_order_no
      AND sd.work_order_task_no = sd2.work_order_task_no
      )
    AND
      (
        sd.work_order_no        = sd3.work_order_no
      AND sd.work_order_task_no = sd3.work_order_task_no
      )
    AND
      (
        sd.work_order_no        = sd4.work_order_no
      AND sd.work_order_task_no = sd4.work_order_task_no
      )
    AND
      (
        sd.work_order_no        = sm.WORK_ORDER_NO
      AND sd.work_order_task_no = sm.work_order_task_no
      )
    AND SD.SPECIFICATION_CATEGORY  = 'WATER OUTAGE'
    AND sd.attribute_desc          = 'Type of Outage'
    AND SD2.SPECIFICATION_CATEGORY = 'WATER OUTAGE'
    AND sd2.attribute_desc         = 'Total Time Water Off'
    AND SD3.SPECIFICATION_CATEGORY = 'WATER OUTAGE'
    AND sd3.attribute_desc         = 'Number of Homes affected'
    AND SD4.SPECIFICATION_CATEGORY = 'WATER OUTAGE'
    AND sd4.attribute_desc         = 'Number of Businesses affected'
    AND WT.TASK_STATUS            != 'CANCELED'
    AND sm.transaction_type        = 'CT'
    )
    GROUP BY
      sm.CT_REGULAR_HOURS,
      sm.CT_PREMIUM_HOURS
      ORDER BY
      sd.work_order_no,
      sd.WORK_ORDER_TASK_NO;

The error is: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action: Error at Line: 152 Column: 23

I've tried many different permutations of the parenthesis locations and understand how it should work but I still can't seem to grasp where to put them (or if that is the problem at all).

TIA for any help you can give!

CodeNewbie · Accepted Answer

I simplified your query to arrive at this pseudo query and the missing paranthesis was immediately apparent.

SELECT
  set_of_rows
FROM
(
SELECT
 set_of_rows
FROM
  set_of_tables
WHERE
  set_of_conditions ) //You missed this closing paranthesis
                    ^
UNION ALL

SELECT
  set_of_rows
FROM
(  SELECT
    set_of_rows
FROM
  set_of_tables
WHERE
  set_of_conditions
)
GROUP BY
  some_rows
ORDER BY
  some_other_rows

Trouble with union and grouping

Answers (2)

Related Questions