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~ODhere 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