Skip to Content
0
Jun 15, 2020 at 11:46 AM

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

962 Views

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