Skip to Content
0

How to make a calculated view subquery from another calculated view

Apr 28, 2017 at 08:41 AM

63

avatar image

Hello,

I explain my doubt.

I have a query in SQL:

("MAC_Pedido_Factura" is a view created by me in SAP)

SELECT b."DocEntry", a."DocStatus",b."LineStatus", a."DocNum", b."LineNum"
FROM OPOR a  LEFT OUTER JOIN POR1 b ON a."DocEntry" = b."DocEntry" 
WHERE (a."DocEntry" NOT IN (SELECT "DocEntry" FROM "MAC_Pedido_Factura"                                 WHERE ("DocEntry" =b."DocEntry")
                            AND ("DocStatus" = 'C')                                                     AND (CANCELED = 'N')                                                   AND (IFNULL("DocentryFC",IFNULL("DocentryE",0))= 0)))

And I'm trying to do it with the SAP HANA Studio in graphical mode. For this I have created two calculated views, CV_1 and CV_2,

CV_1 would be:

SELECT  b."DocEntry", a."DocStatus",
b."LineStatus", a."DocNum", b."LineNum"FROM OPOR a  LEFT OUTER JOIN POR1 b ON
a."DocEntry" = b."DocEntry"

And in the graphical mode I have it like this: CV_1

And CV_2 would be:

SELECT "DocEntry" FROM "MAC_Pedido_Factura" 
WHERE ("DocEntry" = b."DocEntry") 
AND ("DocStatus" = 'C') 
AND (CANCELED = 'N') 
AND (IFNULL("DocentryFC",IFNULL("DocentryE",0)) = 0))

And in graphical mode I did it like this: CV_2

The question I have is how do I make the CV_2 view the subquery of the CV_1 view,

 WHERE (a. "DocEntry" NOT IN (SELECT "DocEntry" FROM "MAC_Form") .... 

What should another JOIN do between the two views? Is there any other way?

Can anyone help me in this?

Regards, Alfonso

cv-1.png (16.6 kB)
cv-2.png (18.5 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers