Are union statements supported?

[CassidyHunt]

Just purchased and installed. Trying to play with it using some of my past queries wrote in notepad. Software doesn't seem to like to parse however it runs the query against the database fine. I think the Union is what is giving it trouble. Below is the complex query:

Code: Select all

select c.cust_order_id,
       c.line_no,
       c.misc_reference reference,
       c.order_qty quantity,
       c.trade_disc_percent discount,
       c.unit_price unitprice,
       (c.order_qty * woc.act_unit_cost) cost
  from (select a.type,
               a.base_id,
               a.lot_id,
               a.split_id,
               a.sub_id,
               a.allocated_qty,
               a.desired_qty,
               a.received_qty,
               a.fulfilled_qty,
               decode(a.desired_qty,
                      null,
                      0,
                      0,
                      0,
                      round((((a.act_material_cost + a.act_labor_cost +
                            a.act_burden_cost + a.act_service_cost) /
                            a.desired_qty)),
                            2)) act_unit_cost,
               decode(a.desired_qty,
                      null,
                      0,
                      0,
                      0,
                      round((((a.est_material_cost + a.est_labor_cost +
                            a.est_burden_cost + a.est_service_cost) /
                            a.desired_qty)),
                            2)) est_unit_cost
          from work_order a) woc,
       demand_supply_link b,
       cust_order_line c
 where woc.Base_ID = b.supply_base_id
   and woc.Lot_ID = b.supply_lot_id
   and woc.Split_ID = b.supply_split_id
   and woc.Sub_ID = 0
   and woc.type = 'W'
   and b.supply_type = 'WO'
   and b.demand_type = 'CO'
   and b.demand_base_id = c.cust_order_id
   and b.demand_seq_no = c.line_no
   and c.cust_order_id = :CustomerOrderID
union all
select c.cust_order_id,
       c.line_no,
       d.description reference,
       c.order_qty quantity,
       c.trade_disc_percent discount,
       d.unit_price unitprice,
       (c.order_qty * (d.unit_material_cost + d.unit_labor_cost +
       d.unit_burden_cost + d.unit_service_cost)) cost
  from part d, cust_order_line c
 where d.id = c.part_id
   and c.part_id is not null
   and c.cust_order_id = :CustomerOrderID
union all
select c.cust_order_id,
       c.line_no,
       c.misc_reference reference,
       c.order_qty quantity,
       c.trade_disc_percent discount,
       c.unit_price unitprice,
       0 cost
  from cust_order_line c
 where c.cust_order_id = :CustomerOrderID
   and (c.cust_order_id, c.line_no) not in
       (select b.demand_base_id, b.demand_seq_no
          from demand_supply_link b
         where b.demand_type = 'CO'
           and b.demand_base_id = :CustomerOrderID)
    and c.part_id is null

Simple query that won't parse but runs:

Code: Select all

select 'yellow' as a, 'green' as b from dual
union
select 'black','red' from dual

I have even tried it with real tables and not dual to rule out dual as table.

Thanks

Cassidy[/code]

[Duke]

Indeed, Query Builder does not support unions. Each query in union must be designed separately.

[CassidyHunt]

Ouch. Thats painful. Most of my complex queries are unions to avoid large table scans. I use it to build a bridge view for instance to scan small pieces of what I want and then only have to make one pass over the larger table.

How do most people get around this?

[Duke]

By now, the only workaround is this:
* Open SQL editor with query and Query Builder for design.
* Copy one select statement (part of the union) and paste it into Text tab of Query Builder.
* Design query and copy text back to the original document.