Query: Overwrite data in one column with static value

[former_member268870]

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

[KennedyT21 · Accepted solution]

You mean T0.Remqty * -1

try this

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]*-1 , T0.[customer]

FROM OSCL T0

ORDER BY T0.[CardCode]

[julie_jamieson2]

Just replacing T0.RemQty with -1 should work