How to work with empty second table while using left outer join ?

[nidak2501]

Hi,

I am joining two tables using left outer join ,where first table lets say A contains customer and financial document ,posting date

while second table ( z table ) contains rate against a combination of customer , date range and amount range.

Now while testing ,table A contains values and custom table lets say B is not maintained- so no values , but output is coming blank.

i tried googling the issue and tried solution as shown

1: all possible condition of left outer join were mentioned in ON clause

2: tried including NULL values in where clause.

So desired output should display all values from table A irrespective of values found in table B or not

SELECT  FROM ZPD1( PBUKRS = @BUKRS-LOW ,POSTFROM = @COL_DATE-LOW ,POSTTO = @COL_DATE-HIGH ) AS A

      LEFT OUTER JOIN
      ZCUSTOM_TABLE  AS F ON  A~PROD_CATEGORY = F~PROD_CATEGORY
      AND  A~OD_DATE GE F~POST_DT_FROM AND  A~OD_DATE LE F~POST_DT_TO
      AND F~COL_DT_FROM GE @COL_DATE-LOW  AND F~COL_DT_TO LE @COL_DATE-HIGH
      AND F~SALES_OFFICE = @VKBUR-LOW
      AND CASE WHEN A~ARREAR_DAYS <= 0 THEN 'NOD' WHEN A~ARREAR_DAYS > 0 THEN 'OD' END = F~OD

here ZPD1 is View created as table A and ZCUSTOM_TABLE as table B,

VKBUR-LOW - selection screen element

COL_DATE-LOW and COL_DATE_HIGH - selection screen element

Where clause contains...

WHERE A~KUNNR IN @KUNNR
    AND  NOT EXISTS  ( SELECT * FROM ZFI_PAYMENT_DISC WHERE BELNR = A~BELNR
    AND GJAHR = A~GJAHR AND BUKRS = A~RBUKRS AND SALES_OFFICE = @VKBUR-LOW AND A~KUNNR IN @KUNNR AND OS_BELNR EQ A~OD_DOC   )
    AND A~ARREAR_DAYS <= @OD
    AND ( F~COL_AMT_FROM LE ( SELECT SUM( COLLECT ) FROM ZPD_HSL( BUKRS = @BUKRS-LOW ,BUDATFROM = @COL_DATE-LOW ,BUDATO = @COL_DATE-HIGH ) WHERE KUNNR = A~KUNNR )
    AND F~COL_AMT_TO GE ( SELECT SUM( COLLECT ) FROM ZPD_HSL( BUKRS = @BUKRS-LOW ,BUDATFROM = @COL_DATE-LOW ,BUDATO = @COL_DATE-HIGH ) WHERE KUNNR = A~KUNNR )
  )

Here ZPF_HSL is also a view

all elements following @ are selection screen elements

i even tried commenting this portion of where clause

WHERE A~KUNNR IN @KUNNR
    AND  NOT EXISTS  ( SELECT * FROM ZFI_PAYMENT_DISC WHERE BELNR = A~BELNR
    AND GJAHR = A~GJAHR AND BUKRS = A~RBUKRS AND SALES_OFFICE = @VKBUR-LOW AND A~KUNNR IN @KUNNR AND OS_BELNR EQ A~OD_DOC   )
    AND A~ARREAR_DAYS <= @OD
* AND ( F~COL_AMT_FROM LE ( SELECT SUM( COLLECT ) FROM ZPD_HSL( BUKRS = @BUKRS-LOW ,BUDATFROM = @COL_D*  ATE-LOW ,BUDATO = @COL_DATE-HIGH ) WHERE KUNNR = A~KUNNR )
*    AND F~COL_AMT_TO GE ( SELECT SUM( COLLECT ) FROM ZPD_HSL( BUKRS = @BUKRS-LOW ,BUDATFROM = @COL_DA* TE-LOW ,BUDATO = @COL_DATE-HIGH ) WHERE KUNNR = A~KUNNR )
*  )

still output is coming blank...

Regards

[nidak2501]

Hi,

Even if table B is maintained then only values from Table A with matching conditions on Table B is displayed.
its like Left outer join is behaving like inner join.

[former_member1716]

nidak2501,

Try to have only Key fields in the Join condition not all the possible fields (Unless it is required by business).

Also Check in the table directly through SE11, how many legal entries are expected to be outputted for the given where conditions.

Regards!

[nidak2501]

Hi Satish,

Thanks for commenting.

All these field in join conditions are keys fields only non key field is what i am using in output.

SE11 will only give me what i give it as input which is fine.

i want non matching values from table A in output as well , simply because user will know which customer - date -document etc combination does not have an entry from table B.

[former_member1716]

nidak2501.

Can you please explain about view ZPD1, is it a database view?

What are the conditions on which your view is created?

[nidak2501]

Hi Satish Kumar

ZPD1 is an ABAP CDS view using joins of ACDOCA,BSID table and BUKRS,BUDAT are passed to it as shown.

[former_member1716]

nidak2501,

What Join is implemented as part of the CDS View?

[nidak2501]

Hi Satish Kumar

Inner Join is used in ZPD1

[michael_piesche]

If you want to find the restriction that is causing this behaviour, you will have to reduce your select statement by as many restrictions as necessary, that still will give you an expected output.

• Start out with not having a left join and no restrictions for the joined table.
  Do you get the results you expected.
• If no, reduce more restrictions for your main table.
• If yes, add join conditions and restrictions for second table one by one.

That way, you will find the restriction that is causing the behaviour. Afterwards, you can post it here or research based on that restriction, why it behaves like this, unless it is not already obvious to you.

[nidak2501]

Hi Michael

I tried this approach as well ,did not work.

