# Where does the calculation logic is on the “SUPPLY” column come from this SQL

Hello All,
I am not so very expert in SQL, Need you experts help. I have a SQL query view created long back, which has SUPPLY column being created. Want to know what is the calculation logic for it. Below is the view.

CREATE VIEW INV_SUPPLY AS ( SELECT RRN(BAL) AS SEQ, BAL.ISTORE AS IS
TORE, BAL.INUMBR AS INUMBR, CASE WHEN (IBHAND - CASE WHEN IBHLDQ < 0
THEN 0 ELSE IBHLDQ END - CASE WHEN COALESCE(TRF.TRANSFERRED, 0) < 0
THEN 0 ELSE COALESCE(TRF.TRANSFERRED, 0) END) > 0 THEN (IBHAND - CA
SE WHEN IBHLDQ < 0 THEN 0 ELSE IBHLDQ END - CASE WHEN COALESCE (TRF.
TRANSFERRED, 0) < 0 THEN 0 ELSE COALESCE(TRF.TRANSFERRED, 0) END) EL
SE 0 END AS SUPPLY FROM MM610LIB.INVBAL BAL LEFT JOIN ( SELECT TRFFL
C, INUMBR, SUM(TRFALC) AS TRANSFERRED FROM MM610LIB.TRFDTL WHERE TRF
STS IN ('A', 'P') GROUP BY TRFFLC, INUMBR ) TRF ON TRF.TRFFLC = BAL.
ISTORE AND TRF.INUMBR = BAL.INUMBR WHERE ( CASE WHEN (IBHAND - CASE
WHEN IBHLDQ< 0 THEN 0 ELSE IBHLDQ END - CASE WHEN COALESCE(TRF.TRANS
FERRED, 0) < 0 THEN 0 ELSE COALESCE(TRF.TRANSFERRED, 0) END) > 0 THE
N (IBHAND -CASE WHEN IBHLDQ < 0 THEN 0 ELSE IBHLDQ END - CASE WHEN C
OALESCE(TRF.TRANSFERRED, 0) < 0 THEN 0 ELSE COALESCE(TRF.TRANSFERRED
, 0) END) ELSE 0 END > 0) )

The INVBAL file has all the fields in it. even if you let me know the logic using the field name that would be great. Just want to understand the logic behind this creation. Thanks a lot in advance.

Regards
Nilesh

it will be clearer if it is formatted nicely

``````CREATE VIEW INV_SUPPLY
AS
SELECT RRN(BAL) AS SEQ,
BAL.ISTORE AS ISTORE,
BAL.INUMBR AS INUMBR,
-- definition of SUPPLY starts from here
CASE
WHEN (
IBHAND - CASE
WHEN IBHLDQ < 0
THEN 0
ELSE IBHLDQ
END - CASE
WHEN COALESCE(TRF.TRANSFERRED, 0) < 0
THEN 0
ELSE COALESCE(TRF.TRANSFERRED, 0)
END
) > 0
THEN (
IBHAND - CASE
WHEN IBHLDQ < 0
THEN 0
ELSE IBHLDQ
END - CASE
WHEN COALESCE(TRF.TRANSFERRED, 0) < 0
THEN 0
ELSE COALESCE(TRF.TRANSFERRED, 0)
END
)
ELSE 0
END AS SUPPLY
FROM MM610LIB.INVBAL BAL
LEFT JOIN (
SELECT TRFFLC,
INUMBR,
SUM(TRFALC) AS TRANSFERRED
FROM MM610LIB.TRFDTL
WHERE TRFSTS IN (
'A',
'P'
)
GROUP BY TRFFLC,
INUMBR
) TRF
ON TRF.TRFFLC = BAL.ISTORE
AND TRF.INUMBR = BAL.INUMBR
WHERE (
CASE
WHEN (
IBHAND - CASE
WHEN IBHLDQ < 0
THEN 0
ELSE IBHLDQ
END - CASE
WHEN COALESCE(TRF.TRANSFERRED, 0) < 0
THEN 0
ELSE COALESCE(TRF.TRANSFERRED, 0)
END
) > 0
THEN (
IBHAND - CASE
WHEN IBHLDQ < 0
THEN 0
ELSE IBHLDQ
END - CASE
WHEN COALESCE(TRF.TRANSFERRED, 0) < 0
THEN 0
ELSE COALESCE(TRF.TRANSFERRED, 0)
END
)
ELSE 0
END > 0
)``````