SAP HANA Query Creation ERROR

[former_member593234]

Dear All,

I am trying to make new query report name "PO-GR-RT-AP Report".

But it's not taking, kindly help to create this.

SELECT T1."DocEntry" as "Link", T1."[DocNum]" as "PO No.", T1."[DocDate]" as "PO Date", T1."DocStatus" as "PO Status", T1."[CardName]" as "Vendor Name", T9."SlpName", T10."firstName" AS "PO Owner", T8."FrgnName" AS "Part No." , T0."[Dscription]" as "Part Name", T0."[Quantity]" as "PO Qty",T0."[Price]" as "purc Price", T0."[OpenQty]", T0."[OpenSum]", T3."DocNum" as "GRPO Doc Num", T2."[Quantity]" as "GRPO Quantity", T12."DocNum" AS "Return No", T12."DocDate" as "Return Date", T11."Quantity" as "Return Qty" , T5."DocNum" as "APinvoice No", T5."DocDate" as "APinvoice Date", T5."DocStatus" as "APinvoice Status" , T4."Quantity" as "APinvoice Qty", T5."DocTotal",T5."PaidToDate" as "Applied Amt", T7."DocNum" as "Credit Note No.", T7."DocDate" as "Credit Note date",T6."Quantity" as "Credit Note Qty" FROM "POR1" T0 INNER JOIN "OPOR" T1 ON T0."DocEntry" = T1."DocEntry" left outer join "PDN1" T2 on T2."BaseEntry" = T0."DocEntry" and T2."BaseLine" = T0."Linenum" left outer join "OPDN" T3 on T2."DocEntry" = T3."DocEntry" left Outer join "PCH1" T4 on T4."BaseEntry" = T3."DocEntry" and T4."BaseLine" = T2."Linenum" and T4."BaseType" = 20 -- OR (T4."Basetype"=17 and T4."BaseEntry"=T0."DocEntry" and T4."BaseLine"=T0."LineNum") LEFT outer join "RPD1" T11 on T11."BaseEntry" = T2."DocEntry" and T11."BaseLine" = T2."LineNum" LEFT outer join "ORPD" T12 on T11."DocEntry" = T12."DocEntry" left outer join "OPCH" T5 on T5."DocEntry" = T4."DocEntry" left Outer join "RPC1" T6 on T6."BaseEntry" = T5."DocEntry" and T6."BaseLine" = T4."Linenum" left outer join "ORPC" T7 on T6."DocEntry" = T7."DocEntry" left outer join "OITM" T8 on T0."ItemCode" = T8."ItemCode" left outer join "OSLP" T9 on T9."SlpCode" = T1."SlpCode" left outer join "OHEM" T10 on T10."empID" = T1."OwnerCode" WHERE T1."[DocDate]" >='[%1]' and T1."[DocDate] <='[%2]' Group by T1."DocEntry", T1."DocNum",T1."DocDate",T1."DocStatus",T1."CardName", T9."SlpName",T10."firstName",T8."FrgnName",T0."[Dscription]" , T0."[Quantity]" ,T0."[Price]", T0."[OpenQty]", T0."[OpenSum]", T3."DocNum" , T2."[Quantity]" , T5."DocNum" , T5."DocDate", T5."DocStatus" , T4."Quantity", T5."DocTotal",T5."PaidToDate" , T7."DocNum" , T7."DocDate" ,T6."Quantity",T12."DocNum",T12."DocDate",T11."Quantity"

Thanks & Regards,

ATHUL KM

[kothandaraman_nagarajan]

Hi,

Try this query,

