ABAP CDS Views: Is it possible to Select Single? (Part 2 of 2)

[ABAPMarty]

Solution – with “No Counter”

Part 1 is here: https://community.sap.com/t5/technology-blogs-by-members/abap-cds-views-is-it-possible-to-select-sin... 

This solution is a little more subtle. Can you fetch the first record, or can you only fetch a single record? At this time, the “single record” will also be the first record, but this might not always be the case. Always test your solution thoroughly to check that it behaves in the ways you expect.

We’re programmers, and we know what is most important: the details. Basically if we use the MIN() function on the barcode (not the counter) it will return the barcode with the lowest value irrespective of it being the first or not. Depending on the table this will be the same as data is often sorted.

The “Z” Tables

Table: ZMC_MEAN_HDR (Material Master)

MANDT type MANDT
MATNR type MATNR
MEINH type MEINH

I populated these tables with this dataset to demonstrate clearly what we are trying to achieve.

Data Preview

I populated these tables with this dataset to demonstrate clearly what we are trying to achieve.

Table: ZMC_MEAN_NC (Barcodes, no counter field)

Data Preview

At this point my efforts were slightly undercut. I added in material “150000000000009888” but added the barcodes for that field in descending order. This was to show that that the system would select the last record for that material number as it was the lowest barcode. But the system added those to the DB table in ascending order.

Here is the ABAP code I used to load the data:

  lt_mean_nc = VALUE #( ( matnr = '150000000000009555' meinh = 'EA'  ean11 = '4444-5555-6666-776' hpean = '' )
                        ( matnr = '150000000000009555' meinh = 'EA'  ean11 = '4444-5555-6666-777' hpean = '' )
                        ( matnr = '150000000000009555' meinh = 'EA'  ean11 = '4444-5555-6666-778' hpean = '' )
                        ( matnr = '150000000000009555' meinh = 'EA'  ean11 = '4444-5555-6666-779' hpean = '' )

                        ( matnr = '150000000000009888' meinh = 'EA'  ean11 = '4444-2222-6666-779' hpean = '' )
                        ( matnr = '150000000000009888' meinh = 'EA'  ean11 = '4444-2222-6666-778' hpean = '' )
                        ( matnr = '150000000000009888' meinh = 'EA'  ean11 = '4444-2222-6666-777' hpean = '' )
                        ( matnr = '150000000000009888' meinh = 'EA'  ean11 = '4444-2222-6666-776' hpean = '' )

                        ( matnr = '150000000000007711' meinh = 'PAL' ean11 = '4444-1111-6666-771' hpean = '' )
                        ( matnr = '150000000000007711' meinh = 'PAL' ean11 = '4444-1111-6666-772' hpean = 'X' )
                        ( matnr = '150000000000007711' meinh = 'PAL' ean11 = '4444-1111-6666-773' hpean = '' ) ) .

And in the database:

So, a bit of a facepalm moment for me trying to make a point.

The CDS Views

The following CDS views were created:

CDS View - ZMC_SELS_MEAN_HDR

@AbapCatalog.sqlViewName: 'ZMCSELSHDR'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Barcode Header selection'
define view ZMC_SELS_MEAN_HDR as select from zmc_mean_hdr
{
    matnr,
    meinh
}

This fetches the material number and the unit of measure.

Data preview:

VALUE #(
( matnr = '150000000000007711' meinh = 'PAL'  )
( matnr = '150000000000009555' meinh = 'EA'  )
( matnr = '150000000000009888' meinh = 'EA'  )
 )

CDS View – ZMC_SELS_MEAN_NOCNT

@AbapCatalog.sqlViewName: 'ZMCSELSNOCNT'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Barcode selection (no counter)'
define view ZMC_SELS_MEAN_NOCNT as select from zmc_mean_nc
{
    matnr,
    meinh,
    ean11,
    hpean
}

Data preview:

