cancel
Showing results for 
Search instead for 
Did you mean: 

Cal view SQL Script procedure

Former Member
0 Kudos

Hi Experts,

I am trying to write a HANA stored procedure (to build a SQL
Script Cal View) and need help with coding for the below requirement. I have 3
tables A , B and C.

Table A has the following fields and data:

S.NO    PO# , CompCode,  PurchaseOrg
1          101       1100                 310

2          102       3100                 320

3          103       5100                 330

Table B has the following fields and data

S.NO    PO#, PurchOrg,  InvPartyCode, VendorCode

1          101       310                   BF                    510

2          101       310                   LA                    510                   

3          101       310                   RS                    510

4          102       320                   BF                    520

5          102       320                   LA                    520

6          103       330                   BF                    530

7          103       330                   LA                    530

8          103       330                   RS                    530

Table C has the following fields and data

S.NO    VendorCode CompanyCode

1          510                   1100

2          510                   3100

3          510                   5100

4          520                   1100

5          520                   3100

6          520                   5100

7          530                   1100

8          530                   3100

So out of these 3 tables, the final result set data should
be this:

S.No     PO#      CompCode       VendorCode

  1          102        3100                 520

  2          103        5100                 530

Explanation why only PO#’s 102 and 103 should come in the
output set:

  1. Good Case: For each PO# in table A, if an entry exists in table B where “InvPartyCode = RS” for the
    PO#, and also if the Vendor Code in table B corresponding to that PO, is
    extended to the correct company code in table C . This is good and should not
    show on the  output.

Ex: PO# 101 ,Co Code = 1100 for
this PO case in Table A, there are 3 records in table B , out of which one record
has InvPartyCode = RS and Vendor Code corresponding to it is 510, which when
looked up in Table C is extended to 3 CompCodes,out of which one CoCode(1100)
matches to the CoCode listed on Table A.

Hence this is good and will not show on output.

2.BadCase: For each PO# in table A, if an entry exists in table B where “InvPartyCode not equal to
RS” for the PO#. This PO# is BAD and should show on the  output.

Hence you see PO# 102 is BAD and will show the expected output

3. BadCase: For each
PO# in table A, if an entry exists in table B where “InvPartyCode although
equal to RS” for the PO#, but the Vendor Code is not extended to the correct
company code when checked in Table C. This PO# is BAD and should show on the  output.

Ex: PO# 103 ,Co Code = 5100 for
this PO case in Table A, there are 3 records in table B , out of which one record
has InvPartyCode = RS and Vendor Code corresponding to it is 530, which when
looked up in Table C is extended to only 2 CompCodes, and none of them matches
to what was listed on Table A .Hence PO# 103 is BAD and will show on output.

Please help with writing the coding procedure.

Accepted Solutions (1)

Accepted Solutions (1)

former_member182302
Active Contributor
0 Kudos

Hello Prabhu,

I have loaded the sample data as per your scenario into 3 tables i.e TableA,TableB and TableC

Please find the script i have used in Calc View (Script based):


/********* Begin Procedure Script ************/

BEGIN

T1= SELECT

A."PO"

FROM

(SELECT

"PO",

"PURCHORG",

"INVPARTYCODE",

B."VENDORCODE",

C."COMPCODE"

FROM (SELECT * FROM "SYSTEM"."TABLEB" WHERE "INVPARTYCODE" = 'RS')

B LEFT OUTER JOIN "SYSTEM"."TABLEC" C

ON (B.VENDORCODE = C.VENDORCODE)) B

INNER JOIN "SYSTEM"."TABLEA" A

ON (A.PO = B.PO and A.COMPCODE = B.COMPCODE);

var_out =

select

A."PO",

A."COMPCODE",

"VENDORCODE"

from "SYSTEM"."TABLEA" A

LEFT OUTER JOIN "SYSTEM"."TABLEB" B on A."PO" = B."PO"

WHERE A."PO" not in ( Select "PO" from :T1)

GROUP BY

A."PO",

A."COMPCODE",

"VENDORCODE" ;

END /********* End Procedure Script ************/

Output:

Please be informed that the logic i have written may need to be revisited with more amount of data and see if it is functionally correct.

I hope this will be a good starting point to start your modelling.

PS: i have used only tables here, it is better that i use Attribute Views for joins .

So do validate it functionally and correct the SQL and then we can tune it further using the models available in SAP HANA.

Regards,

Krishna Tangudu

Answers (0)