Skip to Content

Incorrect result Left Outer Join from CDS on HANA DB

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>
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Feb 02, 2017 at 12:28 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 03, 2017 at 10:50 PM

    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


    Add comment
    10|10000 characters needed characters exceeded

  • Feb 02, 2017 at 12:26 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 02, 2017 at 12:48 PM

    @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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 03, 2017 at 12:18 PM

    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

    Add comment
    10|10000 characters needed characters exceeded