HANA Stored procedure logic to loop through each line item

[Former Member]

Hi,

I need some urgent help.

I'm need to join BKPF and BSEG tables and using fields like BKPF.TCODE, BSEG.KOART I have to derive a new column value with some flag.

My end result needs to have one entry per document number and one category based on my conditions. But I need to evaluate each line item for multiple conditions and based on the overall result I need to derive the flag value per document number.

I am trying to use a case statement for the conditions and a subquery but I am having difficulty figuring out how to loop through each line item and derive the flag for each document number. This is an example of the query I am trying to use for one of the rules I have. Thank you and any help is greatly appreciated!

SELECT "BELNR, 'XXXXXX' AS CATEGORY

FROM "table_BKPF_BSEG" A

WHERE"BELNR" IN

(SELECT B."BELNR "FROM "table_BSEG"

WHERE A."BELNR" = B."BELNR"

AND ((A."TCODE"='XXXX' AND B."KOART"='X')

OR

A."TCODE"='XXXX')

GROUPBY "BELNR"

)

ORDERBY "BELNR"

[Former Member]

Hi, more info would be appreciated. Could you upload sample of tables and which result are expected ?

[eralper_yilmaz]

Could you please check following SQLScript Select statement

You will realize I used CASE statements with EXISTS()

select
BUKRS,
BELNR,
case 
    when tcode = 'FB5' 
        then 
            case 
            when 
            exists (
                select * from BSEG 
                where 
                BSEG.bukrs = BKPF.bukrs and
                BSEG.belnr = BKPF.belnr and
                koart <> 'S'
            )
            then NULL 
            else 'GL' 
            end 
    when tcode = 'FB1' 
        then 
            case 
            when 
            exists (
                select * from BSEG 
                where 
                BSEG.bukrs = BKPF.bukrs and
                BSEG.belnr = BKPF.belnr and
                koart = 'D'
            )
            then 'V' 
            else NULL 
            end 
    else null 
end as category
from BKPF;

Output for following sample data (I created BSEG and BKPF on my development schema)

create table BSEG (
bukrs varchar(4),
belnr varchar(10),
gjahr int,
busei int,
koart varchar(2)
)
create table BKPF (
bukrs varchar(4),
belnr varchar(10),
gjahr int,
tcode varchar(10)
)
insert into BSEG select '2122','1400000081',2016,3,'S' from dummy;
insert into BSEG select '2122','1400000081',2016,2,'S' from dummy;
insert into BSEG select '2122','1400000081',2016,1,'D' from dummy;
insert into BSEG select '8123','1400000081',2016,2,'S' from dummy;
insert into BSEG select '8123','1400000081',2016,1,'S' from dummy;
insert into BKPF select '2122','1400000081',2016,'FB1' from dummy;
insert into BKPF select '8123','1400000081',2016,'FB5' from dummy;

The output is

[Former Member]

Thank you for the answer but I have a lot of complicated conditions that need to be applied. My basic requirement is how do I check for koart and tcode values per document for each line item and derive values based on the following examples:

1. Atleast one line item has koart=s and tcode=fb1 or fb4

2. All line items have koart=d and atleast one line item has tcode=f5

I tried executing the code you have given above and the result I get is not considering all line items and deriving one flag per document.

[Former Member]

To be more clear here is sample of some dummy data that I created:

I need to do the following for each document number.

[eralper_yilmaz]

Please check following JOIN condition

SELECT
   A."BELNR", 
   CASE ....  AS CATEGORY
FROM "table_BKPF_BSEG" A
LEFT JOIN "table_BSEG" B
   ON  (A."BELNR" = B."BELNR") AND
     ((A."TCODE"='XXXX' AND B."KOART"='X') OR A."TCODE"='XXXX')
ORDER BY A."BELNR"

This JOIN enables you to use columns from table aliased sa B in your CASE statement

Please also check your JOIN condition or ON clause because

((A."TCODE"='XXXX' AND B."KOART"='X') OR A."TCODE"='XXXX')

is exactly same with

(A."TCODE"='XXXX')

of course if XXXX maps to the same code in both places

[Former Member]

The values that I specified as XXXX are dummy values. TCODE has 2 different values.

[akshinde]

You need correlated query , for every outer table records it would go inside inner table records , filters get applied depending upon join between inner and outer table , and this way you can derive flag for each document, i am not expert in ERP tables but this is how structure would be of correlated query

Ex : OUT_TABLE WITH COL1,COL2,COL3 This is Outer Table

IN_TABLE WITH COL4,COL5,COL6 This is inner table

SELECT A.COL1,A.COL2,A.COL3 FROM OUT_TABLE A

WHERE EXISTS ----- You can add your A table specific where clause in this outer query

(SELECT B.COL3,B.COL4,B.COL5 FROM IN_TABLE B WHERE A.COL1=B.COL4 ) -- In this inner query you can add Table B specific where clause

[Former Member]

Thank you for the reply. When I use subquery and EXISTS as above, I get the result if atleast one row fulfills this condition. But I need to check for each row and then arrive at a flag value.