Buenas tardes, estoy tratando de crear un query que me de los siguientes datos (itemcode, barcode, itemname, unitprice, UOMprice) de un artículo en específico, pero algo está mal creo
los 42.17 es el UOM price y los 1.15 es el precio regular por unidad
en el query obtengo esto:
pero el query deberia mostrar ambos precios en una sola línea o registro no en dos separadas, no sé como puedo corregir esto?
este es el query actual:
select t1.itemcode as ItemCode, t1.CodeBars as CodeBars, t1.ItemName as ItemName, MAX(case when T0.PriceList = 2 then t0.Price else null end) as UOMPRICE, t1.UpdateDate,
MAX(case when T0.PriceList = 1 then t0.Price else null end) as UNITPRICE,
CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode from ITM1 T0
inner join oitm t1 on t0.itemcode = t1.itemcode
inner join ouom t2 on t2.uomentry = t0.uomentry and t0.Price <> '0.000000' where t1.ItemCode='32436'
GROUP BY t1.itemcode, t1.CodeBars, t1.ItemName, t1.UpdateDate, t1.VatGourpSa
union
select t1.itemcode as ItemCode, t1.CodeBars as CodeBars, t1.ItemName as ItemName, MAX(case when T0.PriceList = 1 then t0.Price else null end) as UOMPRICE, t1.UpdateDate,
MAX(case when T0.PriceList = 2 then t0.Price else null end) as Price2,
CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode from ITM9 T0
inner join oitm t1 on t0.itemcode = t1.itemcode
inner join ouom t2 on t2.uomentry = t0.uomentry and t0.Price <> '0.000000' where t1.ItemCode='32436'
GROUP BY t1.itemcode, t1.CodeBars, t1.ItemName, t1.UpdateDate, t1.VatGourpSa```