Neuer Kurs! Jeder Programmierer sollte generative KI lernen!
Kostenlose Lektion ausprobieren0
Kindly someone tell me what is happening in this query ..
select 'BCHR' tp, value(i.itnbr,p.product) product, value(i.lbhno,p.batch) batch, case when value(r.itnbr,h.itnbr) is null then ' ' else 'X' end Ssts , value(cyymmdd2d(i.expdt,800101),p.edate ) expdt, value(cyymmdd2d(i.mfgdt,800101),p.mdate ) mdate, (Select value(min(ewmcty),p.coo,' ') from itmlotcoo c left join coocde o on o.coocty=c.coocty left join EWMXCTP on XACTY=cocd where c.itnbr=k.itnbr and c.lbhno=k.lbhno ) ewmcty case when p.product is null and i.itnbr is not null then 'I'
2 Antworten
+ 1
it's easier to read sql when properly formated...
SELECT 'BCHR' tp,
value(i.itnbr,p.product) product,
value(i.lbhno,p.batch) batch,
CASE
WHEN value(r.itnbr,h.itnbr) IS NULL THEN ' '
ELSE 'X'
END ssts ,
value(Cyymmdd2d(i.expdt,800101),p.edate ) expdt,
value(Cyymmdd2d(i.mfgdt,800101),p.mdate ) mdate,
(
SELECT value(Min(ewmcty),p.coo,' ')
FROM itmlotcoo c
LEFT JOIN coocde o
ON o.coocty=c.coocty
LEFT JOIN ewmxctp
ON xacty=cocd
WHERE c.itnbr=k.itnbr
AND c.lbhno=k.lbhno ) ewmcty
case
WHEN p.product IS NULL
AND i.itnbr IS NOT NULL THEN 'I'
essentially they have created a custom set of columns to be returned. the select queries between the brackets are the results for a column.
it looks like there is the use of the value clause which reads xml see here how to use -
https://msdn.microsoft.com/en-us/library/ms178030.aspx
there is also use of a case statement which allows you to have different data displayed of different conditions. think of it as an if statement.
see here -
https://msdn.microsoft.com/en-us/library/ms181765.aspx
the one column is then the minimum value from a sub query which has several joins taking place to get to the required table.
this is one of the best visual explanations of joins-
https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
with the links I have sent you I'm sure you will be able to understand this query.
0
thank you.. so much