CODEWITHSUNDEEP
mysqlunion
Gusti Aldi
Gusti Aldi

Reputation: 201

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.

Upvotes: 1

Views: 77

Answers (1)

sagi
sagi

Reputation: 40481

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.

Upvotes: 2

Related Questions