Skip to Content
0
Sep 10, 2018 at 11:40 AM

Can you please help me to combine three SQL queries, shown below into one?

167 Views

Hello!

Can you please help me to combine three SQL queries, shown below into one?

1 – Discount for a product
SELECT Distinct 'true' from oedg t0 RIGHT JOIN edg1 t1 ON t0."AbsEntry" = t1."AbsEntry" where t0."Type" = 'A' and t1."ObjKey" = $[$38.1.0] and t1."Discount" < $[$38.15.Number]

2 – Discount for a Business Partner and Group of Products
SELECT Distinct 'true' from oedg t0 right join edg1 t1 on t0."AbsEntry" = t1."AbsEntry" where t0."Type" = 'S' and t1."ObjKey" = TO_VARCHAR((select t2."ItmsGrpCod" from oitm t2 where t2."ItemCode" = $[$38.1.0])) and t1."Discount" < $[$38.15.Number] and t0."ObjCode" = $[$4.0.0]

3 – Discount for a Business Partner’s Group and Group of Products
SELECT Distinct 'true' from oedg t0 right join edg1 t1 on t0."AbsEntry" = t1."AbsEntry" where t0."Type" = 'C' and t0."ObjCode" != '0' and t0."ObjCode" = TO_VARCHAR((select t2."GroupCode" from ocrd t2 where t2."CardCode" = $[$4.0.0])) and t1."ObjKey" = TO_VARCHAR((select t3."ItmsGrpCod" from oitm t3 where t3."ItemCode" = $[$38.1.0])) and t1."Discount" < $[$38.15.Number]

What we need to do: We use a “Discount Group” feature, and some settings within this feature (discount for a product; discount for a product and business partner’s group; discount for a business partner and product groups). We want to develop one combined query for approval process in documents “Order” and ”Invoice” A/R that will check the field $[$38.15.0] for a manual filling and compare with information in OEDG and EDG1 tables.

When I apple those three queries separately, we get incorrect results, and we need them to be checked all at once. Δ