November 18, 2015 at 1:05 am
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
November 18, 2015 at 1:17 am
This will help others. I've taken the query, removed the spurious additional spaces and reformatted it:
create view INV_SUPPLY
as
(select RRN(BAL) as SEQ
,BAL.ISTORE as isTORE
,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 - 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 TRFFL C
,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)
);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2015 at 1:58 am
Thanks a lot for your reply, as i said earlier i am not so good in SQL, Can you please just explain me how the value of SUPPLY is given i need the explanation to it. I am basically from AS400 part and use basic select or create statements. Here in the above view creation there are multiple When condition with few coalesce statements which i am not at all aware of. Can you please help.
Regards
Nilesh
November 18, 2015 at 2:07 am
nileshbgp123 (11/18/2015)
Thanks a lot for your reply, as i said earlier i am not so good in SQL, Can you please just explain me how the value of SUPPLY is given i need the explanation to it. I am basically from AS400 part and use basic select or create statements. Here in the above view creation there are multiple When condition with few coalesce statements which i am not at all aware of. Can you please help.Regards
Nilesh
Use APPLY to strip down the elements of the calculation:
CREATE VIEW INV_SUPPLY AS (
SELECT
SEQ = RRN(BAL),
ISTORE = BAL.ISTORE,
INUMBR = BAL.INUMBR,
b.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
CROSS APPLY (
SELECT
IBHLDQ = CASE WHEN bal.IBHLDQ < 0 THEN 0 ELSE bal.IBHLDQ END,
TRANSFERRED = CASE WHEN TRF.TRANSFERRED > 0 THEN TRF.TRANSFERRED ELSE 0 END
) a
CROSS APPLY (
SELECT SUPPLY = CASE WHEN (bal.IBHAND - a.IBHLDQ - a.TRANSFERRED) > 0 THEN (bal.IBHAND - a.IBHLDQ - a.TRANSFERRED) ELSE 0 END
) b
WHERE b.SUPPLY > 0
)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 18, 2015 at 2:09 am
nileshbgp123 (11/18/2015)
Thanks a lot for your reply, as i said earlier i am not so good in SQL, Can you please just explain me how the value of SUPPLY is given i need the explanation to it. I am basically from AS400 part and use basic select or create statements. Here in the above view creation there are multiple When condition with few coalesce statements which i am not at all aware of. Can you please help.Regards
Nilesh
I don't have the time to decompose it fully, sorry.
But I will give you some relevant info.
The CASE construction works as follows:
case when condition1 then value when condition2 then value2 else value3 end
Conditions are evaluated from left to right. There can be any number of them.
COALESCE works as follows:
COALESCE(val1, val2, ..., valn) returns the first non-NULL item in the list of arguments.
COALESCE(val, 0) is an ugly way of returning zero when the value is NULL (better to use IsNull(val,0)).
Based on this, I suggest you break the statement into chunks.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply