Incorrect result Left Outer Join from CDS on HANA DB

[former_member284740]

System:

SAP ERP 6.0 / EHP7 FOR SAP ERP 6.0

SAP NETWEAVER 7.4

HANA DB release 1.00.122.05.1481577062

Software Component Version Support Package Patch Level

SAP KERNEL 7.45 64-BIT UNICODE SP310 000310

Currently i'm facing a peculiar problem. I have searched on service.sap.com and found similar problems but all related to non HANA-DB.

The problem.

I have build a CDS with a join between MARA and MSEG. The join is LEFT OUTER JOIN.

However, the result seems like it was an INNER JOIN: left table MARA has entries en right table MSEG has no entries. The result is no records returned by the CDS.

define view Zcds_Artikel_Omzet 
with parameters p_date:abap.dats

as select distinct mara.matnr, mara.mtart, mara.mstae, mara.mstde, mara.lvorm, mara.attyp, mara.satnr,
    case mseg.bwart when '251' then ' ' else 'X' end as verwerken

from mara as mara
                   
left outer join mseg as mseg on mara.matnr = mseg.matnr 
left outer join mkpf on mseg.mblnr = mkpf.mblnr and
                        mseg.mjahr = mkpf.mjahr

where mkpf.bldat >= $parameters.p_date  

With or without the addition DISTINCT for certain materials (that don't have a MSEG record) no result is returned. I would at least expect details from MARA as normally a LEFT OUTER JOIN would give.

I have even tried with replacing

where mkpf.bldat >= $parameters.p_date  

with

where mseg.budat_mkpf >= $parameters.p_date  

But the result remains the same: no records found.

Including a BSEG field in the field list to be returned resulted also in 0 records found.

I have reconstructed the selection in pure ABAP, but even here no records are returned.

Anyone else experienced the same problem? And if so, how did you solve it?

Regards,

Guus

<br>

[horst_keller]

Run the exisitng example. If it behaves as described, I guess it's rather your data/expectation that is not correct. Otherwise, ...

[raymond_giuseppi]

I suppose the WHERE clause remove those defaulted records, you could change the where clause with a

mkpf.bldat >= $parameters.p_date or mkpf.bldat IS NULL

You could also put the where criteria in the ON criteria

left outer join mseg as mseg on mara.matnr = mseg.matnr and mseg.budat_mkpf >= $parameters.p_date

Regards,
Raymond

[former_member284740]

@Horst Unfortunately, i'm on ABAP 7.4. Your example cannot be activated. Still, my expectation of the left outer join was not incorrect. I have a specific material in MARA, but no records in MSEG for that material (checked with SE16N). As far as I know, the left outer join should still return a result.

@Raymond extending with IS NULL doesn't affect the result. However, extending the left outer join seems to working. The reason why eludes me though.

Anyway, thanks for responding!

regards,

Guus

[raymond_giuseppi]

I suppose the LEFT JOIN doesn't return a NULL value (as in a native SQL statement) but has already converted to an Abap compliant initial value, so the where clause out of JOIN apply to the result of the join, not to the join itself?

[horst_keller]

No, OUTER JOINS are in fact examples how you can produce NULL VALUES in result sets. If you transfer to ABAP with INTO, those are converted to ABAP initial values. But in the WHERE condition, you have to use IS [NOT] NULL.

DELETE FROM demo_join1. 
INSERT demo_join1 FROM TABLE @( VALUE #( 
  ( a = 'a1' b = 'b1' c = 'c1'  d = 'd1' ) 
  ( a = 'a2' b = 'b2' c = 'c2'  d = 'd2' ) ) ). 
DELETE FROM demo_join2. 
INSERT demo_join2 FROM TABLE @( VALUE #( 
  ( d = 'd1' e = 'e1' f = 'f1'  g = 'g1'  h = 'h1' ) ) ). 
SELECT * 
       FROM demo_join1 AS d1 
         INNER JOIN demo_join2 AS d2 
           ON d1~d = d2~d 
       INTO TABLE @DATA(result1). 
SELECT * 
       FROM demo_join1 AS d1 
         LEFT OUTER JOIN demo_join2 AS d2 
           ON d1~d = d2~d 
       WHERE d2~d IS NOT NULL 
       INTO TABLE @DATA(result2). 
ASSERT result1 = result2. 

The LEFT OUTER JOIN produces a result set with null values, that are removed by the WHERE condition.

[horst_keller]

You can look up the example in your system, it is there too ...

[raymond_giuseppi]

I understand, that's why I suggested first to add a OR IS NULL in the WHERE criteria, but OP wrote that this didn't work, so I misinterpreted as if the value was no longer NULL, when actually the JOIN should have returned some record(s) with a date lower than parameter required...

[horst_keller]

No problem!

[former_member284740]

Hi Horst, indeed I found the example on 7.4 and it works as intended.

But i'm still trying to figure out why my original LEFT OUTER didn't give me the result I wanted: indeed I want NULL values for the MSEG part. And I still don't understand why extending the ON did give me the result I wanted as opposed to the WHERE version.

I have checked the data in both tables and it should have worked. But apparently i'm missing something in my understanding of how a LEFT OUTER should work.

   parameters: pa_matnr type matnr,
              pa_Date type sydatum default sy-datum.


  select mara~matnr, mseg~mblnr into table @data(lt_mseg)
    from mara as mara left outer join mseg as mseg on mara~matnr = mseg~matnr
      where mara~matnr eq @pa_matnr and
            mseg~budat_mkpf ge @pa_date.

  select mara~matnr, mseg~mblnr into table @data(lt_mseg2)
    from mara as mara left outer join mseg as mseg on mara~matnr = mseg~matnr and
                                                      mseg~budat_mkpf >= @pa_date
      where mara~matnr eq @pa_matnr.

The first selection fails and the second works. But why? Maybe someone could shed some light on this so I can understand it. Always better than simply taking something for granted.

Regards,

Guus

[former_member183045]

If I read it right, your comparision field mseg~budat_mkpf is of type date. Thus maybe your comparision checks works against the HANA specific null value for dates and an "is null" does not fit.

See here fore details:

https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a1569875191014b507cf392724b7eb/content.htm

<quote>

An empty date ('000-00-00') is a special value in SAP HANA. Even though an empty date looks like a NULL or unknown value, it does not behave like one nor is it treated like one.

</quote>

regards, Andreas

[horst_keller]

Good point. ON conditions, are indeed platform dependent.