Skip to Content
avatar image
-1
Former Member

HANA Stored procedure logic to loop through each line item

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"

Add comment
10|10000 characters needed characters exceeded

  • Former Member

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

  • Get RSS Feed

4 Answers

  • Jan 12 at 11:50 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

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

  • avatar image
    Former Member
    Jan 12 at 06:03 PM

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

    I need to do the following for each document number.

    Example

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 15 at 11:35 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • Jan 12 at 05:24 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.