How to convert below code in CTE[Answered] RSS

• • Reply
  jsshivalik

  Member

  140 Points

  518 Posts

  How to convert below code in CTE

  Jul 28, 2020 11:27 AM|jsshivalik|LINK

  Hi

  Select Batchnum , Itemname ,DocDate
  from
  (Select A.BatchNum as Batchnum ,B.ItemName as ItemName ,
  (Select InDate from OBTN where DistNumber = A.BatchNum and ItemCode = b.ItemCode) as DocDate
  from IBT1 A
  Inner Join OITM B on A.ItemCode = B.ItemCode
  ) as A
  group by Batchnum , Itemname ,Whscode,DocDate
  

  Thanks

• • Reply
  imapsp

  Participant

  1120 Points

  296 Posts

  Re: How to convert below code in CTE

  Jul 28, 2020 12:34 PM|imapsp|LINK

  Hi,

  Try:

  with CTE_A as
  (
      Select 
          A.BatchNum as Batchnum,
          B.ItemName as ItemName,
          (Select InDate from OBTN where DistNumber = A.BatchNum and ItemCode = b.ItemCode) as DocDate
      from IBT1 A
      Inner Join OITM B on A.ItemCode = B.ItemCode
  )
  
  Select Batchnum, Itemname, DocDate
  from CTE_A 
  group by Batchnum, Itemname, DocDate

  Hope this help

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  jsshivalik

  Member

  140 Points

  518 Posts

  Re: How to convert below code in CTE

  Jul 28, 2020 12:54 PM|jsshivalik|LINK

  HI imapsp

     I want below code also in Cte 

  Select Batchnum, Itemname, DocDate
  from CTE_A 
  group by Batchnum, Itemname, DocDate

  Thanks

• • Reply
  imapsp

  Participant

  1120 Points

  296 Posts

  Re: How to convert below code in CTE

  Jul 28, 2020 02:55 PM|imapsp|LINK

  Hi,

  with 
      CTE_A1 as
      (
          Select 
              A.BatchNum as Batchnum,
              B.ItemName as ItemName,
              (Select InDate from OBTN where DistNumber = A.BatchNum and ItemCode = b.ItemCode) as DocDate
          from IBT1 A
          Inner Join OITM B on A.ItemCode = B.ItemCode
      ),
  
      CTE_A2 as
      (
          Select Batchnum, Itemname, DocDate
          from CTE_A 
          group by Batchnum, Itemname, DocDate
      )
  
  

  Hope this help

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  jsshivalik

  Member

  140 Points

  518 Posts

  Re: How to convert below code in CTE

  Jul 28, 2020 04:08 PM|jsshivalik|LINK

  Hi

     Resolved

  Thanks