Devart Forums

Posted: **Mon 30 Mar 2009 21:56**

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]

Posted: **Wed 01 Apr 2009 14:51**

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

Posted: **Wed 01 Apr 2009 18:04**

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?

Posted: **Fri 03 Apr 2009 11:48**

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.

Devart Forums

Are union statements supported?

Are union statements supported?