SQLTeam.com | Weblogs | Forums

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


#1

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