Hi
I have got this table over here
[1]: /answers/storage/attachments/116385-be6c2f7zq3c71.png
The right 3 columns has to appear as rows, I thought a left join would achieve it but I think an union would do it. I need advice on how can I achieve this as I understand union has to have the same number of rows, I added fake column but I am still getting errors.
select d.PlanDate, d.ProcessUnitID, d. xProfID, (d.DActualcumtotal - d.DTargetcumtotal) as Result, x.xProfileID, x.JobOrderName,x.WorkNumber, p.ProcessUnitName, i.TotalAvailable, i.InvxID, i.InvProcType, c.CoDate,c.CoTotalAmount,c.CoxProfID from FullTable d
LEFT JOIN xProfile x
ON d.xProfID = x.xProfileID
LEFT JOIN ProcessUnit p
ON d.ProcessUnitID = p.ProcessUnitID
LEFT JOIN Inventory i
ON d.xProfID = i.InvxID and d.ProcessUnitID=i.InvProcUnit and d.PlanDate = i.InvDate
LEFT JOIN Counting c
ON d.xProfID = c.CoxProfID and d.PlanDate = c.CoDate
where d.PlanDate = '2021-07-09'
order by d.xProfID
Basically, I need the counting table columns to appear as rows if the c.CoDate matches the where clause. How can I achieve this ?
Right now, I am getting this error
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