SELECT T1."DocEntry",T1."DocNum",T1."DocDate",T1."DocStatus",T1."CardName",T9."SlpName",T10."firstName",T8."FrgnName", T0."Dscription",T0."Quantity",T0."Price",T0."OpenQty",T0."OpenSum",T3."DocNum", T2."Quantity", T12."DocNum", T12."DocDate",T11."Quantity",T5."DocNum",T5."DocDate",T5."DocStatus",T4."Quantity", T5."DocTotal",T5."PaidToDate",T7."DocNum",T7."DocDate",T6."Quantity" FROM POR1 T0 INNER JOIN OPOR T1 ON T0."DocEntry" = T1."DocEntry" left outer join PDN1 T2 on T2."BaseEntry" = T0."DocEntry" and T2."BaseLine" = T0."LineNum" left outer join OPDN T3 on T2."DocEntry" = T3."DocEntry" left Outer join PCH1 T4 on T4."BaseEntry" = T3."DocEntry" and T4."BaseLine" = T2."LineNum" and T4."BaseType" = '20' --OR (T4."Basetype"=17 and T4."BaseEntry"=T0."DocEntry" and T4."BaseLine"=T0."LineNum") LEFT outer join RPD1 T11 on T11."BaseEntry" = T2."DocEntry" and T11."BaseLine" = T2."LineNum" LEFT outer join ORPD T12 on T11."DocEntry" = T12."DocEntry" left outer join OPCH T5 on T5."DocEntry" = T4."DocEntry" left Outer join RPC1 T6 on T6."BaseEntry" = T5."DocEntry" and T6."BaseLine" = T4."LineNum" left outer join ORPC T7 on T6."DocEntry" = T7."DocEntry" left outer join OITM T8 on T0."ItemCode" = T8."ItemCode" left outer join OSLP T9 on T9."SlpCode" = T1."SlpCode" left outer join OHEM T10 on T10."empID" = T1."OwnerCode" WHERE T5."DocDate" >=[%1] and T1."DocDate"<=[%2] AND T5."CardName" BETWEEN [%3] AND [%4] AND T4."ItemCode" BETWEEN [%4] AND [%4] Group by T1."DocEntry", T1."DocNum",T1."DocDate",T1."DocStatus",T1."CardName", T9."SlpName",T10."firstName", T8."FrgnName",T0."Dscription" , T0."Quantity" ,T0."Price", T0."OpenQty", T0."OpenSum", T3."DocNum" , T2."Quantity" , T5."DocNum" , T5."DocDate", T5."DocStatus" , T4."Quantity", T5."DocTotal",T5."PaidToDate" , T7."DocNum" , T7."DocDate" ,T6."Quantity",T12."DocNum",T12."DocDate",T11."Quantity"

Regards,

Nagarajan

[former_member593234]

Could you please revise this without PO?

[jitin_chawla]

Hi,

For your reference, to create HANA queries refer:

https://www.dngsystems.com/wp-content/uploads/2018/11/How_to_Convert_SQL_from_MS_SQL_Server_to_SAP_H...

Check this for your query. Enter the Alias name as required as per the pdf link above:

SELECT T1."DocEntry",T1."DocNum",T1."DocDate",T1."DocStatus",T1."CardName",T9."SlpName",T10."firstName",T8."FrgnName", 
T0."Dscription",T0."Quantity",T0."Price",T0."OpenQty",T0."OpenSum",T3."DocNum", T2."Quantity", T12."DocNum", 
T12."DocDate",T11."Quantity",T5."DocNum",T5."DocDate",T5."DocStatus",T4."Quantity", 
T5."DocTotal",T5."PaidToDate",T7."DocNum",T7."DocDate",T6."Quantity" 
FROM POR1 T0 INNER JOIN OPOR T1 ON T0."DocEntry" = T1."DocEntry" 
left outer join PDN1 T2 on T2."BaseEntry" = T0."DocEntry" and T2."BaseLine" = T0."LineNum" 
left outer join OPDN T3 on T2."DocEntry" = T3."DocEntry" 
left Outer join PCH1 T4 on T4."BaseEntry" = T3."DocEntry" and T4."BaseLine" = T2."LineNum" and
T4."BaseType" = '20' 
--OR (T4."Basetype"=17 and T4."BaseEntry"=T0."DocEntry" and T4."BaseLine"=T0."LineNum") 
LEFT outer join RPD1 T11 on T11."BaseEntry" = T2."DocEntry" and T11."BaseLine" = T2."LineNum" 
LEFT outer join ORPD T12 on T11."DocEntry" = T12."DocEntry" 
left outer join OPCH T5 on T5."DocEntry" = T4."DocEntry" 
left Outer join RPC1 T6 on T6."BaseEntry" = T5."DocEntry" and T6."BaseLine" = T4."LineNum" 
left outer join ORPC T7 on T6."DocEntry" = T7."DocEntry" 
left outer join OITM T8 on T0."ItemCode" = T8."ItemCode" 
left outer join OSLP T9 on T9."SlpCode" = T1."SlpCode" 
left outer join OHEM T10 on T10."empID" = T1."OwnerCode"
WHERE T1."DocDate" >=[%1] and T1."DocDate"<=[%2]
Group by T1."DocEntry", T1."DocNum",T1."DocDate",T1."DocStatus",T1."CardName", T9."SlpName",T10."firstName",
T8."FrgnName",T0."Dscription" , T0."Quantity" ,T0."Price", T0."OpenQty", T0."OpenSum", T3."DocNum" , 
T2."Quantity" , T5."DocNum" , T5."DocDate", T5."DocStatus" , T4."Quantity", T5."DocTotal",T5."PaidToDate" ,
T7."DocNum" , T7."DocDate" ,T6."Quantity",T12."DocNum",T12."DocDate",T11."Quantity"

[former_member593234]

Thank you Mr. Jitin for the valuable response.

Actually we are not creating PO, we are using direct GRPO then AP.

So kindly help to reverse the query with selection criteria for the below.

Date

BP Name

Item Code