Explode group by columns to avoid distinct

diff --git a/enyo-client/database/source/xt/views/invciteminfo.sql b/enyo-client/database/source/xt/views/invciteminfo.sql
index 5877cfe..f84bdf9 100644 (file)
--- a/enyo-client/database/source/xt/views/invciteminfo.sql
+++ b/enyo-client/database/source/xt/views/invciteminfo.sql
@@ -1,17 +1,59 @@
 select xt.create_view('xt.invciteminfo', $$
 
-  -- select distinct on allows us to add the aggregated tax total column
-  -- without having to inner join a temporarily grouped tax table or specify
-  -- two dozen group-bys
   select
-    invcitem.*,
+    invcitem_id,
+    invcitem_invchead_id ,
+    invcitem_linenumber,
+    invcitem_item_id,
+    invcitem_warehous_id,
+    invcitem_custpn,
+    invcitem_number,
+    invcitem_descrip,
+    invcitem_ordered,
+    invcitem_billed,
+    invcitem_custprice,
+    invcitem_price,
+    invcitem_notes,
+    invcitem_salescat_id,
+    invcitem_taxtype_id,
+    invcitem_qty_uom_id,
+    invcitem_qty_invuomratio,
+    invcitem_price_uom_id,
+    invcitem_price_invuomratio,
+    invcitem_coitem_id ,
+    invcitem_updateinv,
+    invcitem_rev_accnt_id,
+    invcitem.obj_uuid,
     case when invcitem_item_id = -1 then true else false end as invcitem_is_misc,
     invcitem_billed * invcitem_qty_invuomratio
       * (invcitem_price / invcitem_price_invuomratio) as invcitem_ext_price,
     sum(taxhist_tax) as invcitem_tax_total
   from invcitem
   left join invcitemtax on invcitem_id = taxhist_parent_id
-  group by invcitem_id
+  group by
+    invcitem_id,
+    invcitem_invchead_id ,
+    invcitem_linenumber,
+    invcitem_item_id,
+    invcitem_warehous_id,
+    invcitem_custpn,
+    invcitem_number,
+    invcitem_descrip,
+    invcitem_ordered,
+    invcitem_billed,
+    invcitem_custprice,
+    invcitem_price,
+    invcitem_notes,
+    invcitem_salescat_id,
+    invcitem_taxtype_id,
+    invcitem_qty_uom_id,
+    invcitem_qty_invuomratio,
+    invcitem_price_uom_id,
+    invcitem_price_invuomratio,
+    invcitem_coitem_id ,
+    invcitem_updateinv,
+    invcitem_rev_accnt_id,
+    invcitem.obj_uuid
 
 $$, false);