Kindly someone tell me what is happening in this query ..

Question

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'

Answer 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.

Answer 2

thank you.. so much