How to convert SQL Query to Hanna ?

[Former Member]

Dear All,

Any one can help me to convert below mention SQL query to hanna ?

With PO_Sale as (

Select OITM .ItemCode ,OITM .ItemName ,IBT1 .BatchNum ,

sum(case when OINM .TransType IN (15,16) then OINM .OutQty - OINM .InQty else 0 end) as [Sale Qty],

sum(case when OINM .TransType IN (15,16) then OINM .Price * (OINM .OutQty - OINM .InQty) else 0 end)/sum(case when OINM .TransType IN (15,16) then OINM .OutQty - OINM .InQty else 0 end)  as [Avg Sale Price] ,

sum(case when OINM .TransType IN (15,16) then OINM .Price * (OINM .OutQty - OINM .InQty) else 0 end) as  [Sale Total],

sum(case when OINM .TransType IN (59,60) then OINM .InQty - OINM .OutQty else 0 end) as [PO Qty],

sum(case when OINM .TransType IN (59,60) then OINM .TransValue else 0 end)/sum(case when OINM .TransType IN (59,60) then OINM .InQty - OINM .OutQty else 0 end) as [Avg PO Cost] ,

sum(case when OINM .TransType IN (59,60) then OINM .TransValue else 0 end) as [Cost of PO]

from IBT1 inner join OINM on IBT1 .BaseEntry  = OINM .CreatedBy  and IBT1 .BaseNum = OINM .BASE_REF and IBT1 .ItemCode = OINM .ItemCode and

IBT1 .BaseLinNum = OINM .DocLineNum and IBT1 .BaseType = OINM .TransType and IBT1 .WhsCode = OINM .Warehouse

inner join OITM on OITM .ItemCode = OINM .ItemCode

where  OINM .TransType in (15,16,59,60)

group by OITM .ItemCode ,OITM .ItemName ,IBT1 .BatchNum

),RawMaterial_Lab_Foh as

(

Select IBT1 .BatchNum ,OITM .ItemCode as [Material Code],OITM .ItemName as[Material Name],OITB .ItmsGrpCod ,OITB .ItmsGrpNam

,sum(WOR1 .IssuedQty) as [Issued Qty] ,

sum(isnull(WOR1 .CompTotal/nullif(WOR1 .IssuedQty,0),0)) as [Unit Cost], sum(WOR1 .CompTotal) as [Total Cost]

from (select Distinct BatchNum ,BsDocEntry  ,BsDocType from IBT1 where IBT1 .BsDocType = 202 ) IBT1

inner join WOR1 on IBT1 .BsDocEntry = WOR1 .DocEntry and IBT1 .BsDocType = 202 and WOR1 .IssueType = 'B'

inner join OITM on OITM .ItemCode = WOR1 .ItemCode

inner join OITB on OITM .ItmsGrpCod = OITB .ItmsGrpCod

group by IBT1 .BatchNum ,OITM .ItemCode,OITM .ItemName ,OITB .ItmsGrpCod ,OITB .ItmsGrpNam

union all

Select IBT1 .BatchNum ,OITM .ItemCode ,OITM .ItemName,OITB .ItmsGrpCod ,OITB .ItmsGrpNam

,sum(OINM .OutQty - OINM .InQty) as [Issued Qty] , avg(OINM .CalcPrice) as [Unit Cost], sum(OINM .TransValue)*-1 as [Total Cost]

from (Select Distinct BatchNum ,BsDocEntry,BsDocType,ItemCode From IBT1 where IBT1 .BsDocType = 202)IBT1

inner join OINM on IBT1 .BsDocEntry = OINM .AppObjAbs and IBT1 .BsDocType = OINM .ApplObj and IBT1 .ItemCode <> OINM .ItemCode

inner join OITM on OITM .ItemCode = OINM.ItemCode

inner join OITB on OITM .ItmsGrpCod = OITB .ItmsGrpCod

group by IBT1 .BatchNum ,OITM .ItemCode ,OITM .ItemName,OITB .ItmsGrpCod ,OITB .ItmsGrpNam

)

Select PO_Sale .*,RawMaterial_Lab_Foh.[Material Code]  ,RawMaterial_Lab_Foh .[Material Name],

RawMaterial_Lab_Foh.ItmsGrpCod,RawMaterial_Lab_Foh .ItmsGrpNam ,RawMaterial_Lab_Foh .[Issued Qty] ,OBTN.MnfDate,OBTN.ExpDate,

RawMaterial_Lab_Foh .[Unit Cost] ,RawMaterial_Lab_Foh .[Total Cost]

from OBTN Left outer Join PO_Sale on OBTN .DistNumber = PO_Sale .BatchNum

left outer join RawMaterial_Lab_Foh on OBTN .DistNumber = RawMaterial_Lab_Foh .BatchNum

[kothandaraman_nagarajan]

Hi,

Please check this document:

Thanks & Regards,

Nagarajan

[Former Member]

Is any one can support ?

[kothandaraman_nagarajan]

Did you check attached document?

[Former Member]

yes but cnt understand .

[former_member184146]

Hi,

    Please remove your UDF's and UDO's from the query and post it again.

Regards,

Manish

[Former Member]

Manish, there is no UDFs and UDOs in the query .

[former_member184146]

Sorry , I will check for you and update you.

Regards,

Manish