Skip to Content
0
Jul 30, 2016 at 10:46 AM

Query: Overwrite data in one column with static value

52 Views

Experts,

I use the Union All function to show Deliveries, Inventory Transfers and Service Call details.

When it shows the Service call details, I need the quantity field to be -1. At this stage, I have it pulling the RemQty as a placeholder.

But when it pulls Deliveries or Inv. Transfers, it needs to pull the REAL quantity.

Here is what I have:

SELECT T0.[DocNum], T0.[ObjType], T0.[CardCode], T0.[CardName], T0.[DocDate], T1.[ItemCode], T1.[Quantity], T1.[WhsCode]

FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

UNION ALL

SELECT T0.[DocNum], T0.[ObjType], T0.[CardCode], T0.[CardName], T0.[DocDate], T1.[ItemCode], T1.[Quantity], T1.[WhsCode]

FROM OWTR T0 INNER JOIN WTR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

UNION ALL

SELECT T0.[callID], T0.[ObjType], T0.[customer], T0.[custmrName], T0.[createDate], T0.[ItemCode], T0.[RemQty], T0.[customer]

FROM OSCL T0

ORDER BY T0.[CardCode]

Your help would be much appreciated,

Marli