VALUE #(
( matnr = '150000000000007711' meinh = 'PAL' ean11 = '4444-1111-6666-771' hpean = ''  )
( matnr = '150000000000007711' meinh = 'PAL' ean11 = '4444-1111-6666-772' hpean = 'X'  )
( matnr = '150000000000007711' meinh = 'PAL' ean11 = '4444-1111-6666-773' hpean = ''  )
( matnr = '150000000000009555' meinh = 'EA' ean11 = '4444-5555-6666-776' hpean = ''  )
( matnr = '150000000000009555' meinh = 'EA' ean11 = '4444-5555-6666-777' hpean = ''  )
( matnr = '150000000000009555' meinh = 'EA' ean11 = '4444-5555-6666-778' hpean = ''  )
( matnr = '150000000000009555' meinh = 'EA' ean11 = '4444-5555-6666-779' hpean = ''  )
( matnr = '150000000000009888' meinh = 'EA' ean11 = '4444-2222-6666-776' hpean = ''  )
( matnr = '150000000000009888' meinh = 'EA' ean11 = '4444-2222-6666-777' hpean = ''  )
( matnr = '150000000000009888' meinh = 'EA' ean11 = '4444-2222-6666-778' hpean = ''  )
( matnr = '150000000000009888' meinh = 'EA' ean11 = '4444-2222-6666-779' hpean = ''  )
 )

CDS View - ZMC_SELS_MEAN_NOCNT_MIN

Gets the MIN() value for each barcode.

@AbapCatalog.sqlViewName: 'ZMCSELSNOCNTMIN'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Barcode selection count minimum'
define view ZMC_SELS_MEAN_NOCNT_MIN as select from zmc_mean_nc
{
    matnr,
    meinh,
    min(ean11) as ean11 
} group by matnr, meinh

Data Preview

VALUE #(
( matnr = '150000000000007711' meinh = 'PAL' ean11 = '4444-1111-6666-771'  )
( matnr = '150000000000009555' meinh = 'EA' ean11 = '4444-5555-6666-776'  )
( matnr = '150000000000009888' meinh = 'EA' ean11 = '4444-2222-6666-776'  )
 )

As you can see for the “PAL” barcode it did not bring back the main barcode but the minimum one, which is why we have the logic in the main CDS View below. In this instance that is correct, but in our main CDS view we will retrieve the main barcode if it is set.

CDS View - ZMC_SELECT_BARCODES_NC

This CDS View fetches the data per the requirement:

“Give the me the main barcode and if there isn’t one, fetch the first one.”

@AbapCatalog.sqlViewName: 'ZMCSELSBCNOCNT'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Barcode selection no counter'
define view ZMC_SELECT_BARCODES_NC as select from ZMC_SELS_MEAN_HDR as Header

    left outer join ZMC_SELS_MEAN_NOCNT as MainBarcode
      on Header.matnr = MainBarcode.matnr
      and Header.meinh = MainBarcode.meinh
      and MainBarcode.hpean = 'X'

    left outer join ZMC_SELS_MEAN_NOCNT_MIN as BarcodeMinimum
      on Header.matnr = BarcodeMinimum.matnr
      and Header.meinh = BarcodeMinimum.meinh

{
    Header.matnr,
    Header.meinh,
    
    case when MainBarcode.ean11    is not null then MainBarcode.ean11
         when BarcodeMinimum.ean11 is not null then BarcodeMinimum.ean11
         else ''
         end as ean11
}

Data preview:

VALUE #(
( matnr = '150000000000007711' meinh = 'PAL' ean11 = '4444-1111-6666-772'  )
( matnr = '150000000000009555' meinh = 'EA' ean11 = '4444-5555-6666-776'  )
( matnr = '150000000000009888' meinh = 'EA' ean11 = '4444-2222-6666-776'  )
 )

Here you can see for the “PAL” material it returned the second record which is tagged as the main barcode, while for the next two materials it brought back the record with the lowest barcode value.

Conclusion

While CDS Views have no “select single” implementation, using functions like “MIN()” or “SUM()” and a bit of trickery it is possible to implement this requirement yourself.

Performance will vary with multiple factors influencing the outcome. It is up to the developer to ensure that testing covers enough scenarios to validate that the performance will be acceptable to the user.

The Report

I created an ABAP report to select from the above CDS views but also to populate the tables and you can see as an attachment to this post.

Please feel free to use it in your own learning if you like. This is not for production usage and is provided for educational purposes only.

Report output: