HanaQuery Not supported using SAP DIS object

[surajitkundu]

Hi Expert ,

I have a environment where SAP B1 installed with Hana DB . I want read some data using HanaQuery with DIS object DoQuery . below is the format of soap Request for execute query :

<?xml version="1.0" encoding="UTF-8"?>

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">

<env:Header>

<SessionID>5E9448C9-4FA9-40B9-B5AA-299E9B1DE9D8</SessionID>

</env:Header>

<env:Body>

<dis:ExecuteSQL xmlns:dis="http://www.sap.com/SBO/DIS">

<DoQuery><![CDATA[SELECT T0."ItemCode", CASE WHEN SUM((TS."OnHand" - TS."IsCommited")) + (SELECT IFNULL(SUM(TX."U_OnHand"), 0) FROM OSCN TX INNER JOIN OCRD TY ON TY."CardCode" = TX."CardCode" AND TY."U_AGL_WebStock" = 'Y' WHERE TX."ItemCode" = T0."ItemCode") < 0 THEN 0 ELSE SUM((TS."OnHand" - TS."IsCommited")) + (SELECT IFNULL(SUM(TX."U_OnHand"), 0) FROM OSCN TX INNER JOIN OCRD TY ON TY."CardCode" = TX."CardCode" AND TY."U_AGL_WebStock" = 'Y' WHERE TX."ItemCode" = T0."ItemCode") END AS "TotalStock", CASE T0."U_LastStock" WHEN '' THEN 0 ELSE T0."U_LastStock" END AS "LastStock", T0."U_WebID" FROM OITM T0 INNER JOIN OITW TS ON TS."ItemCode" = T0."ItemCode" INNER JOIN OWHS TW ON TW."WhsCode" = TS."WhsCode" AND TW."U_AGL_WebStock" = 'Y' WHERE IFNULL(T0."U_WebID", '') <> '' GROUP BY T0."ItemCode", T0."U_WebID", T0."U_LastStock" HAVING (CASE T0."U_LastStock" WHEN '' THEN 0 ELSE T0."U_LastStock" END) <> CASE WHEN SUM((TS."OnHand" - TS."IsCommited")) + (SELECT IFNULL(SUM(TX."U_OnHand"), 0) FROM OSCN TX INNER JOIN OCRD TY ON TY."CardCode" = TX."CardCode" AND TY."U_AGL_WebStock" = 'Y' WHERE TX."ItemCode" = T0."ItemCode") < 0 THEN 0 ELSE SUM((TS."OnHand" - TS."IsCommited")) + (SELECT IFNULL(SUM(TX."U_OnHand"), 0) FROM OSCN TX INNER JOIN OCRD TY ON TY."CardCode" = TX."CardCode" AND TY."U_AGL_WebStock" = 'Y' WHERE TX."ItemCode" = T0."ItemCode") END ORDER BY T0."ItemCode"]]></DoQuery>

</dis:ExecuteSQL>

</env:Body>

</env:Envelope>

Scinario :

1) Success : above soap request format is working if we just change the complex query to simple query (select "ItemCode" FROM "OITM" )

2) Error : Below is the response data

<?xml version="1.0"?><env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope"><env:Body><env:Fault><env:Code><env:Value>env:Receiver</env:Value><env:Subcode><env:Value>339</env:Value></env:Subcode></env:Code><env:Reason><env:Text xml:lang="en">Failed to execute command</env:Text></env:Reason><env:Detail><Statement>SELECT TOP 50 T0."ItemCode", CASE WHEN SUM((TS."OnHand" - TS."IsCommited")) + (SELECT IFNULL(SUM(TX."U_OnHand"), 0) FROM OSCN TX INNER JOIN OCRD TY ON TY."CardCode" = TX."CardCode" AND TY."U_AGL_WebStock" = 'Y' WHERE TX."ItemCode" = T0."ItemCode") < 0 THEN 0 ELSE SUM((TS."OnHand" - TS."IsCommited")) + (SELECT IFNULL(SUM(TX."U_OnHand"), 0) FROM OSCN TX INNER JOIN OCRD TY ON TY."CardCode" = TX."CardCode" AND TY."U_AGL_WebStock" = 'Y' WHERE TX."ItemCode" = T0."ItemCode") END AS "TotalStock", CASE T0."U_LastStock" WHEN '' THEN 0 ELSE T0."U_LastStock" END AS "LastStock", T0."U_WebID" FROM OITM T0 INNER JOIN OITW TS ON TS."ItemCode" = T0."ItemCode" INNER JOIN OWHS TW ON TW."WhsCode" = TS."WhsCode" AND TW."U_AGL_WebStock" = 'Y' WHERE IFNULL(T0."U_WebID", '') <> '' GROUP BY T0."ItemCode", T0."U_WebID", T0."U_LastStock" HAVING (CASE T0."U_LastStock" WHEN '' THEN 0 ELSE T0."U_LastStock" END) <> CASE WHEN SUM((TS."OnHand" - TS."IsCommited")) + (SELECT IFNULL(SUM(TX."U_OnHand"), 0) FROM OSCN TX INNER JOIN OCRD TY ON TY."CardCode" = TX."CardCode" AND TY."U_AGL_WebStock" = 'Y' WHERE TX."ItemCode" = T0."ItemCode") < 0 THEN 0 ELSE SUM((TS."OnHand" - TS."IsCommited")) + (SELECT IFNULL(SUM(TX."U_OnHand"), 0) FROM OSCN TX INNER JOIN OCRD TY ON TY."CardCode" = TX."CardCode" AND TY."U_AGL_WebStock" = 'Y' WHERE TX."ItemCode" = T0."ItemCode") END ORDER BY T0."ItemCode"</Statement><Command>ExecuteSQL</Command><SessionID>A2E60F08-8DA8-45C5-A716-76B899C8F671</SessionID></env:Detail></env:Fault></env:Body></env:Envelope>

3) above Query executed successfully in HanaQury also

Thanks & Advance

Surajit