left outer join is behaving like inner join even if i put just one ON condition without where clause for table B.

without the outer join i am getting table A result in output.

Thanks

[michael_piesche]

nidak2501, I would recommend reproducing this behaviour with standard CDS view and standard tables. Also check if it makes a difference whether you have parameters in the from-clause or not. If that also leads to an inner join instead of a left join, I would open a SAP ticket, however, you would have more success if you also find SAP coding where it behaves like this. If it does behave like a left join on the other hand, you have the proof that in general it works, but there is something off with your custom objects or your select statement. Based on ABAP documentation, CDS view can be used in join expressions like any other data source.Please also post the complete SQL statement as well as the ABAP version you are working on right now for the community.

[nidak2501]

Hi Michael,

I did try your suggestion and it is working for standard tables as ZPD1 is an ABAP CDS view with ACDOCA,BSID,MARA etc

sharing complete select statement...

*here ZPD1 is a view with ACDOCA left joining BSAD ,MAKT etc 
SELECT  FROM ZPD1( PBUKRS = @BUKRS-LOW ,POSTFROM = @COL_DATE-LOW ,POSTTO = @COL_DATE-HIGH ) AS A

      LEFT OUTER JOIN
      ZCUSTOM_TABLE  AS F ON
       A~PROD_CATEGORY = F~PROD_CATEGORY
      AND  A~OD_DATE GE F~POST_DT_FROM AND  A~OD_DATE LE F~POST_DT_TO
      AND F~COL_DT_FROM GE @COL_DATE-LOW  AND F~COL_DT_TO LE @COL_DATE-HIGH
      AND F~SALES_OFFICE = @VKBUR-LOW
      AND CASE WHEN A~ARREAR_DAYS <= 0 THEN 'NOD' WHEN A~ARREAR_DAYS > 0 THEN 'OD' END = F~OD

      INNER JOIN KNA1 AS H ON H~KUNNR = A~KUNNR
      INNER JOIN TVKBT AS I ON I~VKBUR = F~SALES_OFFICE AND I~SPRAS = @SY-LANGU

      LEFT OUTER JOIN SKAT AS J ON J~SAKNR = A~HKONT AND J~SPRAS = 'E' AND J~KTOPL = '1000'
      LEFT OUTER JOIN MAKT AS K ON K~MATNR = A~MATNR AND K~SPRAS = 'E'
      LEFT OUTER JOIN T001W AS L ON L~WERKS = A~WERKS


      FIELDS
      A~RBUKRS  AS BUKRS,
      A~KUNNR,
      H~NAME1,
      F~SALES_OFFICE,
      I~BEZEI,
      A~BELNR AS COL_BELNR,
      A~GJAHR AS COL_GJAHR,
      A~HSL AS COL_AMT,
      A~BUDAT AS COL_DATE,
      A~KEY_DATE AS KEY_OS_DATE,
      A~OD_DOC AS OS_BELNR,
      A~OD_YR AS OS_GJAHR,
      A~BLART ,
      A~OD_DATE AS OS_DATE,
      CASE WHEN A~SHKZG = 'H' THEN CAST( A~WRBTR * -1 AS CURR( 12, 2 ) ) ELSE A~WRBTR END AS OS_AMT ,
      A~DUE_DATE,
      A~ARREAR_DAYS,
      CASE WHEN A~ARREAR_DAYS <= 0 THEN 'NOD'
      WHEN A~ARREAR_DAYS > 0 THEN 'OD'
      ELSE ' ' END AS OD_IND,
      A~HKONT,
      J~TXT50,
      A~PROD_CATEGORY,
      A~MATNR,
      K~MAKTX,
      A~WERKS ,
      L~NAME1,
      A~BWTAR,
      A~PROFIT_CENTRE AS PRCTR,
      COALESCE( F~PD_RATE , 0 ) AS RATE,
      0 AS PD_AMT,
      CASE WHEN A~ARREAR_DAYS > 0 THEN  CAST( DIVISION( ( CAST( A~WRBTR   * F~PD_RATE AS CURR( 15 ,2 ) ) ) , 100 ,3 ) AS  CURR( 15, 2 ) ) * -1
      ELSE  DIVISION( ( CAST( A~WRBTR   * F~PD_RATE AS CURR( 15 , 2 ) ) ) , 100 ,3 )  END AS ITEM_PD_AMT,  "DISCOUNT_RATE,

     A~MAIN_TYPE

    WHERE A~KUNNR IN @KUNNR
    AND  NOT EXISTS  ( SELECT * FROM ZFI_PAYMENT_DISC WHERE BELNR = A~BELNR
    AND GJAHR = A~GJAHR AND BUKRS = A~RBUKRS AND SALES_OFFICE = @VKBUR-LOW AND A~KUNNR IN @KUNNR AND OS_BELNR EQ A~OD_DOC   )
    AND A~ARREAR_DAYS <= @OD
    AND ( F~COL_AMT_FROM LE ( SELECT SUM( COLLECT ) FROM ZPD_HSL( BUKRS = @BUKRS-LOW ,BUDATFROM = @COL_DATE-LOW ,BUDATO = @COL_DATE-HIGH ) WHERE KUNNR = A~KUNNR )
    AND F~COL_AMT_TO GE ( SELECT SUM( COLLECT ) FROM ZPD_HSL( BUKRS = @BUKRS-LOW ,BUDATFROM = @COL_DATE-LOW ,BUDATO = @COL_DATE-HIGH ) WHERE KUNNR = A~KUNNR )
  )

  ORDER BY A~KUNNR, A~BELNR ,A~DUE_DATE ASCENDING
  INTO TABLE @PROV_POST.
   

Regards,