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

Transact-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

2

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
		)