on 01-19-2014 6:15 AM
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:
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.