Gusti Aldi Gusti Aldi - 6 months ago 11
MySQL Question

MySQL table get the wrong data after union

I tried to make view using union for 3 table, every table works succesfully with the right data. after i try union, the data on totalqtyspb is get the wrong data.

datatype is varchar.

here's my code

SELECT
p.PONumber,
p.POdate,
p.customername,
p.description,
p.poqty,
SUM(q.deliveryqty) AS TotalQtySpb,
p.poqty-SUM(q.deliveryqty) AS OTSPO
FROM
tb_po p
INNER JOIN
tb_rls q ON p.PONumber = q.PONumber AND p.description = q.description
GROUP BY
p.PONumber,
p.PODate,
p.customername,
p.description,
p.poqty
UNION
SELECT
PONumber,
PODate,
CustomerName,
Description,
POQty,
'0' AS TotalQtySpb,
poqty AS OTSPO
FROM
tb_po
WHERE
poreleasedate = ''
UNION
SELECT
PONumber,
PODate,
CustomerName,
Description,
POQty,
'0' AS TotalQtySpb,
poqty AS OTSPO
FROM
tb_po
WHERE
poreleasedate != '' AND OutboundInbound = ''


i cant explain why data on totalqtyspb get the wrong data, already trying change to int datatype still not works.

Answer

I suspect it because you are selecting TotalQtySpb once as an integer, and twice as a varchar , so try replacing in the third and second query this:

  '0' AS TotalQtySpb,

To this:

  0 AS TotalQtySpb,

Although you didn't say what is wrong, so its just a guess.